Avatar of britpopfan74
britpopfan74
Flag 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'
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
britpopfan74

8/22/2022 - Mon
britpopfan74

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
Sharath S

Can you just post some raw data from your table and the expected result?
britpopfan74

ASKER
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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
britpopfan74

ASKER
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
Sharath S

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
britpopfan74

ASKER
Thank you