group by day and month of a Date column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

group by day and month of a Date column

Author

Topic

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-12-12 : 18:27:56

Simple one guys,
I need to know how to group by the day and month part of a date for by group by statement.
i.e: group by Day(Serv_Date)&Month(Serv_Date)
Cheers
GK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 18:30:08

Did you try your own suggestion?

Peter Larsson
Helsingborg, Sweden

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-12-12 : 18:38:27

Yes this works, but i would like it to return the actual date in its format.
i.e:
select left(SERVICE_ID,4) as service_id_lpad4,
USAGE_TYPE_CODE,
Day(billed_date)&Month(billed_date),
sum(AMOUNT) as amount
from RDEAL44_AUG06
group by left(SERVICE_ID,4),
Day(billed_date)&Month(billed_date),
USAGE_TYPE_CODE
having USAGE_TYPE_CODE <> 'IDDV'
order by Day(billed_date)&Month(billed_date),
service_id_lpad4

I want it to return something like this: (in particular to the 3rd column-date)

0400 NDD 01/06 726642335.0000
0400 NDDD 01/06 2086643.0000
0400 PACD 01/06 2728563.0000
0400 NDDF 04/06 31797.0000
0400 NDDM 04/06 584521.0000
0400 PACT 04/06 16392302.0000
0400 NDDT 15/07 346383.0000

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-12-12 : 18:43:43

also i still dont think its grouping by the day and month as initial request.
GK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 18:48:39

[code]select left(SERVICE_ID,4) as service_id_lpad4,
USAGE_TYPE_CODE,
Month(billed_date),
Day(billed_date),
sum(AMOUNT) as amount
from RDEAL44_AUG06
where USAGE_TYPE_CODE <> 'IDDV'
group by left(SERVICE_ID,4),
USAGE_TYPE_CODE,
Month(billed_date),
Day(billed_date)
order by service_id_lpad4,
Month(billed_date),
Day(billed_date)[/code]

Peter Larsson
Helsingborg, Sweden

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-12-12 : 19:01:42

Yes this would work. thanks.
Is there another way where I can have both the day and month in the same column. i.e: the result displayed is in the format of dd/mm such that it is grouped by the same format?
GK

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-12-12 : 19:28:50

left(billed_date,7) instead of month(billed_date) and day(billed_date)
Will do the trick for the above.
GK

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-01-18 : 22:20:44

Going on from this post, how do i get a year and month component of a datetime column. in particular im using this in a store procedure and i need the user only to enter the month/year component, i.e: 10/2006 such that:


select       left(service_id,3) as Service_Number,
	billed_date as Billed_Date, 
	sum(amount/100) as revenue
from rdeal44_06
WHERE YEAR(billed_date) = @YEAR2 AND MONTH(BILLED_DATE) = @MONTH2
group by left(service_id,3),
	 billed_date
order by billed_date

This works however i need something that will take in both...similar to left(billed_date,7) but i only need the month and year component. for some apparent reason when i try the left function it returns the month and day component and not the month year component although in analyser the billed_date column is in 2006-10-01 00:000 etc... format.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 01:30:23

[code]SELECT x.Service_Number,
DATENAME(month, x.Billed_Date) + ' ' + CAST(DATEPART(day, x.Billed_Date) AS VARCHAR) AS Billed_Date,
x.Revenue
FROM (
SELECT LEFT(Service_ID, 3) AS Service_Number,
DATEADD(day, DATEDIFF(day, 0, Billed_Date), 0) AS Billed_Date,
SUM(Amount / 100.0) AS Revenue
FROM rdeal44_06
WHERE YEAR(Billed_Date) = @Year2
AND MONTH(Billed_Date) = @Month2
GROUP BY LEFT(Service_ID, 3),
DATEADD(day, DATEDIFF(day, 0, Billed_Date), 0)
) AS x
ORDER BY x.Billed_Date[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources