Help with Group by months and Ordering.

Question

Post reply

Help with Group by months and Ordering.

Siva Ramasamy

SSCarpal Tunnel

Points: 4026
More actions
April 10, 2012 at 11:29 am

#258918

Hi everyone,
Thanks for looking into my question.
I have a table that contains data in the below mentioned format.
CR_DATE COUNT
01/01/2012 1
01/30/2012 1
02/05/2012 1
02/07/2012 2
05/09/2012 5
05/19/2012 10
12/21/2012 15
12/31/2012 15
I need to write a query that gives me the total count group by months. But the result set should look like "MON-YYYY" and order by the month of the year (Numerically, not alphabetically..)
Jan-2012 - 2
Feb-2012 - 3
May-2012 - 15
Dec -2012 - 30
help please...
Thanks!
Siva.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

April 10, 2012 at 11:40 am

#1471386

Care to post what you have tried so far?

CapnHector SSCoach Points: 16143 More actions · Answer 2

some thing like this may work.

SELECT DATEADD(MONTH,0,DATEDIFF(MONTH,0,[datefield])), COUNT(*)

FROM table

group by DATEADD(MONTH,0,DATEDIFF(MONTH,0,[datefield]))

if you would like tested code can you post the DDL (Create table scripts) and some sample data (Hard coded insert statements)

Thanks

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Siva Ramasamy SSCarpal Tunnel Points: 4026 More actions · Answer 3

Hi,

Thanks for your reply..

I just got this question...and have started googling...no solutions yet..

But I need to solve it in ASAP.. 🙂

Thanks!

Siva.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Siva Ramasamy (4/10/2012)
Hi,
Thanks for your reply..
I just got this question...and have started googling...no solutions yet..
But I need to solve it in ASAP.. 🙂
Thanks!
Siva.

Interview question?

Siva Ramasamy SSCarpal Tunnel Points: 4026 More actions · Answer 5

Here is the sample data.

CREATE TABLE T1 (CR_DATE DATETIME, CNT INT)

INSERT INTO T1 VALUES( '01/01/2012 08:30.200' ,1)

INSERT INTO T1 VALUES( '01/30/2012 09:30.200' ,1)

INSERT INTO T1 VALUES( '02/05/2012 10:30.200' ,1)

INSERT INTO T1 VALUES( '02/07/2012 07:30.200' ,2)

INSERT INTO T1 VALUES( '05/09/2012 06:30.200' ,5)

INSERT INTO T1 VALUES( '05/19/2012 05:30.200' ,10)

INSERT INTO T1 VALUES( '12/21/2012 04:30.200' ,15)

INSERT INTO T1 VALUES( '12/31/2012 03:30.200' ,15)

Siva Ramasamy SSCarpal Tunnel Points: 4026 More actions · Answer 6

Siva Ramasamy

SSCarpal Tunnel

Points: 4026

April 10, 2012 at 11:49 am

#1471393

no..Not interview question...business question.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

This?

IF OBJECT_ID('TempDB..#Temp') IS NOT NULL

DROP TABLE #Temp;

CREATE TABLE #Temp

(

iD INT IDENTITY(1,1)

,CR_DATE DATETIME

,[COUNT] INT

);

INSERT INTO #Temp (CR_DATE ,[COUNT])

SELECT '01/01/2012', 1

UNION ALL SELECT '01/30/2012', 1

UNION ALL SELECT '02/05/2012', 1

UNION ALL SELECT '02/07/2012', 2

UNION ALL SELECT '05/09/2012', 5

UNION ALL SELECT '05/19/2012', 10

UNION ALL SELECT '12/21/2012', 15

UNION ALL SELECT '12/31/2012', 15

SELECT MonthOfTheYear = REPLACE ( CONVERT(VARCHAR(11), DATEADD(MM, DATEDIFF(MM , 0, T.CR_DATE) ,0)) , ' 1 ','-')

,SM = SUM( T.[COUNT])

FROM #Temp T

GROUP BY DATEDIFF(MM , 0, T.CR_DATE)

ORDER BY DATEDIFF(MM , 0, T.CR_DATE)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Edit: --- Ignore, doesn't work (yet) ----

Here is my shot at the code:

select

substring(convert(varchar(24), dateadd(mm,datediff(mm,0,datefield),0), 113),4,8), count(*)

from

dbo.MyTable

group by

dateadd(mm,datediff(mm,0,datefield),0)

order by

dateadd(mm,datediff(mm,0,datefield),0)

EDIT -- Fixed, used count instead of sum Also changed to use ColdCoffee's setup.

select

substring(convert(varchar(24), dateadd(mm,datediff(mm,0,CR_DATE),0), 113),4,8), sum([COUNT])

from

#Temp

group by

dateadd(mm,datediff(mm,0,CR_DATE),0)

order by

dateadd(mm,datediff(mm,0,CR_DATE),0)

CapnHector SSCoach Points: 16143 More actions · Answer 9

