November 10, 2009 at 7:23 am
I have 3 SQL below and wonder if there is anyway I could combine them all to produce one SQL with 3 fields per month
So I would have something like this
Area SQL1-April SQL2-April SQL3-April SQL1-May etc
SQL1
SELECT [Area],
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 THEN 1 ELSE 0 END) AS March,
COUNT([FTFMarker]) AS 'Total'
FROM dbo.IBFirstTimeCompletion
WHERE [FTFMarker] = 1
GROUP BY [Region], [Area]
ORDER BY [Region], [Area]
SQL2
SELECT [Area],
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 THEN 1 ELSE 0 END) AS March,
COUNT([FTFMarker]) AS 'Total'
FROM dbo.IBFirstTimeCompletion
WHERE [FTFMarker] IN (99, 0)
GROUP BY [Region], [Area]
ORDER BY [Region], [Area]
SQL3
SELECT [Area],
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 THEN 1 ELSE 0 END) AS March,
COUNT([Appt]) AS 'Total'
FROM dbo.IBFirstTimeCompletion
WHERE [Appt] = 'Appt1'
GROUP BY [Region], [Area]
ORDER BY [Region], [Area]
November 10, 2009 at 8:27 am
Hi,
Using a CTE you should be able to get the desired effect.
Please see example code below...
Let me know if you have any issues...
🙂
;WITH cteSQL1 as
(
SELECT
ROW_NUMBER() OVER (ORDER BY AREA)AS RowNumber,
[Area],
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 THEN 1 ELSE 0 END) AS March,
COUNT([FTFMarker]) AS 'Total'
FROM dbo.IBFirstTimeCompletion
WHERE [FTFMarker] = 1
GROUP BY [Region], [Area]
),
cteSQL2 as
(
SELECT
ROW_NUMBER() OVER (ORDER BY AREA)AS RowNumber,
[Area],
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 THEN 1 ELSE 0 END) AS March,
COUNT([FTFMarker]) AS 'Total'
FROM dbo.IBFirstTimeCompletion
WHERE [FTFMarker] IN (99, 0)
GROUP BY [Region], [Area]
),
cteSQL3 as
(
SELECT
ROW_NUMBER() OVER (ORDER BY AREA)AS RowNumber,
[Area],
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 THEN 1 ELSE 0 END) AS March,
COUNT([Appt]) AS 'Total'
FROM dbo.IBFirstTimeCompletion
WHERE [Appt] = 'Appt1'
GROUP BY [Region], [Area]
)
SELECT
cteSQL1.April,
cteSQL2.April,
cteSQL3.April,
cteSQL1.May,
cteSQL2.May,
cteSQL3.May,
cteSQL1.June,
cteSQL2.June,
cteSQL3.June
-- etc
-- filling the rest
FROM cteSQL1 c1
JOIN cteSQL2 c2 on c1.RowNumber = c2.RowNumber
JOIN cteSQL3 c3 on c2.RowNumber = c3.RowNumber
November 10, 2009 at 8:41 am
Thanks for this 🙂
I've had a look at and tried to run it. I get an error
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "cteSQL1.April" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "cteSQL2.April" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "cteSQL3.April" could not be bound.
How can I get around this?
November 10, 2009 at 9:13 am
My bad, try this one...
;WITH cteSQL1 as
(
SELECT
ROW_NUMBER() OVER (ORDER BY AREA)AS RowNumber,
[Area],
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 THEN 1 ELSE 0 END) AS March,
COUNT([FTFMarker]) AS 'Total'
FROM dbo.IBFirstTimeCompletion
WHERE [FTFMarker] = 1
GROUP BY [Region], [Area]
),
cteSQL2 as
(
SELECT
ROW_NUMBER() OVER (ORDER BY AREA)AS RowNumber,
[Area],
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 THEN 1 ELSE 0 END) AS March,
COUNT([FTFMarker]) AS 'Total'
FROM dbo.IBFirstTimeCompletion
WHERE [FTFMarker] IN (99, 0)
GROUP BY [Region], [Area]
),
cteSQL3 as
(
SELECT
ROW_NUMBER() OVER (ORDER BY AREA)AS RowNumber,
[Area],
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 THEN 1 ELSE 0 END) AS March,
COUNT([Appt]) AS 'Total'
FROM dbo.IBFirstTimeCompletion
WHERE [Appt] = 'Appt1'
GROUP BY [Region], [Area]
)
SELECT
c1.April,
c2.April,
c3.April,
c1.May,
c2.May,
c3.May,
c1.June,
c2.June,
c3.June
-- etc
-- filling the rest
FROM cteSQL1 c1
JOIN cteSQL2 c2 on c1.RowNumber = c2.RowNumber
JOIN cteSQL3 c3 on c2.RowNumber = c3.RowNumber
November 10, 2009 at 1:56 pm
Excellent, thanks for this it works a treat 😀
I just need to understand how you have done it now :hehe:
Thanks again
November 10, 2009 at 2:19 pm
Why don't you add the WHERE clause to your CASE statement and do all in one query?
To get the count for each group would be the same as do SUM(CASE WHEN .. THEN 1 ELSE 0 END)...
SELECT
[Area],
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS April1,
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS April2,
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS April3,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS May1,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS May2,
SUM(CASE WHEN [Month] = 5 AND [Year] = 2009 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS May3,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS June1,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS June2,
SUM(CASE WHEN [Month] = 6 AND [Year] = 2009 AND [[Appt] = 'Appt1' THEN 1 ELSE 0 END) AS June3,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS July1,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS July2,
SUM(CASE WHEN [Month] = 7 AND [Year] = 2009 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS July3,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS August1,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS August2,
SUM(CASE WHEN [Month] = 8 AND [Year] = 2009 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS August3,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS September1,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS September2,
SUM(CASE WHEN [Month] = 9 AND [Year] = 2009 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS September3,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS October1,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS October2,
SUM(CASE WHEN [Month] = 10 AND [Year] = 2009 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS October3,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS November1,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS November2,
SUM(CASE WHEN [Month] = 11 AND [Year] = 2009 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS November3,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS December1,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS December2,
SUM(CASE WHEN [Month] = 12 AND [Year] = 2009 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS December3,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS January1,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS January2,
SUM(CASE WHEN [Month] = 1 AND [Year] = 2010 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS January3,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS February1,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS February2,
SUM(CASE WHEN [Month] = 2 AND [Year] = 2010 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS February3,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 AND [FTFMarker] = 1 THEN 1 ELSE 0 END) AS March1,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 AND [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS March2,
SUM(CASE WHEN [Month] = 3 AND [Year] = 2010 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS March3,
SUM (CASE WHEN [FTFMarker] =1 THEN 1 ELSE 0 END) AS 'TotalFTFMarker_1',
SUM (CASE WHEN [FTFMarker] IN (99, 0) THEN 1 ELSE 0 END) AS 'TotalFTFMarker_99',
SUM (CASE WHEN [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS 'TotalAppt1'
FROM dbo.IBFirstTimeCompletion
WHERE [FTFMarker] = 1
GROUP BY [Region], [Area]
November 11, 2009 at 2:44 am
Jez,
For an explanation on how Common Table Expressions (CTE) work please read the following article...
http://www.4guysfromrolla.com/webtech/071906-1.shtml
lmu92, I agree you could do the same in one query but I find that it is both more maintainable and readable if you use the CTE method. Since CTE was introduced for SQL Server 2005 - why not use it when applicable.
Pleased it helped.
November 11, 2009 at 2:46 am
Thanks for that, much simpler SQL...
I have a problem with the 3rd SQL, all the lines like this dont add the correct amount
SUM(CASE WHEN [Month] = 4 AND [Year] = 2009 AND [Appt] = 'Appt1' THEN 1 ELSE 0 END) AS April3,
when they add up its around 80 or so different than my original query and the CTE SQL
How can I get them to match?
November 11, 2009 at 3:00 am
Jez, which ever method you choose, you should still familiarise yourself with CTEs because they are v useful.
November 11, 2009 at 5:09 am
Lewis Dowson (11/11/2009)
lmu92, I agree you could do the same in one query but I find that it is both more maintainable and readable if you use the CTE method. Since CTE was introduced for SQL Server 2005 - why not use it when applicable.Pleased it helped.
I don't think the CTE is more maintainable. But maintainability is not a hard fact to measure against... It depends on the coding standard that are used - if the CTE for this scenario is the only place in the whole environment where it is used it may not be the best way to do.
If it's common practice then it's easier to maintain. I agree.
The reason I don't recommend the CTE here is simple: performance.
Your CTE solution require to access the table three times whereas mine does it in one path. (If we'd have sample data to test against we could compare execution plans...).
Another option I probably would have used: dynamic SQL.
In order to maintain this code I think it's even easier than the other three version discussed so far (three separate queries, CTE solution and "select-all-in-one" solution. But the aforementioned statement applies as well: if dynamic SQL is not used very often, it's harder to maintain than the other ones the people in charge are "used to".
DECLARE @sql VARCHAR(max)
SELECT @sql =
COALESCE(@sql,'') + ',
SUM(CASE WHEN [Month] = ' + CAST (month(dateadd(month,t1.cnt,'20090301')) as char(2)) +
' AND [Year] = ' + CAST (year(dateadd(month,t1.cnt,'20090301')) as char(4)) +
' AND ' + t2.Field + ' THEN 1 ELSE 0 END) AS ' + datename(month,(dateadd(month,t1.cnt,'20090301'))) + cast(t2.alias as char(1))
FROM (SELECT 1 as cnt 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 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12
) AS t1 -- could be replaced by a tally table if available
CROSS APPLY
(SELECT '[FTFMarker] = 1' as Field, 1 as alias, 'TotalFTFMarker_1' as TotalName UNION ALL
SELECT '[FTFMarker] IN (99, 0)' , 2,'TotalFTFMarker_99' UNION ALL
SELECT '[Appt] = ''Appt1''' , 3,'TotalAppt1'
) AS t2 -- could be replaced by a report control table
ORDER BY t1.cnt,t2.alias
select @sql=stuff(@sql,1,2,'')
select @sql= @sql + ',
SUM(CASE WHEN ' + t2.Field + ' THEN 1 ELSE 0 END) AS ' + t2.TotalName
FROM
(
SELECT '[FTFMarker] = 1' as Field, 1 as alias, 'TotalFTFMarker_1' as TotalName UNION ALL
SELECT '[FTFMarker] IN (99, 0)' , 2,'TotalFTFMarker_99' UNION ALL
SELECT '[Appt] = ''Appt1''' , 3,'TotalAppt1'
) t2 -- could be replaced by the same report control table as above
ORDER BY t2.alias
PRINT @sql
Edit: Typo fixed.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply