July 24, 2012 at 8:00 pm
I'm using the cross tab approach and it's quite easy to construct with copy and paste.
Read my mantra and avoid loops when they're not necessary. It is better to write out the code than construct it with a loop and run it using Dynamic SQL. Much more understandable also.
And be nice. It's not nice to say a solution "sucks" when your help is coming free of charge.
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 8:06 pm
dwain.c (7/24/2012)
I'm using the cross tab approach and it's quite easy to construct with copy and paste.Read my mantra and avoid loops when they're not necessary. It is better to write out the code than construct it with a loop and run it using Dynamic SQL. Much more understandable also.
And be nice. not nice t It'so say a solution "sucks" when your help is coming free of charge.
no no, i'm apologize for said it..
but it's not for solution that all of you guys that gave to me..
i really thankfull for all of you guys..
i said "sucks" for my own self, coz i really really dont want to do something that repeatable like that(not your code,but my own code that i had built, it really like yours)..
*why we used reapeatable script if we can create a looping script ? 🙂
once again i said so sorry for not to be nice.. 🙂
July 24, 2012 at 8:11 pm
OK apology accepted.
The real question is do you have something that works and does it perform satisfactorily?
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 8:18 pm
dwain.c (7/24/2012)
OK apology accepted.The real question is do you have something that works and does it perform satisfactorily?
yup..
here's the code..
SELECT purchase_id,mat_code,qty,require_by,require_desc,scheduled,
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 1 THEN qty ELSE '0' END), 0)) AS [1],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 2 THEN qty ELSE '0' END), 0)) AS [2],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 3 THEN qty ELSE '0' END), 0)) AS [3],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 4 THEN qty ELSE '0' END), 0)) AS [4],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 5 THEN qty ELSE '0' END), 0)) AS [5],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 6 THEN qty ELSE '0' END), 0)) AS [6],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 7 THEN qty ELSE '0' END), 0)) AS [7],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 8 THEN qty ELSE '0' END), 0)) AS [8],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 9 THEN qty ELSE '0' END), 0)) AS [9],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 10 THEN qty ELSE '0' END), 0)) AS [10],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 11 THEN qty ELSE '0' END), 0)) AS [11],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 12 THEN qty ELSE '0' END), 0)) AS [12],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 13 THEN qty ELSE '0' END), 0)) AS [13],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 14 THEN qty ELSE '0' END), 0)) AS [14],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 15 THEN qty ELSE '0' END), 0)) AS [15],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 16 THEN qty ELSE '0' END), 0)) AS [16],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 17 THEN qty ELSE '0' END), 0)) AS [17],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 18 THEN qty ELSE '0' END), 0)) AS [18],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 19 THEN qty ELSE '0' END), 0)) AS [19],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 20 THEN qty ELSE '0' END), 0)) AS [20],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 21 THEN qty ELSE '0' END), 0)) AS [21],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 22 THEN qty ELSE '0' END), 0)) AS [22],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 23 THEN qty ELSE '0' END), 0)) AS [23],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 24 THEN qty ELSE '0' END), 0)) AS [24],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 25 THEN qty ELSE '0' END), 0)) AS [25],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 26 THEN qty ELSE '0' END), 0)) AS [26],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 27 THEN qty ELSE '0' END), 0)) AS [27],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 28 THEN qty ELSE '0' END), 0)) AS [28],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 29 THEN qty ELSE '0' END), 0)) AS [29],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 30 THEN qty ELSE '0' END), 0)) AS [30],
SUM(ISNULL((CASE WHEN datepart(day, require_date) = 31 THEN qty ELSE '0' END), 0)) AS [31]
FROM t_pr_details
GROUP BY purchase_id,mat_code,qty,require_by,require_desc,scheduled
it works, but i think i can create a looping script / dynamic script (but the problem is i dont know how to create it)
July 24, 2012 at 8:25 pm
The code you posted is likely to perform better than any looping/dynamic script you can create.
If you just want to do it as an exercise, that's fine. But for production code, you should always be using the solution that performs the best. You never know when your application will become the "mission-critical, high volume" application that brings poor performing code to its knees.
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 25, 2012 at 2:30 am
As Dwain has already said, the hard coding method, though tedious is the best for Production and quite readable
I have presented below the code to do it using Dynamic SQL. Use it when you feel hard coding is almost impossible.
I have used a table spt_values in the script, you can use a Tally Table instead of this
For more information on Tally table and its uses, you can check the below link
Create a Tally or Numbers Table
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
DECLARE@SQL VARCHAR(4000)
DECLARE@DynamicSelect VARCHAR(4000)
-- Construct the repetitive part using Dynamic SQL
SELECT@DynamicSelect= COALESCE(@DynamicSelect + ', ', '')
+ ' SUM( ISNULL( ( CASE WHEN DATEPART(DAY, require_date ) = ' + CAST( number AS VARCHAR(2) ) + ' THEN qty ELSE 0 END ), 0 ) ) AS [' + CAST( number AS VARCHAR(2) ) + ']'
FROMmaster.dbo.spt_values -- You can use a Tally Table instead of this
WHEREtype = 'P' AND number BETWEEN 1 AND 31
SET@SQL= ' SELECTpurchase_id, mat_code, qty, require_by, require_desc, scheduled, '
+@DynamicSelect
+ ' FROMt_pr_details '
+ ' GROUP BY purchase_id, mat_code, qty, require_by, require_desc, scheduled '
EXECUTE ( @SQL )
--PRINT( SQL )
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 25, 2012 at 3:16 am
Kingston Dhasian (7/25/2012)
As Dwain has already said, the hard coding method, though tedious is the best for Production and quite readableI have presented below the code to do it using Dynamic SQL. Use it when you feel hard coding is almost impossible.
I have used a table spt_values in the script, you can use a Tally Table instead of this
For more information on Tally table and its uses, you can check the below link
Create a Tally or Numbers Table
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
DECLARE@SQL VARCHAR(4000)DECLARE@DynamicSelect VARCHAR(4000)
-- Construct the repetitive part using Dynamic SQL
SELECT@DynamicSelect= COALESCE(@DynamicSelect + ', ', '')
+ ' SUM( ISNULL( ( CASE WHEN DATEPART(DAY, require_date ) = ' + CAST( number AS VARCHAR(2) ) + ' THEN qty ELSE 0 END ), 0 ) ) AS [' + CAST( number AS VARCHAR(2) ) + ']'
FROMmaster.dbo.spt_values -- You can use a Tally Table instead of this
WHEREtype = 'P' AND number BETWEEN 1 AND 31
SET@SQL= ' SELECTpurchase_id, mat_code, qty, require_by, require_desc, scheduled, '
+@DynamicSelect
+ ' FROMt_pr_details '
+ ' GROUP BY purchase_id, mat_code, qty, require_by, require_desc, scheduled '
EXECUTE ( @SQL )
--PRINT( SQL )
thx for share it,, but i'm really sorry coz i cannot understand what is it ??
:crazy:
*once again i'm really newbie on SS..
July 25, 2012 at 4:18 am
dwain.c (7/24/2012)
The code you posted is likely to perform better than any looping/dynamic script you can create.If you just want to do it as an exercise, that's fine. But for production code, you should always be using the solution that performs the best. You never know when your application will become the "mission-critical, high volume" application that brings poor performing code to its knees.
wow, i thought if i can create a dynamic script it'll be best for production..
(coz i dont need to modify it)..
July 25, 2012 at 4:36 am
xmozart.ryan (7/25/2012)
dwain.c (7/24/2012)
The code you posted is likely to perform better than any looping/dynamic script you can create.If you just want to do it as an exercise, that's fine. But for production code, you should always be using the solution that performs the best. You never know when your application will become the "mission-critical, high volume" application that brings poor performing code to its knees.
wow, i thought if i can create a dynamic script it'll be best for production..
(coz i dont need to modify it)..
In terms of performance, avoiding the Dynamic script will be better
You are probably thinking from maintenance perspective, but it might backfire in terms of performance
If you are finding it difficult to understand the Dynamic code, I would suggest you to avoid it
Don't put any code in Production which you don't understand or which doesn't perform well
Try to learn simple Dynamic SQL queries and slowly proceed to complicated queries
You can keep the code I provided for future reference when you need
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 25, 2012 at 8:32 am
xmozart.ryan (7/25/2012)
thx for share it,, but i'm really sorry coz i cannot understand what is it ??:crazy:
*once again i'm really newbie on SS..
Try printing the code stored in @DynamicSelect and @SQL.
Also look for help on EXECUTE or sp_executesql, those are a way to start understanding dynamic queries.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply