August 6, 2010 at 3:55 am
But I don't think it's going to change the concept in general 😉
It just leaves a little more work to do for the OP to modify the solution...
August 6, 2010 at 4:31 am
LutzM (8/6/2010)
Chris Morris-439714 (8/6/2010)
...Chris, please take the time to set up the sample data properly. The whole batch should run without errors, and the column names and types must match those in your real tables, not those I made up. If you can't be bothered then it's unlikely you will get much more help. Show some effort and enthusiasm and folks will fall over themselves to help you. Thanks.
The only error I found was the UNION ALL statement at the end of each last insert. Easy to fix. I think the solution I provided earlier is at least something to start with...
But in general you're right, sample data usually should be tested before posting.
CREATE TABLE #CR_REPORTPERIODS(
PERIODID INT IDENTITY,
PERIODNAME VARCHAR(20),
PERIODSTARTDATE] DATETIME,
PERIODENDDATE DATETIME
)
INSERT INTO #CR_REPORTPERIODS
SELECT 1, 2007-2008, '2007-07-01 00:00:00.000', '2008-06-30 00:00:00.000' UNION ALL
SELECT 2, 2008-2009, '2008-07-01 00:00:00.000', '2009-06-30 00:00:00.000' UNION ALL
SELECT 3, 2009-2010, '2009-07-01 00:00:00.000', '2010-06-30 00:00:00.000' UNION ALL
LOL here goes:
Trailing UNION ALL
Insert into an identity column without IDENTITY_INSERT ON
"]"
Numeric entered into a character column
That's enough to put me off 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 10, 2010 at 4:01 am
Sorry if the data I supplied had a few issues. I did this in about 5 minutes amongst doing a million other things! Next time I'll take more care.
LutzM - Thank you for your solution. This looks like it's giving me the expected results now I've expanded it to include my other columns and it looks a lot cleaner than before.
I'm a little concerned about the performance but I can have a look into this. Once I've done this I'll post what I come up with.
August 10, 2010 at 7:42 am
Ok I seem to be getting correct figures now so thank you LutzM. I just have one last question. At the moment the query returned will be something like the following:
1.1 1.2 .......
2007-2008 15 0
2008-2009 8 2
2009-2010 11 6
Is it easy for this to be PIVOTED so that the periods are the columns instead of rows? This is what my original question was regarding.
August 10, 2010 at 11:14 am
I would use CrossTab over PIVOT due to more flexibility:
;
WITH cte AS
(
SELECT
RP.PERIODNAME,
[1.1] = COUNT(CASE WHEN C.CASETYPE = 'F' AND C.PROPERTYTYPE = 'P' AND RIGHT(C.IRN, 2) = 'PA' AND CE.EVENTNO = -16 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END),
[1.2] = COUNT(CASE WHEN C.CASETYPE = 'F' AND C.PROPERTYTYPE = 'T' AND RIGHT(C.IRN, 2) = 'TA' AND CE.EVENTNO = -16 AND MONTH(CE.EVENTDATE) = 6 THEN C.IRN ELSE NULL END)
/*
the rest of the previous statement
*/
FROM #CASES C
LEFT OUTER JOIN [#PROPERTY] P ON P.CASEID = C.CASEID
LEFT OUTER JOIN #CASEEVENT CE ON CE.CASEID = C.CASEID
INNER JOIN #CR_REPORTPERIODS RP ON CE.EVENTDATE >= RP.PERIODSTARTDATE
AND CE.EVENTDATE < RP.PERIODENDDATE
WHERE C.CASETYPE IN ('A', 'B', 'C', 'F')
AND C.PROPERTYTYPE IN ('P', 'T', 'D')
AND RP.PERIODNAME IS NOT NULL
GROUP BY RP.PERIODNAME
), cte2 AS
(
SELECT PERIODNAME, cols, vals
FROM
(SELECT *
FROM cte) p
UNPIVOT
(vals FOR cols IN
([1.1],[1.2] --add the othe columns from the statement inside the cte
)
)AS unpvt
)
SELECT
cols,
ISNULL(MAX(CASE WHEN Periodname='2008-2009' THEN vals ELSE NULL END),0) AS [2008-2009],
ISNULL(MAX(CASE WHEN Periodname='2009-2010' THEN vals ELSE NULL END),0) AS [2009-2010]
FROM cte2
GROUP BY cols
ORDER BY cols
If you need to get this query to deal with a flexible number of periods (not hard-coded) you might want to have a look at the DynamicCrossTab article referenced in my signature.
Regarding performance:
I'd be surprised if this solution will be less efficient then calling a table [x] times within a view and join that view to another table, referncing it [y] times... But you could always post the actual execution plans for both solution so we might find some more areas for improvement (e.g. proper indexing and the like) 😉
August 11, 2010 at 1:56 am
Ok I never thought about using a CTE to accomplish this.
Again this looks perfect for what I need. At the moment I only have a couple of reporting periods but this will obviously increase over time so I'll have a read of your article on the dynamic cross tab queries.
Once again thank you so much for your help. I'll go through this and see if I can now construct my report the way I wanted to.
I'll let you know if I have any further questions 🙂
August 11, 2010 at 5:06 am
Glad I could help 😀
Would you mind sharing the performance difference for the solution you used before vs. the one I provided? Just being curious...
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply