April 12, 2019 at 2:48 pm
Comments posted to this topic are about the item CTE Example with Pivot operator
April 12, 2019 at 2:54 pm
My only complaint about your code is the semicolon preceding the WITH for your CTE. When creating a CTE it is required that the PREVIOUS statement be terminated with a semicolon, not the CTE begin with a CTE. It is obvious that people doing this leads others not familiar with writing CTEs to think that the semicolon is required before the WITH when declaring a CTE.
May 2, 2019 at 4:03 pm
Being picky, I do have a few other minor comments about the code:
May 2, 2019 at 4:15 pm
Coalesce() with two arguments is equivalent to IsNull() ... might as well save yourself some typing
Actually, Phil, I feel I need to correct you on that one. ISNULL
and COALESCE
do behave differently. COALESCE
is a shortcut function for a CASE
expression and therefore uses Data Type Precedence to determine the return value. ISNULL
, however, returns the data type of the first parameter.
I certainly agree on the dates though. Most of those would fail to convert to a date
on my instance, or would provide the wrong value.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 2, 2019 at 4:47 pm
If we're going to be picky, I have some other things to note:
Those were just formatting issues, but my main problems are:
My version would turn out like this:
WITH preAggregate AS(
SELECT YEAR(order_dt) as [Year],
MONTH( [order_dt]) AS [month],
SUM( [amount]) AS [amount]
FROM orders
GROUP BY YEAR(order_dt),
MONTH( [order_dt])
)
SELECT [Year],
SUM( [amount]) AS [Annual Total],
SUM( CASE WHEN [month] = 1 THEN [amount] ELSE 0 END) AS [Jan],
SUM( CASE WHEN [month] = 2 THEN [amount] ELSE 0 END) AS [Feb],
SUM( CASE WHEN [month] = 3 THEN [amount] ELSE 0 END) AS [Mar],
SUM( CASE WHEN [month] = 4 THEN [amount] ELSE 0 END) AS [Apr],
SUM( CASE WHEN [month] = 5 THEN [amount] ELSE 0 END) AS [May],
SUM( CASE WHEN [month] = 6 THEN [amount] ELSE 0 END) AS [Jun],
SUM( CASE WHEN [month] = 7 THEN [amount] ELSE 0 END) AS [Jul],
SUM( CASE WHEN [month] = 8 THEN [amount] ELSE 0 END) AS [Aug],
SUM( CASE WHEN [month] = 9 THEN [amount] ELSE 0 END) AS [Sep],
SUM( CASE WHEN [month] = 10 THEN [amount] ELSE 0 END) AS [Oct],
SUM( CASE WHEN [month] = 11 THEN [amount] ELSE 0 END) AS [Nov],
SUM( CASE WHEN [month] = 12 THEN [amount] ELSE 0 END) AS [Dec]
FROM preAggregate
GROUP BY [Year];
May 2, 2019 at 5:38 pm
Thanks Thom, quite right! I should have added the text "in this case".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy