Display values for the whole month - Tom Moreau |
04-Jan-08 07:00:58
|
Try:
select
item
, sum (case when datepart (dd, [date]) = 1 then qty else 0 end) as day1
, sum (case when datepart (dd, [date]) = 2 then qty else 0 end) as day2
, sum (case when datepart (dd, [date]) = 3 then qty else 0 end) as day3
...
from
MyTable
group by
item
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
Dear Sir,
My values in the table is given below.
sys_line_Num item date qty
1 1 01/01/2008 100
2 1 02/01/2008 200
3 1 03/01/2008 300
4 1 05/01/2008 400
I want to display the values in the report in the following way for the
whole month
item day1 day2 day3 day4 day5 day7 ........
1 100 200 300 0 400
Kindly help us how to display the above result.
regards
SUNDARA MURTHY |
 |
| |
Display values for the whole month - Sundar |
04-Jan-08 07:16:01
|
thanks a lot. |
| |
| |
Display values for the whole month - Sundar |
04-Jan-08 07:17:01
|
one more issue that i want to sum the qty for a particular week |
| |
| |
Display values for the whole month - Tom Moreau |
04-Jan-08 07:57:16
|
Then you can add a WHERE clause to filter that:
select
item
, sum (case when datepart (dd, [date]) = 1 then qty else 0 end) as day1
, sum (case when datepart (dd, [date]) = 2 then qty else 0 end) as day2
, sum (case when datepart (dd, [date]) = 3 then qty else 0 end) as day3
...
from
MyTable
where
date >= '20080108'
and date < '20080115'
group by
item
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
one more issue that i want to sum the qty for a particular week |
| |
| |