Month wise date on column Address

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

Month wise date on column Address

Author

Topic

shineyjohn2007
Starting Member

5 Posts

Posted - 2007-11-20 : 00:20:35

could u help me out to get the month wise date on column address

this is the query

SELECT [ID],
SUM(CASE WHEN week_no = 1 THEN Qty ELSE 0 END) AS week1,
SUM(CASE WHEN week_no = 2 THEN Qty ELSE 0 END) AS week2,
SUM(CASE WHEN week_no = 3 THEN Qty ELSE 0 END) AS week3,
SUM(CASE WHEN week_no = 4 THEN Qty ELSE 0 END) AS week4,
SUM(CASE WHEN week_no = 5 THEN Qty ELSE 0 END) AS week5
FROM
(
SELECT [ID],week_no = DATEPART(week, [Date]) - DATEPART(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date), 0)) + 1, Qty
FROM @sample
WHERE [Date] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date), 0)
AND [Date] < DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date) + 1, 0)
) d
GROUP BY [ID]

for this query the output will be

/*
ID week1 week2 week3 week4 week5
----- ----------- ----------- ----------- ----------- -----------
st001 114 38 0 0 0
st002 110 110 0 0 0

but i need date wise split up to be displayed for particular month

ID week1 week2 week3 week4 week5
(1-7) (8-14) (15-21) (22-28) (29-31)
----- ----------- ----------- ----------- ----------- -----------
st001 114 38 0 0 0
st002 110 110 0 0 0

pls help me out.

Shiney

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-20 : 00:26:23

SUM(CASE WHEN week_no = 1 THEN Qty ELSE 0 END) AS [week1 (1-7)],
SUM(CASE WHEN week_no = 2 THEN Qty ELSE 0 END) AS [week2 (8-14)],

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

shineyjohn2007
Starting Member

5 Posts

Posted - 2007-11-20 : 00:36:47

we should not hot code,
if u take for the month of october 2007
week1(1-7)
week2(8-14)
week3(15-21)
week4(22-28)
week5(29-31)

for the month of november 2007 the date split up will change

week1(1-4)
week2(5-11)
week3(12-18)
week4(19-25)
week5(26-30)

pls help me out.

Shiney

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-20 : 00:38:50

Precisely we shouldn't. If you want to achieve what you want in T-SQL, you have to use Dynamic SQL. But then wouldn't the front end have problem when the column name is dynamic ?

For this type of requirement, it is best to handle at your front end application where you are displaying the data.

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

shineyjohn2007
Starting Member

5 Posts

Posted - 2007-11-20 : 00:43:18

so this requirement is not possible via query,
we can only hot code the dates in the week column.
is it so. :)

Shiney

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-20 : 00:50:57

quote:
Originally posted by shineyjohn2007

so this requirement is not possible via query,
we can only hot code the dates in the week column.
is it so. :)

Shiney

It is. In order to have the column name return from the query dynamically generated depending on the input parameter, you will required to use Dynamic SQL.

See http://www.sommarskog.se/dynamic_sql.html

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

shineyjohn2007
Starting Member

5 Posts

Posted - 2007-11-20 : 00:57:05

ok fine :)
thank u :)

Shiney

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources