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-13 : 16:04:22

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-14 : 05:46:13

moved from script library

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-14 : 10:26:06

Why do the ordering of headers matter?
Since you already do a "rollover" for group2!

What if you have 99 groups? There is no way to oversee the information.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-14 : 10:26:22

[code]DECLARE @Sample TABLE (Grp TINYINT, OrderID SMALLINT, OrderDesc VARCHAR(10), DateRec DATETIME, IstDate DATETIME, ShippedCases SMALLINT)

INSERT @Sample
SELECT 1, 123, 'aaaa', '1/1/2006', '1/25/2008', 200 UNION ALL
SELECT 1, 123, 'aaaa', '1/26/2007', '1/25/2008', 258 UNION ALL
SELECT 1, 123, 'aaaa', '2/1/2007', '1/25/2008', 244 UNION ALL
SELECT 1, 224, 'qqqq', '2/8/2007', '1/25/2008', 244 UNION ALL
SELECT 1, 224, 'qqqq', '2/15/2007', '1/25/2008', 245 UNION ALL
SELECT 2, 333, 'www', '2/1/2007', '2/8/2008', 2574 UNION ALL
SELECT 2, 333, 'www', '2/15/2007', '2/8/2008', 898 UNION ALL
SELECT 3, 999, 'oooo', '2/1/2007', '3/5/2008', 750 UNION ALL
SELECT 3, 888, 'mmm', '1/26/2007', '3/5/2008', 5842 UNION ALL
SELECT 3, 999, 'oooo', '3/12/2007', '3/5/2008', 685[/code]

E 12°55'05.25"
N 56°04'39.16"

mercybthomas74
Starting Member

3 Posts

Posted - 2008-02-14 : 11:28:16

if you look at it the wk is incrementing as column name based on the DateRec
But the wk only start based on the 1st shipped

For Group 1 the highest 1st shipped is 4

Group OrderId OrderDesc wk4 wk5 wk6 wk7 wk8 wk9 wk10 wk11 wk1 wk2 wk3
1 123 aaaa 258 244 0 0 0 0 0 0 200 0 0
1 224 qqqq 0 0 244 245 0 0 0 0 0 0 0

For Group 2 the highest 1st shipped is 6

Group OrderId OrderDesc Wk6 Wk7 Wk8 Wk9 Wk10 Wk11 Wk1 Wk2 Wk3 Wk4 Wk5
2 333 www 258 244 0 0 0 0 0 0 0 0 2574

For Group 3 the highest 1st shipped is 10

Group OrderId OrderDesc Wk10 Wk11 Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Wk7 Wk8 Wk9
3 333 www 0 685 0 0 0 5842 0 0 0 0 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-14 : 12:51:48

And again, if you are so concerned about wht column headers, why don't you return 1 resulset for each group?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources