Return results in weekly report format

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

SQL Server Development (2000)

Return results in weekly report format

Author

Topic

JoeAD
Starting Member

1 Post

Posted - 2008-01-14 : 13:03:31

I have a table consisting of the following:

i_loc_code i_group i_dept e_epos_date e_sell_val
010 952 D94 13/01/2008 2000
010 952 D94 12/01/2008 1500
010 956 D94 11/01/2008 3000
012 981 D94 11/01/2008 5000
014 952 D94 10/01/2008 2000

I want to return a weelky sales report in the following format:

Code Sun Mon Tue Wed Thurs Fri Sat Total
010 3000 4000 2000 7000 2000 18000
014 2000 1000 2000 4000 2000 11000

It is totalled figures (from 'e_sell_val' column) for each day of the current week only. In the example above fri and sat are returned but are blanck because they are in the future and no data exists!

I need to return the following loc codes...

010
012
015
014
016

...and I need to apply the following filter:

i_dept = D94 OR i_group = 952 OR i_group = 981 OR i_group = 956

I also need to make it so I can pass a week number and a year into the query so previous weeks results can be generated.

Thanks
Joe

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-14 : 21:23:46

[code]SELECT Code = i_loc_code
[Sun] = SUM(CASE WHEN DATENAME(weekday, e_epos_date) = 'Sunday' THEN e_sell_val ELSE 0 END),
[Mon] = SUM(CASE WHEN DATENAME(weekday, e_epos_date) = 'Monday' THEN e_sell_val ELSE 0 END),
. . .
FROM yourtable
WHERE <your where condition where>
GROUP BY i_loc_code[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 02:29:21

Also read about Cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources