2

I have a table :

------------------------------------ ReportDate | DAGName | MailboxCount ------------------------------------

There is lot of records in this table . I have to get the sum of mailboxcount of each dagName on the 1st day of every month for a particular year.

------------------------------------ ReportDate | DAGName | MailboxCount ------------------------------------ 01-01-2012 | abc | 25 01-02-2012 | xyz | 55 01-02-2012 | abc | 125 01-03-2012 | lmn | 225 01-01-2012 | ghf | 325 01-03-2012 | kil | 525 11-03-2012 | kil | 525 21-03-2012 | kil | 625 10-05-2012 | jil | 225 20-11-2012 | kil | 1525 04-03-2012 | Mil | 5025

So what i want as the result is 

--------------------------------- Month Name | Count --------------------------------- January | 350 Ferbuary | 150 March | 850

My Query

SELECT SUM(MailboxCount) AS Count,DagName ,MONTH(CAST(ReportDate AS DATETIME)) ,DATENAME(month, ReportDate) AS 'Month Name' FROM MailboxDatabase WHERE (CONVERT(VARCHAR, CONVERT(VARCHAR(10), [ReportDate], 103), 103) IN ( '01/01/'+ @year,'01/02/'+ @year, '01/03/'+ @year,'01/04/'+ @year, '01/05/'+ @year,'01/06/'+ @year, '01/07/'+ @year,'01/08/'+ @year, '01/09/'+ @year,'01/10/'+ @year, '01/11/'+ @year,'01/12/'+ @year )) GROUP BY MONTH(CAST(ReportDate AS DATETIME)),DATENAME(month, ReportDate),DagName ORDER BY 2

I am fine if some extra columns like coming with my query. But it is not giving me the correct result. Any help ??

Improve this question
3
  • remove DagName its not part of your grouping, reportDate is of what type isn't it a datetime Commented May 8, 2013 at 9:28
  • Do not EVER use VARCHAR without specifying a length, or it will come back and haunt you. Commented May 8, 2013 at 9:30
  • no it is not as date is not the first day of month.... Commented May 8, 2013 at 9:51

4 Answers 4

Reset to default
3

I might be completely misinterpreting the question but wouldn't the following suffice?

 SELECT [Month Name] = DATENAME(month, ReportDate), [Count] = SUM(MailboxCount) FROM MailboxDatabase WHERE DAY(ReportDate) = 1 AND YEAR(ReportDate) = 2012 GROUP BY DATENAME(month, ReportDate)

Test script

;WITH MailboxDatabase AS ( SELECT * FROM (VALUES ('01-01-2012', 25) , ('02-01-2012', 55) , ('02-01-2012', 125) , ('03-01-2012', 225) , ('01-01-2012', 325) , ('03-01-2012', 525)) AS X(ReportDate, MailboxCount) ) SELECT [Month Name] = DATENAME(month, ReportDate) , [Count] = SUM(MailboxCount) , Month = MONTH(ReportDate) FROM MailboxDatabase WHERE DAY(ReportDate) = 1 AND YEAR(ReportDate) = 2012 GROUP BY DATENAME(month, ReportDate), MONTH(ReportDate) ORDER BY MONTH(ReportDate)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

it is almost there but what i need is on the first day with group by of groups tooo
@ankur - That I don't understand. Can you clarify by adding the requirement to your posted example (ps. the count's in your example are off)
Not really, adding those records doesn't alter the result of the statement I've posted but I'm not sure if you want another result or not. Did you try the statement?
@ankur - I have added an order by the simplest way I see possible (by adding a month column). Another option would be to add a searched order by (using a case statement).
1 
SELECT DATENAME(MONTH, CONVERT(DATETIME, ReportDate, 105)) Month, SUM(MailboxCount) COUNT FROM tableName GROUP BY DATENAME(MONTH, CONVERT(DATETIME, ReportDate, 105))

UPDATE 1

SELECT DATENAME(MONTH, CONVERT(DATETIME, ReportDate, 105)) Month, SUM(MailboxCount) COUNT FROM tableName WHERE (CONVERT(VARCHAR(15), CONVERT(DATETIME, ReportDate, 105), 103) IN ( '01/01/'+ '2012','01/02/'+ '2012', '01/03/'+ '2012','01/04/'+ '2012', '01/05/'+ '2012','01/06/'+ '2012', '01/07/'+ '2012','01/08/'+ '2012', '01/09/'+ '2012','01/10/'+ '2012', '01/11/'+ '2012','01/12/'+ '2012' )) GROUP BY MONTH(CONVERT(DATETIME, ReportDate, 105)), DATENAME(MONTH, CONVERT(DATETIME, ReportDate, 105)) ORDER BY MONTH(CONVERT(DATETIME, ReportDate, 105))
Improve this answer

6 Comments

the result are correct but there is no condition for date , since there will be lot of records of other dates too..
FYI, the table name is MailboxDatabase
@SenJacob does it really matter on the demo? :)
JW can you inculde some random dates in your demo, that are not the first day of any month though have the same DAG name to test, if it work i can mark it as solved.....
i meant this INSERT INTO Tablename ([ReportDate], [DAGName], [MailboxCount]) VALUES ('01-01-2012', 'abc', 25), ('01-02-2012', 'xyz', 55), ('01-02-2012', 'abc', 125), ('01-03-2012', 'lmn', 225), ('01-01-2012', 'ghf', 325), ('01-03-2012', 'kil', 525), ('20-11-2012', 'kil', 525), ('11-07-2012', 'kil', 525), ('31-03-2012', 'kil', 525), ('21-03-2012', 'kil', 525), ('11-03-2012', 'kil', 525) ; and it is not working correctly
|
0 
SELECT DATENAME(MONTH, CONVERT(DATETIME, ReportDate, 105)) AS [MonthName], YEAR(CONVERT(DATETIME, ReportDate, 105)) AS [Year], SUM(MailboxCount) COUNT FROM tableName GROUP BY DATENAME(MONTH, CONVERT(DATETIME, ReportDate, 105)), YEAR(CONVERT(DATETIME, ReportDate, 105))

Try above sql query, this query return the result with year wise month and count.

Improve this answer

Comments

0

Try this one -

Query:

SET DATEFORMAT dmy DECLARE @temp TABLE ( ReportDate DATETIME , DAGName NVARCHAR(50) , MailboxCount INT ) INSERT INTO @temp (ReportDate, DAGName, MailboxCount) VALUES ('01-01-2012', 'abc', 25), ('01-02-2012', 'xyz', 55), ('01-02-2012', 'abc', 125), ('01-03-2012', 'lmn', 225), ('01-01-2012', 'ghf', 325), ('01-03-2012', 'kil', 525), ('11-03-2012', 'kil', 525), ('21-03-2012', 'kil', 625), ('10-05-2012', 'jil', 225), ('20-11-2012', 'kil', 1525), ('04-03-2012', 'Mil', 5025) DECLARE @year INT = 2012 SELECT [Count] = SUM(MailboxCount) , Month_Name = DATENAME(MONTH, ReportDate) FROM @temp WHERE DAY(ReportDate) = 01 AND YEAR(ReportDate) = @year GROUP BY ReportDate ORDER BY ReportDate

Output:

Count Month_Name ----------- ------------------------------ 350 January 180 February 750 March
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.