Pivot data from monthly data

I have the following code and need help with the following:

1.  This is supposed to pull records from the previous month and from the month before that.  For example, from April and March.  Instead of specifying month 4 and 3, I need to make sure that it is calling out the previous month and the month before that.  Can someone help modify my code here to do that?

2.  Where I have the function (SUM(MDM_Error), I need to incude the following (NPI_Counts) from the same table and have the (SUM(MDM_Error)) divided by the (NPI_Counts), can someone help with that as well?
SELECT PMG_BG, [PMG_BG_Level0]
      ,[PMG_BG_Level1]
      ,[PMG_BG_Level2]
      ,[PMG_BG_Level3]
, [3] AS Month3
, [4] AS Month4
FROM (
SELECT     PMG_BG,[PMG_BG_Level0]
      ,[PMG_BG_Level1]
      ,[PMG_BG_Level2]
      ,[PMG_BG_Level3],(R100_Error_E + R100_Error_W + R100_Error_A + R101_Error_E + R101_Error_W + R101_Error_A + R102_Error_E + R102_Error_W + R102_Error_A + R300_Error_E + R300_Error_W + R300_Error_A + R301_Error_E + R301_Error_W + R301_Error_A + R302_Error_E + R302_Error_W + R302_Error_A + R400_Error_E + R400_Error_W + R400_Error_A + R401_Error_E + R401_Error_W + R401_Error_A + R302_Error_E + R402_Error_W + R402_Error_A)  AS MDM_Error, Month
FROM         dbo.Rxxx_MonthTrendRpt
 
WHERE     (Month = MONTH(GETDATE()) - 1) OR (Month = MONTH(GETDATE()) - 2)
) t 
PIVOT (SUM(MDM_Error) FOR Month IN ([4],[3])) pvt

Open in new window

FairfieldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Something like this:
SELECT PMG_BG, [PMG_BG_Level0]
      ,[PMG_BG_Level1]
      ,[PMG_BG_Level2]
      ,[PMG_BG_Level3]
, [1] AS OneMonthAgo
, [2] AS TwoMonthsAgo
FROM (
	SELECT     PMG_BG,[PMG_BG_Level0]
		  ,[PMG_BG_Level1]
		  ,[PMG_BG_Level2]
		  ,[PMG_BG_Level3],(R100_Error_E + R100_Error_W + R100_Error_A + R101_Error_E + R101_Error_W + R101_Error_A + R102_Error_E + R102_Error_W + R102_Error_A + R300_Error_E + R300_Error_W + R300_Error_A + R301_Error_E + R301_Error_W + R301_Error_A + R302_Error_E + R302_Error_W + R302_Error_A + R400_Error_E + R400_Error_W + R400_Error_A + R401_Error_E + R401_Error_W + R401_Error_A + R302_Error_E + R402_Error_W + R402_Error_A)  AS MDM_Error
	, MONTH(GETDATE()) - Month AS MonthsAgo
	FROM         dbo.Rxxx_MonthTrendRpt
	WHERE     (Month = MONTH(GETDATE()) - 1) OR (Month = MONTH(GETDATE()) - 2)
) t 
PIVOT (SUM(MDM_Error) FOR Month IN ([1],[2])) pvt

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
Although, you will have difficulty going from January back to December and November.  But you have this issue regardless as you will need the Year also in data and use date functions to truly get last two calendar months.
0
Mark WillsTopic AdvisorCommented:
Well,

As far as dividing NPI_Counts you will not be able to achieve that easily at the aggregation level. It will need to happen before then as part of your selection, but I will check on a few things...

There are a couple of worthwhile Articles about this :
http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Dynamic-Pivot-Procedure-for-SQL-Server.html
http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Pivot-Reporting-Tips-for-SQL-Server-Handling-Rolling-Periods-Without-Using-Dynamic-SQL.html


0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Kevin CrossChief Technology OfficerCommented:
Glad you joined us Mark, as I overlooked the second question.
0
FairfieldAuthor Commented:
I am getting an invalid column name "Month" from your code
0
Mark WillsTopic AdvisorCommented:
*laughing* here was no one here when I first got the e-mail !  I am just glad I included your article as well !!
0
Mark WillsTopic AdvisorCommented:
Ooppss... That last message was for mwvisa1 - was not laughing at you Fairfield...
0
SharathData EngineerCommented:
Is it possible to provide some sample data and expected result?
0
Kevin CrossChief Technology OfficerCommented:

SELECT PMG_BG, [PMG_BG_Level0]
      ,[PMG_BG_Level1]
      ,[PMG_BG_Level2]
      ,[PMG_BG_Level3]
, [1] AS OneMonthAgo
, [2] AS TwoMonthsAgo
FROM (
        SELECT     PMG_BG,[PMG_BG_Level0]
                  ,[PMG_BG_Level1]
                  ,[PMG_BG_Level2]
                  ,[PMG_BG_Level3],(R100_Error_E + R100_Error_W + R100_Error_A + R101_Error_E + R101_Error_W + R101_Error_A + R102_Error_E + R102_Error_W + R102_Error_A + R300_Error_E + R300_Error_W + R300_Error_A + R301_Error_E + R301_Error_W + R301_Error_A + R302_Error_E + R302_Error_W + R302_Error_A + R400_Error_E + R400_Error_W + R400_Error_A + R401_Error_E + R401_Error_W + R401_Error_A + R302_Error_E + R402_Error_W + R402_Error_A)  AS MDM_Error
        , MONTH(GETDATE()) - Month AS MonthsAgo
        FROM         dbo.Rxxx_MonthTrendRpt
        WHERE     (Month = MONTH(GETDATE()) - 1) OR (Month = MONTH(GETDATE()) - 2)
) t 
PIVOT (SUM(MDM_Error) FOR MonthsAgo IN ([1],[2])) pvt

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Mark, I had referenced your article and mine in the related question. :) So I found it funny myself when you joined the conversation...figured you keyboard was tingling. LOL
0
FairfieldAuthor Commented:
If I had another column MDM_Error_C and wanted to include that as well in the result, how would I include that in the pivot table as another column?  I tried to use (SUM(MDM_Error_P, MDM_Error_C) but that didn't work.
SELECT     PMG_BG, [PMG_BG_Level0], [PMG_BG_Level1], [PMG_BG_Level2], [PMG_BG_Level3], [1] AS OneMonthAgo, [2] AS TwoMonthsAgo
FROM         (SELECT     PMG_BG, [PMG_BG_Level0], [PMG_BG_Level1], [PMG_BG_Level2], [PMG_BG_Level3], MDM_Error_P, MONTH(GETDATE()) 
                                              - Month AS MonthsAgo
                       FROM          dbo.Rxxx_MonthTrendRpt_Percent
                       WHERE      (Month = MONTH(GETDATE()) - 1) OR
                                              (Month = MONTH(GETDATE()) - 2)) t PIVOT (SUM(MDM_Error_P) FOR MonthsAgo IN ([1], [2])) pvt

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Try it like this:
SELECT PMG_BG, [PMG_BG_Level0], [PMG_BG_Level1], [PMG_BG_Level2], [PMG_BG_Level3]
, P_OneMonthAgo, P_TwoMonthsAgo
, C_OneMonthAgo, C_TwoMonthsAgo
FROM (
	SELECT PMG_BG
	, [PMG_BG_Level0]
	, [PMG_BG_Level1]
	, [PMG_BG_Level2]
	, [PMG_BG_Level3]
	, MDM_Error_P
	, CASE MONTH(GETDATE()) - Month WHEN 1 THEN 'P_OneMonthAgo' WHEN 2 THEN 'P_TwoMonthsAgo' END AS MonthsAgo_P
	, MDM_Error_C
	, CASE MONTH(GETDATE()) - Month WHEN 1 THEN 'C_OneMonthAgo' WHEN 2 THEN 'C_TwoMonthsAgo' END AS MonthsAgo_C
    FROM dbo.Rxxx_MonthTrendRpt_Percent
	WHERE (Month = MONTH(GETDATE()) - 1) OR (Month = MONTH(GETDATE()) - 2)
) t 
PIVOT (SUM(MDM_Error_P) FOR MonthsAgo_P IN ([P_OneMonthAgo], [P_TwoMonthsAgo])) p
PIVOT (SUM(MDM_Error_C) FOR MonthsAgo_C IN ([C_OneMonthAgo], [C_TwoMonthsAgo])) c

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Sorry, forgot with the two PIVOT's you have to do the group by.
SELECT PMG_BG, [PMG_BG_Level0], [PMG_BG_Level1], [PMG_BG_Level2], [PMG_BG_Level3]
, SUM(P_OneMonthAgo) AS P_OneMonthAgo
, SUM(P_TwoMonthsAgo) AS P_TwoMonthsAgo
, SUM(C_OneMonthAgo) AS C_OneMonthAgo
, SUM(C_TwoMonthsAgo) AS C_TwoMonthsAgo
FROM (
        SELECT PMG_BG
        , [PMG_BG_Level0]
        , [PMG_BG_Level1]
        , [PMG_BG_Level2]
        , [PMG_BG_Level3]
        , MDM_Error_P
        , CASE MONTH(GETDATE()) - Month WHEN 1 THEN 'P_OneMonthAgo' WHEN 2 THEN 'P_TwoMonthsAgo' END AS MonthsAgo_P
        , MDM_Error_C
        , CASE MONTH(GETDATE()) - Month WHEN 1 THEN 'C_OneMonthAgo' WHEN 2 THEN 'C_TwoMonthsAgo' END AS MonthsAgo_C
    FROM dbo.Rxxx_MonthTrendRpt_Percent
        WHERE (Month = MONTH(GETDATE()) - 1) OR (Month = MONTH(GETDATE()) - 2)
) t 
PIVOT (SUM(MDM_Error_P) FOR MonthsAgo_P IN ([P_OneMonthAgo], [P_TwoMonthsAgo])) p
PIVOT (SUM(MDM_Error_C) FOR MonthsAgo_C IN ([C_OneMonthAgo], [C_TwoMonthsAgo])) c
GROUP BY PMG_BG, [PMG_BG_Level0], [PMG_BG_Level1], [PMG_BG_Level2], [PMG_BG_Level3]

Open in new window

0
Mark WillsTopic AdvisorCommented:
Well, looks like my job is done here :) Good work mwvisa1.
0
Kevin CrossChief Technology OfficerCommented:
@mark_wills, Thanks!
@Fairfield, that working out for you?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.