britpopfan74

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'

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'

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

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 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!

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

; 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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thank you

ASKER

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,FI

DOCUMENT,

GROUPINGS

FROM #temp_CLAIMS_MC_EM) AS SourceTable

PIVOT (

COUNT(DOCUMENT)

FOR [weekday] IN ([Sun], [Mon], [Tue], [Wed], [Thu], [Fri], [Sat])

) AS PivotTable