July 23, 2012 at 12:27 am
hi guys,, i'm newbie here,, hope it can make my problem solve about T-Sql.. :-):-):-)
anyway i have a code that using case for 1 to 31 ,,
the question is when i use While script it always shows error message..
where's the error,, ??
can we use 'While' function in selecting a record ??
*i use ss2k
here's my code
declare @n int
set @n = 1
SELECT TOP 100 PERCENT ID, purchase_id, supp_add_mat_id, mat_code, asset_no, qty, require_by, require_date, require_desc, status, pr_detail_asset,
scheduled,
while @n <=31
begin
if @n = 31
CASE WHEN datepart(day, require_date) = @n THEN qty ELSE '0' END AS @n
else
CASE WHEN datepart(day, require_date) = @n THEN qty ELSE '0' END AS @n,
set @n=@n+1
end
FROM dbo.t_pr_details
July 23, 2012 at 1:53 am
You cannot use a WHILE loop to solve this problem, use CROSS TABS instead
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
http://qa.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
http://qa.sqlservercentral.com/articles/Crosstab/65048/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 23, 2012 at 2:19 am
I think this should do it:
DECLARE @n INT
SET @n = 1
WHILE @n <=31
BEGIN
SELECT ID, purchase_id, supp_add_mat_id,
mat_code, asset_no,
CASE WHEN DATEPART(dd, require_date) = @n THEN qty ELSE '0' END AS qty,
require_by, require_date, require_desc,
status, pr_detail_asset, scheduled
FROM dbo.t_pr_details
SET @n = @n + 1
END
I'm sure this isn't the best way of doing it, lots of RBAR n' all, but it's a quick and dirty solution.
I've not been able to test it as you didn't post the table definition or any sample data. If the above doesn't work please can you post the definition of the table t_pr_details (column name, datatype) and maybe ten rows of sample data? Use the IFCode tags (to the left of the reply screen) to format your code inside CODE tags to make it more legible.
Thanks.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
July 23, 2012 at 3:00 am
Assuming you want one result set returned to your client, you should consider doing it something like this:
;WITH Tally (n) AS (
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns)
SELECT ID, purchase_id, supp_add_mat_id,
mat_code, asset_no,
CASE WHEN DATEPART(dd, require_date) = n THEN qty ELSE '0' END AS qty,
require_by, require_date, require_desc,
status, pr_detail_asset, scheduled
FROM dbo.t_pr_details
CROSS APPLY Tally
Again, this is not tested because there's no DDL and sample data provided by the OP.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 23, 2012 at 8:43 pm
Kingston Dhasian (7/23/2012)
You cannot use a WHILE loop to solve this problem, use CROSS TABS insteadCross Tabs and Pivots, Part 1 – Converting Rows to Columns
http://qa.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
thx for reply my thread..
the link that you gave to me is such a great script..
but it's still use a 'Hard' code for creating a pivot.. like this code..
--===== The Pivot Example
SELECT Year,
COALESCE([1],0) AS [1st Qtr],
COALESCE([2],0) AS [2nd Qtr],
COALESCE([3],0) AS [3rd Qtr],
COALESCE([4],0) AS [4th Qtr],
COALESCE([1],0) + COALESCE([2] ,0) + COALESCE([3],0) + COALESCE([4],0) AS Total
FROM (SELECT Year, Quarter,Amount FROM #SomeTable1) AS src
PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt
ORDER BY Year
i dont want to do it,but i want to do a looping.. 🙂
July 23, 2012 at 8:50 pm
derek.colley (7/23/2012)
I think this should do it:
DECLARE @n INT
SET @n = 1
WHILE @n <=31
BEGIN
SELECT ID, purchase_id, supp_add_mat_id,
mat_code, asset_no,
CASE WHEN DATEPART(dd, require_date) = @n THEN qty ELSE '0' END AS qty,
require_by, require_date, require_desc,
status, pr_detail_asset, scheduled
FROM dbo.t_pr_details
SET @n = @n + 1
END
I'm sure this isn't the best way of doing it, lots of RBAR n' all, but it's a quick and dirty solution.
I've not been able to test it as you didn't post the table definition or any sample data. If the above doesn't work please can you post the definition of the table t_pr_details (column name, datatype) and maybe ten rows of sample data? Use the IFCode tags (to the left of the reply screen) to format your code inside CODE tags to make it more legible.
Thanks.
well , it's really takes a long time when i use this code..
but thx for trying to help me.. 🙂
this is the definition of t_pr_details,
CREATE TABLE [dbo].[t_pr_details] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[purchase_id] [bigint] NOT NULL ,
[supp_add_mat_id] [bigint] NULL ,
[mat_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[asset_no] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty] [int] NOT NULL ,
[require_by] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[require_date] [datetime] NOT NULL ,
[require_desc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [bit] NULL ,
[pr_detail_asset] [bigint] NULL ,
[scheduled] [bit] NULL
) ON [PRIMARY]
GO
how i can show the sample of data ??
July 23, 2012 at 8:52 pm
dwain.c (7/23/2012)
Assuming you want one result set returned to your client, you should consider doing it something like this:
;WITH Tally (n) AS (
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns)
SELECT ID, purchase_id, supp_add_mat_id,
mat_code, asset_no,
CASE WHEN DATEPART(dd, require_date) = n THEN qty ELSE '0' END AS qty,
require_by, require_date, require_desc,
status, pr_detail_asset, scheduled
FROM dbo.t_pr_details
CROSS APPLY Tally
Again, this is not tested because there's no DDL and sample data provided by the OP.
well when i test it to query analizer it shows error..
*i dont understand what is this code.. (i'm newbie on SS) .. but it really helpfull if this code can work.. thx.
July 23, 2012 at 9:49 pm
xmozart.ryan (7/23/2012)
dwain.c (7/23/2012)
Assuming you want one result set returned to your client, you should consider doing it something like this:
;WITH Tally (n) AS (
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns)
SELECT ID, purchase_id, supp_add_mat_id,
mat_code, asset_no,
CASE WHEN DATEPART(dd, require_date) = n THEN qty ELSE '0' END AS qty,
require_by, require_date, require_desc,
status, pr_detail_asset, scheduled
FROM dbo.t_pr_details
CROSS APPLY Tally
Again, this is not tested because there's no DDL and sample data provided by the OP.
well when i test it to query analizer it shows error..
*i dont understand what is this code.. (i'm newbie on SS) .. but it really helpfull if this code can work.. thx.
As noted, if you provide DDL and sample data in a readily consumable format, you're much more likely to get a tested and fully workable solution.
Without that, you get results like this.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 23, 2012 at 11:42 pm
dwain.c (7/23/2012)
xmozart.ryan (7/23/2012)
dwain.c (7/23/2012)
Assuming you want one result set returned to your client, you should consider doing it something like this:
;WITH Tally (n) AS (
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns)
SELECT ID, purchase_id, supp_add_mat_id,
mat_code, asset_no,
CASE WHEN DATEPART(dd, require_date) = n THEN qty ELSE '0' END AS qty,
require_by, require_date, require_desc,
status, pr_detail_asset, scheduled
FROM dbo.t_pr_details
CROSS APPLY Tally
Again, this is not tested because there's no DDL and sample data provided by the OP.
well when i test it to query analizer it shows error..
*i dont understand what is this code.. (i'm newbie on SS) .. but it really helpfull if this code can work.. thx.
As noted, if you provide DDL and sample data in a readily consumable format, you're much more likely to get a tested and fully workable solution.
Without that, you get results like this.
well i gave the DDL ,,
but for sample data , how i can share it ??
July 23, 2012 at 11:47 pm
OK I see the DDL now, sorry I missed it earlier.
Sample data can be done with an INSERT like as follows:
INSERT INTO dbo.Table
SELECT 1,2,3,4 -- As many values as there are table columns
UNION ALL SELECT 3,4,5,6 -- etc.
Suggest you also provide expected results (so your helpers can verify their work is according to your expectations).
Be sure to provide enough rows of sample data (especially any strange data conditions) so that you can see the results are what you want.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 24, 2012 at 12:36 am
dwain.c (7/23/2012)
OK I see the DDL now, sorry I missed it earlier.Sample data can be done with an INSERT like as follows:
INSERT INTO dbo.Table
SELECT 1,2,3,4 -- As many values as there are table columns
UNION ALL SELECT 3,4,5,6 -- etc.
Suggest you also provide expected results (so your helpers can verify their work is according to your expectations).
Be sure to provide enough rows of sample data (especially any strange data conditions) so that you can see the results are what you want.
this is the sample data..
declare @n int
while @n < 10
begin
insert into t_pr_details (purchase_id,mat_code,qty,require_by,require_date,require_desc,scheduled)
values
(5,'307-021-0005',100,'STR01020103',SELECT DATEADD(day, @n, getdate()),'TEST',1)
end
set @n=@n+1
😀
July 24, 2012 at 3:07 am
xmozart.ryan (7/23/2012)
Kingston Dhasian (7/23/2012)
You cannot use a WHILE loop to solve this problem, use CROSS TABS insteadCross Tabs and Pivots, Part 1 – Converting Rows to Columns
http://qa.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
thx for reply my thread..
the link that you gave to me is such a great script..
but it's still use a 'Hard' code for creating a pivot.. like this code..
--===== The Pivot Example
SELECT Year,
COALESCE([1],0) AS [1st Qtr],
COALESCE([2],0) AS [2nd Qtr],
COALESCE([3],0) AS [3rd Qtr],
COALESCE([4],0) AS [4th Qtr],
COALESCE([1],0) + COALESCE([2] ,0) + COALESCE([3],0) + COALESCE([4],0) AS Total
FROM (SELECT Year, Quarter,Amount FROM #SomeTable1) AS src
PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt
ORDER BY Year
i dont want to do it,but i want to do a looping.. 🙂
In that case, you can use Dynamic CROSS TABS which is explained in the second link that I have provided
You will have to use Dynamic SQL, but I think its the only way to solve your problem dynamically without hardcoding.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 24, 2012 at 6:43 pm
Let me apologize for my original bone-headed post. Not only did I screw up the syntax (I blame my current cognitive overload), I failed to notice that you're using SQL 2000, which seriously limits your options here (e.g., you can't use a PIVOT). Kingston seems to have noted this by suggesting cross tabs.
I will look at this again and post something fresh (and hopefully tested/working) shortly.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 24, 2012 at 7:05 pm
Using only the stone knives and bear skins available in SS2K, I think this will run without error on that ancient platform, and may produce something somewhat close to what you are looking for.
CREATE TABLE #t_pr_details (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[purchase_id] [bigint] NOT NULL ,
[supp_add_mat_id] [bigint] NULL ,
[mat_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[asset_no] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty] [int] NOT NULL ,
[require_by] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[require_date] [datetime] NOT NULL ,
[require_desc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [bit] NULL ,
[pr_detail_asset] [bigint] NULL ,
[scheduled] [bit] NULL
) ON [PRIMARY]
insert into #t_pr_details (purchase_id,mat_code,qty,require_by,require_date,require_desc,scheduled)
SELECT purchase_id,mat_code,qty,require_by,DATEADD(day, n, getdate()),require_desc,scheduled
FROM (
SELECT purchase_id=5
,mat_code='307-021-0005'
,qty=100
,require_by='STR01020103'
,require_desc='TEST'
,scheduled=1) a
INNER JOIN (
SELECT n
FROM (
SELECT n=1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
UNION ALL SELECT 30 UNION ALL SELECT 31
) b
) c ON 1=1
SELECT purchase_id,mat_code,qty,require_by,require_desc,scheduled
,[Day 1]=MAX(CASE WHEN datepart(day, require_date) = 1 THEN Qty ELSE '0' END)
,[Day 2]=MAX(CASE WHEN datepart(day, require_date) = 2 THEN Qty ELSE '0' END)
,[Day 3]=MAX(CASE WHEN datepart(day, require_date) = 3 THEN Qty ELSE '0' END)
,[Day 4]=MAX(CASE WHEN datepart(day, require_date) = 4 THEN Qty ELSE '0' END)
,[Day 5]=MAX(CASE WHEN datepart(day, require_date) = 5 THEN Qty ELSE '0' END)
,[Day 6]=MAX(CASE WHEN datepart(day, require_date) = 6 THEN Qty ELSE '0' END)
,[Day 7]=MAX(CASE WHEN datepart(day, require_date) = 7 THEN Qty ELSE '0' END)
,[Day 8]=MAX(CASE WHEN datepart(day, require_date) = 8 THEN Qty ELSE '0' END)
,[Day 9]=MAX(CASE WHEN datepart(day, require_date) = 9 THEN Qty ELSE '0' END)
,[Day 10]=MAX(CASE WHEN datepart(day, require_date) = 10 THEN Qty ELSE '0' END)
,[Day 11]=MAX(CASE WHEN datepart(day, require_date) = 11 THEN Qty ELSE '0' END)
,[Day 12]=MAX(CASE WHEN datepart(day, require_date) = 12 THEN Qty ELSE '0' END)
,[Day 13]=MAX(CASE WHEN datepart(day, require_date) = 13 THEN Qty ELSE '0' END)
,[Day 14]=MAX(CASE WHEN datepart(day, require_date) = 14 THEN Qty ELSE '0' END)
,[Day 15]=MAX(CASE WHEN datepart(day, require_date) = 15 THEN Qty ELSE '0' END)
,[Day 16]=MAX(CASE WHEN datepart(day, require_date) = 16 THEN Qty ELSE '0' END)
,[Day 17]=MAX(CASE WHEN datepart(day, require_date) = 17 THEN Qty ELSE '0' END)
,[Day 18]=MAX(CASE WHEN datepart(day, require_date) = 18 THEN Qty ELSE '0' END)
,[Day 19]=MAX(CASE WHEN datepart(day, require_date) = 19 THEN Qty ELSE '0' END)
,[Day 20]=MAX(CASE WHEN datepart(day, require_date) = 20 THEN Qty ELSE '0' END)
,[Day 21]=MAX(CASE WHEN datepart(day, require_date) = 21 THEN Qty ELSE '0' END)
,[Day 22]=MAX(CASE WHEN datepart(day, require_date) = 22 THEN Qty ELSE '0' END)
,[Day 23]=MAX(CASE WHEN datepart(day, require_date) = 23 THEN Qty ELSE '0' END)
,[Day 24]=MAX(CASE WHEN datepart(day, require_date) = 24 THEN Qty ELSE '0' END)
,[Day 25]=MAX(CASE WHEN datepart(day, require_date) = 25 THEN Qty ELSE '0' END)
,[Day 26]=MAX(CASE WHEN datepart(day, require_date) = 26 THEN Qty ELSE '0' END)
,[Day 27]=MAX(CASE WHEN datepart(day, require_date) = 27 THEN Qty ELSE '0' END)
,[Day 28]=MAX(CASE WHEN datepart(day, require_date) = 28 THEN Qty ELSE '0' END)
,[Day 29]=MAX(CASE WHEN datepart(day, require_date) = 29 THEN Qty ELSE '0' END)
,[Day 30]=MAX(CASE WHEN datepart(day, require_date) = 30 THEN Qty ELSE '0' END)
,[Day 31]=MAX(CASE WHEN datepart(day, require_date) = 31 THEN Qty ELSE '0' END)
FROM #t_pr_details
GROUP BY purchase_id,mat_code,qty,require_by,require_desc,scheduled
DROP TABLE #t_pr_details
Note that I have even conjured up an antidiluvian tally table to improve the efficiency of your initial data INSERT.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 24, 2012 at 7:34 pm
dwain.c (7/24/2012)
Using only the stone knives and bear skins available in SS2K, I think this will run without error on that ancient platform, and may produce something somewhat close to what you are looking for.
CREATE TABLE #t_pr_details (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[purchase_id] [bigint] NOT NULL ,
[supp_add_mat_id] [bigint] NULL ,
[mat_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[asset_no] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty] [int] NOT NULL ,
[require_by] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[require_date] [datetime] NOT NULL ,
[require_desc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [bit] NULL ,
[pr_detail_asset] [bigint] NULL ,
[scheduled] [bit] NULL
) ON [PRIMARY]
insert into #t_pr_details (purchase_id,mat_code,qty,require_by,require_date,require_desc,scheduled)
SELECT purchase_id,mat_code,qty,require_by,DATEADD(day, n, getdate()),require_desc,scheduled
FROM (
SELECT purchase_id=5
,mat_code='307-021-0005'
,qty=100
,require_by='STR01020103'
,require_desc='TEST'
,scheduled=1) a
INNER JOIN (
SELECT n
FROM (
SELECT n=1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
UNION ALL SELECT 30 UNION ALL SELECT 31
) b
) c ON 1=1
SELECT purchase_id,mat_code,qty,require_by,require_desc,scheduled
,[Day 1]=MAX(CASE WHEN datepart(day, require_date) = 1 THEN Qty ELSE '0' END)
,[Day 2]=MAX(CASE WHEN datepart(day, require_date) = 2 THEN Qty ELSE '0' END)
,[Day 3]=MAX(CASE WHEN datepart(day, require_date) = 3 THEN Qty ELSE '0' END)
,[Day 4]=MAX(CASE WHEN datepart(day, require_date) = 4 THEN Qty ELSE '0' END)
,[Day 5]=MAX(CASE WHEN datepart(day, require_date) = 5 THEN Qty ELSE '0' END)
,[Day 6]=MAX(CASE WHEN datepart(day, require_date) = 6 THEN Qty ELSE '0' END)
,[Day 7]=MAX(CASE WHEN datepart(day, require_date) = 7 THEN Qty ELSE '0' END)
,[Day 8]=MAX(CASE WHEN datepart(day, require_date) = 8 THEN Qty ELSE '0' END)
,[Day 9]=MAX(CASE WHEN datepart(day, require_date) = 9 THEN Qty ELSE '0' END)
,[Day 10]=MAX(CASE WHEN datepart(day, require_date) = 10 THEN Qty ELSE '0' END)
,[Day 11]=MAX(CASE WHEN datepart(day, require_date) = 11 THEN Qty ELSE '0' END)
,[Day 12]=MAX(CASE WHEN datepart(day, require_date) = 12 THEN Qty ELSE '0' END)
,[Day 13]=MAX(CASE WHEN datepart(day, require_date) = 13 THEN Qty ELSE '0' END)
,[Day 14]=MAX(CASE WHEN datepart(day, require_date) = 14 THEN Qty ELSE '0' END)
,[Day 15]=MAX(CASE WHEN datepart(day, require_date) = 15 THEN Qty ELSE '0' END)
,[Day 16]=MAX(CASE WHEN datepart(day, require_date) = 16 THEN Qty ELSE '0' END)
,[Day 17]=MAX(CASE WHEN datepart(day, require_date) = 17 THEN Qty ELSE '0' END)
,[Day 18]=MAX(CASE WHEN datepart(day, require_date) = 18 THEN Qty ELSE '0' END)
,[Day 19]=MAX(CASE WHEN datepart(day, require_date) = 19 THEN Qty ELSE '0' END)
,[Day 20]=MAX(CASE WHEN datepart(day, require_date) = 20 THEN Qty ELSE '0' END)
,[Day 21]=MAX(CASE WHEN datepart(day, require_date) = 21 THEN Qty ELSE '0' END)
,[Day 22]=MAX(CASE WHEN datepart(day, require_date) = 22 THEN Qty ELSE '0' END)
,[Day 23]=MAX(CASE WHEN datepart(day, require_date) = 23 THEN Qty ELSE '0' END)
,[Day 24]=MAX(CASE WHEN datepart(day, require_date) = 24 THEN Qty ELSE '0' END)
,[Day 25]=MAX(CASE WHEN datepart(day, require_date) = 25 THEN Qty ELSE '0' END)
,[Day 26]=MAX(CASE WHEN datepart(day, require_date) = 26 THEN Qty ELSE '0' END)
,[Day 27]=MAX(CASE WHEN datepart(day, require_date) = 27 THEN Qty ELSE '0' END)
,[Day 28]=MAX(CASE WHEN datepart(day, require_date) = 28 THEN Qty ELSE '0' END)
,[Day 29]=MAX(CASE WHEN datepart(day, require_date) = 29 THEN Qty ELSE '0' END)
,[Day 30]=MAX(CASE WHEN datepart(day, require_date) = 30 THEN Qty ELSE '0' END)
,[Day 31]=MAX(CASE WHEN datepart(day, require_date) = 31 THEN Qty ELSE '0' END)
FROM #t_pr_details
GROUP BY purchase_id,mat_code,qty,require_by,require_desc,scheduled
DROP TABLE #t_pr_details
Note that I have even conjured up an antidiluvian tally table to improve the efficiency of your initial data INSERT.
actually i know that method to create a pivot, but it really suck if i have to write down the script that repeated 31 times..
i want to do some looping,, as kingston said, i should take a look at the cross tab part 2, but when i read it, i can't understand it.. (may be it bcoz i'm new using SS)..
any solution ??
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply