Link to home
Start Free TrialLog in
Avatar of britpopfan74
britpopfan74Flag for United States of America

asked on

Help adding another dimension to SQL Pivot

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'
Avatar of britpopfan74
britpopfan74
Flag of United States of America image

ASKER

Output from above-referenced pivot table:

claim_by_month      Apr      May      Jun      Jul      Aug      Sep      Oct      Nov      Dec      Jan      Feb      Mar
claim count      26      26      26      33      22      23      26      37      19      22      19      4

Now need to get breakdown by days of the week in each month

I know this is not exact need to add in:

SELECT 'claim count' AS claim_by_day,
 [Sun], [Mon], [Tue], [Wed], [Thu], [Fri], [Sat]
FROM
(SELECT Distinct DATENAME(dw,DATEPART(dw,FIRST_DOS)) as [weekday],
      DOCUMENT,
      GROUPINGS
    FROM #temp_CLAIMS_MC_EM) AS SourceTable
PIVOT (
      COUNT(DOCUMENT)
FOR [weekday] IN ([Sun], [Mon], [Tue], [Wed], [Thu], [Fri], [Sat])
      ) AS PivotTable
Avatar of Sharath S
Can you just post some raw data from your table and the expected result?
OK, let me start from the beginning how I originally ran this.

I first ran a script to get all claims for the time period 4/1/12 - 4/1/13:

SELECT DISTINCT
CASE WHEN MC.REGION = 'x' THEN 'gp A'
       WHEN MC.REGION = 'y' THEN 'gp B' END AS GROUPINGS
, MC.[DOCUMENT]
, MC.FIRST_DOS
INTO #temp_CLAIMS_MC_EM
FROM MASTER_CLAIM mc
INNER JOIN MASTER_CLAIM#DETAIL mcd ON mc.[DOCUMENT] = mcd.[DOCUMENT]
WHERE (MC.FIRST_DOS BETWEEN @START_DOS AND @END_DOS)
AND (mc.DOCUMENT IS NOT NULL)
AND (mc.VALID_CLAIM IS NULL) -- accounts for adjustment codes (R, D) --WANT DENIALS
AND (MC.REGION IN('x','y'))
AND (MCD.PL_CODE = '23') -- EMERG. RM.
ORDER BY MC.FIRST_DOS DESC

SELECT * FROM #temp_CLAIMS_MC_EM

This gives output like:

GROUPINGS      DOCUMENT      FIRST_DOS
Gp A            1            3/17/2013
Gp A            2            3/17/2013
Gp A            3            3/15/2013
Gp A            4            3/15/2013
Gp B            5            3/15/2013
Gp A            6            3/15/2013
Gp A            7            3/14/2013
Gp A            8            3/14/2013
Gp A            9            3/13/2013
Gp B            10            3/12/2013

So then I need to break out by days of week and month by group, which I was doing by this way below:


select
case DATEPART(dw, FIRST_DOS)
when 1 then 'Sun'
when 2 then 'Mon'
when 3 then 'Tue'
when 4 then 'Wed'
when 5 then 'Thu'
when 6 then 'Fri'
when 7 then 'Sat'
End as WEEKDAY
, CASE DATEPART(MONTH, FIRST_DOS)
when 1 then 'JAN'
when 2 then 'FEB'
when 3 then 'MAR'
when 4 then 'APR'
when 5 then 'MAY'
when 6 then 'JUN'
when 7 then 'JUL'
when 8 then 'AUG'
when 9 then 'SEP'
when 10 then 'OCT'
when 11 then 'NOV'
when 12 then 'DEC'
END AS MONTHLY
, GR = 'gp A'
, Count(DISTINCT [DOCUMENT]) AS DOC_COUNT
INTO #TEMP_EM_CHP_COUNTS
From #temp_CLAIMS_MC_EM
WHERE GROUPINGS = 'CHP'  
Group by Month(FIRST_DOS),  DatePart(dw, FIRST_DOS)
, DATEPART(YEAR, FIRST_DOS)  DATEPART(MONTH, FIRST_DOS)
ORDER BY DATEPART(YEAR, FIRST_DOS)

SELECT * FROM #TEMP_EM_CHP_COUNTS

Sample output:

WEEKDAY      MONTHLY      GR      DOC_COUNT
Mon      APR      gp A      2
Tue      APR      gp A      6
Wed      APR      gp A      2
Thu      APR      gp A      8
Sat      APR      gp A      8
Mon      MAY      gp A      3
Tue      MAY      gp A      6
Wed      MAY      gp A      3
Thu      MAY      gp A      7
Fri      MAY      gp A      5
Sat      MAY      gp A      2

--So, to simplify, I was trying to see if there was an easier way to do this with PIVOT combining months and days of week

--Then, figure out how to join effectively the output from gp A and gp B together so I could graph in Excel in a time series (I tried a LEFT OUTER JOIN but the results I got did not make sense with the outputs from gp A and B)

Please let me know what else you may need to clarify; thank you!
I found a way to work-around this with a CTE...it's not the pivot but it gets me close to where I wanted...

; with mycte as
(
     select cast(@START_DOS as datetime) DateValue
     union all
     select DateValue + 1
     from    mycte  
     where   DateValue + 1 <= @END_DOS
 )
 select M.DateValue
, CASE DATEPART(dw, FIRST_DOS)
when 1 then 'Sun'
when 2 then 'Mon'
when 3 then 'Tue'
when 4 then 'Wed'
when 5 then 'Thu'
when 6 then 'Fri'
when 7 then 'Sat'
End as WEEKDAY
, CASE DATEPART(MONTH, FIRST_DOS)
when 1 then 'JAN'
when 2 then 'FEB'
when 3 then 'MAR'
when 4 then 'APR'
when 5 then 'MAY'
when 6 then 'JUN'
when 7 then 'JUL'
when 8 then 'AUG'
when 9 then 'SEP'
when 10 then 'OCT'
when 11 then 'NOV'
when 12 then 'DEC'
END AS MONTHLY
, GROUPINGS
, COUNT(DOCUMENT) AS CLAIM_COUNT
INTO #U from    mycte M
LEFT OUTER JOIN #temp_CLAIMS_MC_EM EM ON M.DATEVALUE = EM.FIRST_DOS
WHERE GROUPINGS = 'U'
GROUP BY M.DateValue, GROUPINGS, FIRST_DOS
option (maxrecursion 1000)

SELECT * FROM #U

SELECT U.DATEVALUE
, U.WEEKDAY
, U.MONTHLY
, U.CLAIM_COUNT AS U_COUNT
, C.CLAIM_COUNT AS C_COUNT
FROM #U U
LEFT OUTER JOIN #C C ON U.DATEVALUE = C.DATEVALUE
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you