damn beat me to the convert for the date type

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Siva Ramasamy SSCarpal Tunnel Points: 4026 More actions · Answer 10

this is great...exactly what I wanted..thanks so much..

Let me understand your code...if I have any questions, I will ask you.

Thanks so much again!!!

-Siva.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Siva Ramasamy (4/10/2012)
this is great...exactly what I wanted..thanks so much..
Let me understand your code...if I have any questions, I will ask you.
Thanks so much again!!!
-Siva.

Let us know if you get the job.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 12

Lynn/Capt.Hector , correct me if i am wrong, we dont need DATEADD+DATEDIFF in the GROUPBY/ORDER BY; just DATEDIFF would do.

Why i say this, SQL needs to just to wrap the column over a single functin rather than doing 2 functions, twice - one for Group and one for Order!

Also, i am not sure, how a CROSS APPLY solution, which calculates the DATEDIFF once for a row and supplies to GROUP BY/ORDER BY/SELECT clause will perform. What i mean is

SELECT MonthOfTheYear = REPLACE ( CONVERT(VARCHAR(11), DATEADD(MM, CrsApp.Diff ,0)) , ' 1 ','-')

,SM = SUM( T.[COUNT])

FROM #Temp T

CROSS APPLY (SELECT DATEDIFF(MM , 0, T.CR_DATE)) CrsApp(Diff)

GROUP BY CrsApp.Diff

ORDER BY CrsApp.Diff

In the above query, i assume, DATEDIFF is caluclated just once per execution and the value is supplied to other parts of the query. But when we use the typed DATEDIFF in GROUP BY/ORDER BY/SELECT, SQL will caluclate the function thrice. I am not sure if thats how SQL behaves, but i got to do some tests. Meantime, if you guys know that on top of your head, please let me know if my understanding is correct

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

ColdCoffee (4/10/2012)
Lynn/Capt.Hector , correct me if i am wrong, we dont need DATEADD+DATEDIFF in the GROUPBY/ORDER BY; just DATEDIFF would do.
Why i say this, SQL needs to just to wrap the column over a single functin rather than doing 2 functions, twice - one for Group and one for Order!
Also, i am not sure, how a CROSS APPLY solution, which calculates the DATEDIFF once for a row and supplies to GROUP BY/ORDER BY/SELECT clause will perform. What i mean is
SELECT MonthOfTheYear = REPLACE ( CONVERT(VARCHAR(11), DATEADD(MM, CrsApp.Diff ,0)) , ' 1 ','-')
,SM = SUM( T.[COUNT])
FROM #Temp T
CROSS APPLY (SELECT DATEDIFF(MM , 0, T.CR_DATE)) CrsApp(Diff)
GROUP BY CrsApp.Diff
ORDER BY CrsApp.Diff
In the above query, i assume, DATEDIFF is caluclated just once per execution and the value is supplied to other parts of the query. But when we use the typed DATEDIFF in GROUP BY/ORDER BY/SELECT, SQL will caluclate the function thrice. I am not sure if thats how SQL behaves, but i got to do some tests. Meantime, if you guys know that on top of your head, please let me know if my understanding is correct

I didn't think to order by the difference in months. I just converted back to a datetime value and ordered by that.

Would need to look at the execution plans to see of there is a real difference.

CapnHector SSCoach Points: 16143 More actions · Answer 14

Lynn Pettis (4/10/2012)
ColdCoffee (4/10/2012)
Lynn/Capt.Hector , correct me if i am wrong, we dont need DATEADD+DATEDIFF in the GROUPBY/ORDER BY; just DATEDIFF would do.
Why i say this, SQL needs to just to wrap the column over a single functin rather than doing 2 functions, twice - one for Group and one for Order!
Also, i am not sure, how a CROSS APPLY solution, which calculates the DATEDIFF once for a row and supplies to GROUP BY/ORDER BY/SELECT clause will perform. What i mean is
SELECT MonthOfTheYear = REPLACE ( CONVERT(VARCHAR(11), DATEADD(MM, CrsApp.Diff ,0)) , ' 1 ','-')
,SM = SUM( T.[COUNT])
FROM #Temp T
CROSS APPLY (SELECT DATEDIFF(MM , 0, T.CR_DATE)) CrsApp(Diff)
GROUP BY CrsApp.Diff
ORDER BY CrsApp.Diff
In the above query, i assume, DATEDIFF is caluclated just once per execution and the value is supplied to other parts of the query. But when we use the typed DATEDIFF in GROUP BY/ORDER BY/SELECT, SQL will caluclate the function thrice. I am not sure if thats how SQL behaves, but i got to do some tests. Meantime, if you guys know that on top of your head, please let me know if my understanding is correct
I didn't think to order by the difference in months. I just converted back to a datetime value and ordered by that.
Would need to look at the execution plans to see of there is a real difference.

nice on the cross apply to cold coffee. (tucking that away to use later) and i also did not think to order on the difference.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]