How to do a weekly report - 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

How to do a weekly report

Author

Topic

alanhuro
Starting Member

34 Posts

Posted - 2008-07-31 : 15:17:42

Hello To all Sequel Genius out there,

Yearly, monthly and daily report are easy to generate by using group by function. How do you generate a weekly report? Assume I have

Date__________Cat__________Value
2008-07-01_____A____________30
2008-07-02_____A____________20
2008-07-02_____B____________10
2008-07-03_____A____________60
2008-07-04_____A____________80
2008-07-04_____C____________330
2008-07-05_____A____________230
2008-07-06_____B____________330
2008-07-07_____A____________730
2008-07-08_____B____________330
2008-07-10_____A____________130

It would be nice to have a report having format like this. The date start begin on monday. The report output should look like this

Date___________Cat__________Sum
2008-07-01_____A____________420
2008-07-01_____B____________340
2008-07-01_____C____________330
2008-07-07_____A____________860
2008-07-07_____B____________330

Thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-31 : 17:04:49

look at the datepart function on books online, you can group by week number. that should give you a head start

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-31 : 17:09:12

[code]SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', Date), '19000101'),
Cat, SUM(Value)
FROM Table1
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', Date), '19000101')
ORDER BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', Date), '19000101')[/code]

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-31 : 18:31:58

Wouldn't 2008-07-01 and 2008-07-07 be in the same week? They are only 6 days apart.

CODO ERGO SUM

alanhuro
Starting Member

34 Posts

Posted - 2008-08-01 : 08:46:38

Thanks Peso and elancaster

I did not now that simple. I also set @@Datefirst to monday and it works great.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources