column reverse - 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

SQL Server 2005 Forums

Transact-SQL (2005)

column reverse

Author

Topic

mercybthomas74
Starting Member

3 Posts

Posted - 2008-02-14 : 09:44:31

i have table OrdersShipped
Group OrderId OrderDesc DateRec IstDate Shiped Cases
1 123 aaaa 1/1/2006 1/25/2008 200
1 123 aaaa 1/26/2007 1/25/2008 258
1 123 aaaa 2/1/2007 1/25/2008 244
1 224 qqqq 2/8/2007 1/25/2008 244
1 224 qqqq 2/15/2007 1/25/2008 245
2 333 www 2/1/2007 2/8/2008 2574
2 333 www 2/15/2007 2/8/2008 898
3 999 oooo 2/1/2007 3/5/2008 750
3 888 mmm 1/26/2007 3/5/2008 5842
3 999 oooo 3/12/2007 3/5/2008 685

Writing a report on Week bases
so far I have

DECLARE @sql varchar(8000), @Shipped_Week_ID int, @Min_Week_ID int, @Max_Week_ID int, @counter int, @wk int

SELECT @Shipped_Week_ID = Min(DATEPART(wk, DATEADD(week, ROUND(52.1775 * - 1, 0), [IstDate Shiped])))
FROM OrdersShipped

SELECT @Min_Week_ID = MIN(DATEPART(wk, DATEADD(week, ROUND(52.1775 * - 1, 0), DateRec)))
FROM OrdersShipped

SELECT @Max_Week_ID = MAX(DATEPART(wk, DATEADD(week, ROUND(52.1775 * - 1, 0), DateRec)))
FROM OrdersShipped

SET @counter = 1

SELECT @sql =
'SELECT
[group],
OrderId,
OrderDesc,
--DateRec,
--Cases,
--DATEPART(wk, DATEADD(week, ROUND(52.1775 * - 1, 0), DateRec)) AS Week_ID,
DATEPART(wk, DATEADD(week, ROUND(52.1775 * - 1, 0), [IstDate Shiped])) AS Shipped_Week_ID,
'

WHILE @counter <= @Max_Week_ID
BEGIN

SELECT @wk = CASE WHEN ((@counter + @shipped_week_id -1) % (@Max_Week_ID)) = 0 THEN @Max_Week_ID
ELSE ((@counter + @shipped_week_id -1) % (@Max_Week_ID)) END

SELECT @sql = @sql +
'Sum(CASE WHEN DATEPART(wk, DATEADD(week, ROUND(52.1775 * - 1, 0), DateRec)) = ' + CONVERT(varchar, @wk) + ' THEN Cases ELSE 0 END) AS Wk' + CONVERT(varchar, @wk) + '
,'

SET @counter = @counter +1

END

SELECT @sql = STUFF(@sql, LEN(@sql), 1, '')

SELECT @sql = @sql + ' FROM OrdersShipped group by [group],OrderId,
OrderDesc,[IstDate Shiped]'

EXEC (@sql)

This results....
Group OrderID OrderDesc WeekId wk4 wk5 wk6 wk7 wk8 wk9 wk10 wk11 wk1 wk2 wk3
1 123 aaaa 4 258 244 0 0 0 0 0 0 0 200 0
1 224 qqqq 4 0 0 244 245 0 0 0 0 0 0 0
2 333 www 6 0 2574 0 898 0 0 0 0 0 0 0
3 888 mmm 10 5842 0 0 0 0 0 0 0 0 0 0
3 999 oooo 10 0 750 0 0 0 0 0 685 0 0 0

But I want to see
it should be based Groups.. and the 1st shipped dates...

For Group 1 the highest 1st shipped is 4
For Group 2 the highest 1st shipped is 6
For Group 3 the highest 1st shipped is 10

Group 1 : WeeKID is 4 - starts from wk4 ... wk11 then wk1,wk2,wk3
Group 2 : WeeKID is 6 - starts from wk6 ... wk11 then wk1,wk2,wk3,wk4,wk5
so on ....

Group OrderID OrderDesc WeekId wk4 wk5 wk6 wk7 wk8 wk9 wk10 wk11 wk1 wk2 wk3 wk4 wk5 wk6
1 123 aaaa 4 258 244 0 0 0 0 0 0 0 200 0 0 0 0
1 224 qqqq 4 0 0 244 245 0 0 0 0 0 0 0 0 0 0
2 333 www 6 0 0 0 898 0 0 0 0 0 0 0 2574 0
3 888 mmm 10 0 0 0 0 0 0 0 0 0 0 0 5842 0 0
3 999 oooo 10 0 0 0 0 0 0 0 685 0 0 0 0 750 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-14 : 10:02:56

duplicate post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97316

Please do not post in script library. This is for working script only. And please continue the discuss over the previous thread

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources