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'

Microsoft SQL Server 2005

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

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

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

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
Thank you

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