Week of Year Issue - 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

Week of Year Issue

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-10-24 : 11:05:55

I am working with the following Code. currentweek is datepart(wk,getdate())

NumberWeek is datepart(wk,duedate)

My problem is how to handle the end of the year. Since today's date is week 44. Week 10, 11 and 12 will be week 1, 2, and 3 of next year. Is there a way I can deal with that using the code below?


select comp_item_no,
SUM(case when currentweek = NumberWeek then CompQty End) as Week1,
SUM(case when currentweek+1 = NumberWeek then CompQty End) as Week2,
SUM(case when currentweek+2 = NumberWeek then CompQty End) as Week3
SUM....
thru Week 12
	   from GLSExcelTable
	   group by comp_item_no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 11:26:01

[code]
select comp_item_no,
SUM(case when Rn=1 then CompQty End) as Week1,
SUM(case when Rn=2 then CompQty End) as Week2,
SUM(case when Rn=3 then CompQty End) as Week3,
....
SUM(case when Rn=12 then CompQty End) as Week12
from (select row_number() over (partition by comp_item_no order by datepart(yy,duedate)*100 + datepart(wk,duedate)) as rn,comp_item_no ,
datepart(yy,duedate)*100 + datepart(wk,duedate) AS WeekYear,SUM(CompQty) AS CompQty
from GLSExcelTable
group by comp_item_no,datepart(yy,duedate)*100 + datepart(wk,duedate))t
group by comp_item_no
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources