troubleshooting Question

Help adding another dimension to SQL Pivot

Avatar of britpopfan74
britpopfan74Flag for United States of America asked on
Microsoft SQL Server 2005
6 Comments1 Solution256 ViewsLast Modified:
Dear Experts,

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.

Ex:
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
FROM
(SELECT Distinct
      LEFT(DATENAME(MONTH, first_dos), 3) AS Mth,
      DOCUMENT,
      GROUPINGS
    FROM #temp_CLAIMS_MC_EM) AS SourceTable
PIVOT (
      COUNT(DOCUMENT)
FOR Mth IN ([Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar])
      ) AS PivotTable
where GROUPINGS = 'gpA'
ASKER CERTIFIED SOLUTION
Sharath S
Data Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros