My head is well and truly mashed and I could do with some help please...
I need to write a script to count the number of records opened and closed in certain dates. This will require a pivot eventually but essentially we're looking at two tables - one has the open date, the other the closed date. Both have a matching id. So far so good. Where I'm struggling is that I need to do a monthly breakdown that needs to be dynamic and increase each month to show the number of records closed in a given month number for a given month. I'm having difficulty even explaining it let alone coding it!
Example:
10 cases are opened in January
12 in Feb
3 in March
Of the 10 opened in January, 2 are closed in January (Month 1), 3 are closed in February (Month 2) and 5 are closed in March (Month 3)
Of the 12 opened in February, 7 are closed in February (Month 1), 1 in March (Month 2)
Of the 3 opened in March, 1 is closed in March (Month 1)
This would therefore be reflected something like:
Each month of the report another month is added to row and column.
Just to make things simple the figure actually needs to be percentage rather than number, but baby steps!
Thanks for any pointers....
It's pretty simple. Calculate your stuff vertically, as normal. Then do a "Dynamic Crosstab". Please see the following article for details.
http://www.sqlservercentral.com/articles/Crosstab/65048/