I could use some advice on this report -- will explain what it is and what I have done.
Need to break out for the annual period for two different regions in one chart the monthly breakdown by utilization by total days of week/month.
So, it is looking at for April 2012 forward, how many total were seen all Sundays, Mondays, Tuesday...Saturdays (all business days) each month.
YEARLY MONTHLY WEEKDAY Gpa Count GpB Count
2012 APR Mon 2 69
2012 APR Tue 6 38
2012 APR Wed 2 23
2012 APR Thu 8 29
2012 APR Sat 8 36
There may be a month where there is no count for a given day of the week. For example, let's say Group A had no visits on any Tuesdays in May; however, Group B still had visits during that comparable time period.
I start by querying for all claims in the regions and ordering by DOS...then I get to the point where I run into where I would like to do more dynamic coding than hard-coding.
The below gets me what I need for claims per month for 1 region; however, could someone advise how to add to the pivot the next "layer" being the days of the week?
Also - when I do this for both groups, I eventually need to join these so that I can "line up" the data for exporting to do a graph in Excel...for now let me concentrate on this and upload any data needed though.
Many thanks in advance!
SELECT 'claim count' AS claim_by_month,
[Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar] --rolling yr
LEFT(DATENAME(MONTH, first_dos), 3) AS Mth,
FROM #temp_CLAIMS_MC_EM) AS SourceTable
FOR Mth IN ([Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar])
) AS PivotTable
where GROUPINGS = 'gpA'