Group By Date ? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Group By Date ?

Author

Topic

chacalau
Starting Member

3 Posts

Posted - 2007-12-05 : 01:40:09

Hello SQL Gurus,

Here is my challenge...

I have a database with a table that has a date field in numerical format, ie. 20071231 (YYYYMMDD).

the table lists Invoice numbers, their date, value, customerID, etc.

what would the query look like that shows the sum of value of invoices, for a customer, grouped by each month.

so a recordset that looks like

month, customerid, sum(invoicevalue)
200708, 113, $1000
200709, 113, $1111
200710, 113, $1010

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-05 : 01:56:54

select datepart(year, mydatecol), datepart(month, mydatecol), sum(invoicevalue)
from mytable
group by datepart(year, mydatecol), datepart(month, mydatecol)
order by datepart(year, mydatecol), datepart(month, mydatecol)

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-05 : 01:57:30

Select left(datecol,6) as datecol,customerid,sum(invoicevalue) as invoicevalue from table
group by left(datecol,6),customerid
order by cast(datecol as datetime)

But you should use proper DATETIME datatype to store dates

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-05 : 02:00:31

quote:
Originally posted by jezemine

select datepart(year, mydatecol), datepart(month, mydatecol), sum(invoicevalue)
from mytable
group by datepart(year, mydatecol), datepart(month, mydatecol)
order by datepart(year, mydatecol), datepart(month, mydatecol)

elsasoft.org

You need to convert mydatecol to DATETIME

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-05 : 02:04:46

quote:
Originally posted by madhivanan

quote:
Originally posted by jezemine

select datepart(year, mydatecol), datepart(month, mydatecol), sum(invoicevalue)
from mytable
group by datepart(year, mydatecol), datepart(month, mydatecol)
order by datepart(year, mydatecol), datepart(month, mydatecol)

elsasoft.org

You need to convert mydatecol to DATETIME

Madhivanan

Failing to plan is Planning to fail

no I don't. OP does though.

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-05 : 02:13:55

Good work to OP

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-05 : 11:49:57

Do you have something like a Date/Time dimension table or something? If you had a dimension table like that you could join to it and group by year and month without having to do any conversions.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 13:21:02

Date is numeric?

SELECT Date / 100 AS month, customerid, sum(invoicevalue)
from table1
group by Date / 100 AS month, customerid
order by Date / 100 AS month, customerid

E 12°55'05.25"
N 56°04'39.16"

chacalau
Starting Member

3 Posts

Posted - 2007-12-05 : 21:34:45

Wow guys (and gals), thank you so much for your input.

here then is the winning, er, successful query i used...

Select left(OhDate,6) as OhDate, SUM(OhSubtot) AS TotalInvoiced FROM Inv_hdr WHERE OhDate LIKE '" & year(date) & "%' GROUP BY left(OhDate,6) ORDER BY left(OhDate,6) DESC

worked a treat!

Initially i thought the 'data / 100' method was the way to go... so blatantly simple i almost kicked myself when i read it... but it had one problem... 20071231 / 100 = 200712.31

which means that the Group By still had unique entries for each invoice data... and did not give me the group all for a month.

anyway... thanks heaps for your help guys!!!!

chacalau
Starting Member

3 Posts

Posted - 2007-12-05 : 21:45:14

...and gals!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-06 : 00:21:57

quote:
Originally posted by chacalau

Initially i thought the 'data / 100' method was the way to go... so blatantly simple i almost kicked myself when i read it... but it had one problem... 20071231 / 100 = 200712.31

which means that the Group By still had unique entries for each invoice data... and did not give me the group all for a month.

20071231/100 = 200712 using integer math.

your column must be a decimal/numeric/float/double and not an integer type. if it was int, Peso's query would work.

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-06 : 01:36:06

But as I said in my first reply always use proper DATETIME datatype to store dates

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-06 : 01:42:05

Also refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources