Adding a "calculated" field to a pivot view

Hello,
After getting a good nights sleep, I've refined the question.  Basically I want to set GFP to the status that has the lowest weight which in the above example is "Pending" with a weight of 1.  I think the best way to do this is to create a udf that returns this status and to place this udf in the select clause as in SELECT  udf_ReturnLowestStatus AS GFP From PivotTable.  I'm thinking this might be the best way to go.  I'd like to get some opinions.

CREATE udf_ReturnLowestStatus
@Status1 as varchar(500),
@Status2 as varchar(500),
@Status3 as varchar(500),
@Status4 as varchar(500),
@Status5 as varchar(500),
@Status6 as varchar(500),
@Status7 as varchar(500),
@Status8 as varchar(500),
@MS1 as int,
@MS2 as int,
@MS3 as int,
@MS4 as int,
@MS5 as int,
@MS6 as int,
@MS7 as int,
@MS8 as int
AS

--Return the status

Here is the code for the calling procedure

SELECT p3.USI,  Max(WS.Workstream) AS WorkStream,

udf_ReturnLowestStatus(Status1, Status2, Status3, Status4, Status5, Status6, Status7, Status8,
MappedStatus1, MappedStatus2, MappedStatus3, MappedStatus4, MappedStatus5, MappedStatus6, MappedStatus7, MappedStatus8) AS [GFP],

MAX([Review1]) AS [Review1],  MAX([Status1]) AS [Status1],  MAX([MappedStatus1]) AS [MappedStatus1],
MAX([Review2]) AS [Review2],  MAX([Status2]) AS [Status2],  MAX([MappedStatus2]) AS [MappedStatus2],
MAX([Review3]) AS [Review3],  MAX([Status3]) AS [Status3],  MAX([MappedStatus3]) AS [MappedStatus3],
MAX([Review4]) AS [Review4],  MAX([Status4]) AS [Status4],  MAX([MappedStatus4]) AS [MappedStatus4],
MAX([Review5]) AS [Review5],  MAX([Status5]) AS [Status5],  MAX([MappedStatus5]) AS [MappedStatus5],
MAX([Review6]) AS [Review6],  MAX([Status6]) AS [Status6],  MAX([MappedStatus6]) AS [MappedStatus6],
MAX([Review7]) AS [Review7],  MAX([Status7]) AS [Status7],  MAX([MappedStatus7]) AS [MappedStatus7],
MAX([Review8]) AS [Review8],  MAX([Status8]) AS [Status8],      MAX([MappedStatus8]) AS [MappedStatus8],
MAX(AnalystName) AS AnalystName, MAX(AnalystCompletionDate) AS AnalystCompletionDate, MAX([Senior Analyst]) AS 'Senior Analyst', MAX(SeniorCompletionDate) AS SeniorCompletionDate, MAX(QAAnalyst) AS QAAnalyst, MAX(QAConfirmationDate) AS QAConfirmationDate, SUM(ExceptionCount) AS ExceptionCount
FROM (
SELECT      USI, Review, ReviewStatus, MappedStatus, 'MappedStatus'+CONVERT(VARCHAR(2),rn) MappedStatusPvtCol,'Review'+CONVERT(VARCHAR(2),rn) ReviewPvtCol, 'Status'+CONVERT(VARCHAR(2),rn) StatusPvtCol,
            AnalystName, AnalystCompletionDate, [Senior Analyst], SeniorCompletionDate, QAAnalyst, QAConfirmationDate, ExceptionCount
FROM      (SELECT USI, Review, ReviewStatus, MappedStatus,
            AnalystName, AnalystCompletionDate, [Senior Analyst], SeniorCompletionDate, QAAnalyst, QAConfirmationDate, ExceptionCount,
            ROW_NUMBER() OVER (PARTITION BY USI ORDER BY Review) rn FROM  dbo.vwViewAllForPivot) AS SourceTable
) p
PIVOT
(
max(Review)
FOR ReviewPvtCol IN ([Review1], [Review2], [Review3], [Review4], [Review5], [Review6], [Review7], [Review8])
) AS P1
PIVOT
(
max(ReviewStatus)
FOR StatusPvtCol IN ([Status1], [Status2], [Status3], [Status4], [Status5], [Status6], [Status7], [Status8])
) AS P2
PIVOT
(
max(MappedStatus)
FOR MappedStatusPvtCol IN ([MappedStatus1], [MappedStatus2], [MappedStatus3], [MappedStatus4], [MappedStatus5], [MappedStatus6], [MappedStatus7], [MappedStatus8])
) AS P3
INNER JOIN dbo.vwWorkstream WS
ON P3.USI = WS.USI
GROUP BY P3.USI
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Hello,

I came up with a solution.  It works but it's a little brute force.  I'm open to any suggestions

ALTER VIEW [dbo].[vwPivotedReviewsRevised]
AS
SELECT p3.USI,  Max(WS.Workstream) AS WorkStream,
CASE
      WHEN MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus4]),9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus5]), 9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status1])
      WHEN MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus4]),9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus5]), 9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status2])
      WHEN MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus4]),9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus5]), 9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status3])
      WHEN MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus5]), 9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status4])      
      WHEN MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus4]), 9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status5])            
      WHEN MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus4]), 9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus5]),9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status6])            
      WHEN MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus4]), 9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus5]),9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status7])            
      WHEN MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus4]), 9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus5]),9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus7]), 9999) THEN MAX([Status8])
END AS [GFP],
--CASE
--      
--      WHEN MAX(Review8) IS NOT NULL THEN MAX(Status8)
--      WHEN MAX(Review7) IS NOT NULL THEN MAX(Status7)
--      WHEN MAX(Review6) IS NOT NULL THEN MAX(Status6)
--      WHEN MAX(Review5) IS NOT NULL THEN MAX(Status5)
--      WHEN MAX(Review4) IS NOT NULL THEN MAX(Status4)
--      WHEN MAX(Review3) IS NOT NULL THEN MAX(Status3)
--      WHEN MAX(Review2) IS NOT NULL THEN MAX(Status2)
--      WHEN MAX(Review1) IS NOT NULL THEN MAX(Status1)            
--END As [GFP],
MAX([Review1]) AS [Review1],  MAX([Status1]) AS [Status1],  MAX([MappedStatus1]) AS [MappedStatus1],
MAX([Review2]) AS [Review2],  MAX([Status2]) AS [Status2],  MAX([MappedStatus2]) AS [MappedStatus2],
MAX([Review3]) AS [Review3],  MAX([Status3]) AS [Status3],  MAX([MappedStatus3]) AS [MappedStatus3],
MAX([Review4]) AS [Review4],  MAX([Status4]) AS [Status4],  MAX([MappedStatus4]) AS [MappedStatus4],
MAX([Review5]) AS [Review5],  MAX([Status5]) AS [Status5],  MAX([MappedStatus5]) AS [MappedStatus5],
MAX([Review6]) AS [Review6],  MAX([Status6]) AS [Status6],  MAX([MappedStatus6]) AS [MappedStatus6],
MAX([Review7]) AS [Review7],  MAX([Status7]) AS [Status7],  MAX([MappedStatus7]) AS [MappedStatus7],
MAX([Review8]) AS [Review8],  MAX([Status8]) AS [Status8],      MAX([MappedStatus8]) AS [MappedStatus8],
MAX(AnalystName) AS AnalystName, MAX(AnalystCompletionDate) AS AnalystCompletionDate, MAX([Senior Analyst]) AS 'Senior Analyst', MAX(SeniorCompletionDate) AS SeniorCompletionDate, MAX(QAAnalyst) AS QAAnalyst, MAX(QAConfirmationDate) AS QAConfirmationDate, SUM(ExceptionCount) AS ExceptionCount
FROM (
SELECT      USI, Review, ReviewStatus, MappedStatus, 'MappedStatus'+CONVERT(VARCHAR(2),rn) MappedStatusPvtCol,'Review'+CONVERT(VARCHAR(2),rn) ReviewPvtCol, 'Status'+CONVERT(VARCHAR(2),rn) StatusPvtCol,
            AnalystName, AnalystCompletionDate, [Senior Analyst], SeniorCompletionDate, QAAnalyst, QAConfirmationDate, ExceptionCount
FROM      (SELECT USI, Review, ReviewStatus, MappedStatus,
            AnalystName, AnalystCompletionDate, [Senior Analyst], SeniorCompletionDate, QAAnalyst, QAConfirmationDate, ExceptionCount,
            ROW_NUMBER() OVER (PARTITION BY USI ORDER BY Review) rn FROM  dbo.vwViewAllForPivot) AS SourceTable
) p
PIVOT
(
max(Review)
FOR ReviewPvtCol IN ([Review1], [Review2], [Review3], [Review4], [Review5], [Review6], [Review7], [Review8])
) AS P1
PIVOT
(
max(ReviewStatus)
FOR StatusPvtCol IN ([Status1], [Status2], [Status3], [Status4], [Status5], [Status6], [Status7], [Status8])
) AS P2
PIVOT
(
max(MappedStatus)
FOR MappedStatusPvtCol IN ([MappedStatus1], [MappedStatus2], [MappedStatus3], [MappedStatus4], [MappedStatus5], [MappedStatus6], [MappedStatus7], [MappedStatus8])
) AS P3
INNER JOIN dbo.vwWorkstream WS
ON P3.USI = WS.USI
GROUP BY P3.USI

Have you got some sample data in say an Excel Spreadsheet of vwViewAllForPivot and vwWorkStream ?

Dont want any live or sensitive information, just something representative.

I am pretty sure I can help you, but being involved, would like to run up a test and see the best way...

The challenge as I understand it is that there can be multiple weights and we want the "least" real weight to show. I am not so sure I would do that big case statement, rather, try to resolve at a lower level so we can simply get the MIN(mappedstatus) as GFP.

But, might also need a little more explanation about those weights (e.g. 1 = pending where 1 is from mappedstatus* etc). And understand the need to manage the possibility of NULLS because there might not be a mappedstatus for a section.

So, if you are able to post some sample data we can get something happening quicker. If you cannot, then we can still get something happening, might just take a little longer to do so.

Sure thing mark.  I'll do it tomorrow

Hello Mark,

Attached is a excel file with two tabs, one for the ViewAllForPivot data and the other containing the workstream data
SampleViewForPivot.xlsx

Thanks,

Looks like I only have the 1 review for each USI... Would it be possible to add a couple more in ?

If it is OK for me to simply copy and paste rows and make the copied rows a little different, then thats fine too.

By the looks I would only have to add a few rows into the first worksheet and not upset the fabric of space time continuim in the second 'workstream'  worksheet...

It's okay to add more rows as long as the USI remains unique

Hmmm... Was going to repeat USI otherwise how do we get multiple reviews for each USI ? I realise that they must be distinct (ie only use the ones already there).

Will have a play anyway. But it is nearly time to take the kids to Soccer, so wont be back for a couple of hours. Oh, the pressures of being "coach" :)

I'm sorry,

Actually there are multiple usi's in the viewforallpivot.

OK,

Couple of little challenges, like no MappedStatus in the spreadsheet. But assume it is a simple lookup, similar with statusID and the Status Name - can you please confirm ?

Also, the MAX() aggregations dont necessarily make sense in context. For example, the max([senior Analyst]) doesnt necessarily mean it corresponds to the max(seniorcompletiondate) and similar for Analyst and QA.

I have written code for the above scenarios, and also matching what you have (well, nearly, using statusweight in place of mapped status for the moment).

Anyway, if you could please clarify... And in the meantime, have a look at (and you need to replace # with dbo. for the table names)

It also introduces some new concepts like window functions for aggregations. And that is the real reason for the code as it is to demonstrate to you some new techniques (the code itself is not quite 100% in the example below).

;with Reviews as
(SELECT P.USI, WS.Workstream, P.Review, P.ReviewStatus, P.StatusID, convert(varchar(20),P.StatusWeight) as MappedStatus,
        convert(varchar(2),ROW_NUMBER() OVER (PARTITION BY P.USI ORDER BY P.Review)) rn,
        min(P.statusID) OVER (partition by P.USI) GFP,
        max(isnull(P.AnalystName,'')) over (partition by P.USI) AnalystName, 
        max(isnull(P.AnalystCompletionDate,'')) over (partition by P.USI) AnalystCompletionDate, 
        max(isnull(P.[Senior Analyst],'')) over (partition by P.USI) SeniorAnalyst, 
        max(isnull(P.SeniorCompletionDate,'')) over (partition by P.USI) SeniorCompletionDate, 
        max(isnull(P.QAAnalyst,'')) over (partition by P.USI) QAAnalyst,
        max(isnull(P.QAConfirmationDate,'')) over (partition by P.USI) QAConfirmationDate,
        sum(isnull(P.ExceptionCount,0)) over (partition by P.USI) ExceptionCount
 FROM  #vwViewAllForPivot P
 INNER JOIN #vwWorkstream WS on P.USI = WS.USI )

SELECT *
from  (   
       Select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'Review'+rn PvtCol, Review as PvtVal
       FROM   Reviews
       UNION 
       Select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'Status'+rn PvtCol, ReviewStatus as PvtVal
       FROM   Reviews
       UNION
       select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'MappedStatus'+rn PvtCol, MappedStatus PvtVal
       FROM   Reviews
      ) src
pivot

( max(pvtval) for PvtCol in ([review1],[status1],[mappedstatus1]
                            ,[review2],[status2],[mappedstatus2]
                            ,[review3],[status3],[mappedstatus3]
                            ,[review4],[status4],[mappedstatus4]
                            ,[review5],[status5],[mappedstatus5]
                            ,[review6],[status6],[mappedstatus6]
                            ,[review7],[status7],[mappedstatus7]
                            ,[review8],[status8],[mappedstatus8])) pvt

Select allOpen in new window

MappedStatus is now StatusWeight.  They are both the same.  I thought that statusweight was a more accurate description

Hello Mark,

I will give the code a test tomorrow.  I'll go ahead and read up on windows functions for aggregations.  I am in a Sql Server 2005 environment.  Will that make a difference.


Juan

It will work fine in SQL2005. They are even better / more powerful in the more recent releases (like SQL2012).

You arent concerned about the MAX() ? When looking at it again, I realised that maybe those columns are only populated (once) at the appropriate time (or status), so, if there is only a value and not multiple values for a given USI in those columns, then MAX() will work fine.

A lot of your query performance will be very dependant on indexes. I see that it will be a view on top of another view.

Need to be a little bit careful when doing that so the underlying table indexes are still available as obvious choices for the optimizer. Sometimes views have complex joins or selection criteria so it can result in the optimizer choosing to do a scan rather than using an index.

I will play a bit with different queries to see their relative performance.

I ran a test using a select statment against each view.  My original view took 10 second while the one above took about 30 seconds

Here is the test script
Create View vwTEST
AS

with Reviews as
(SELECT P.USI, WS.Workstream, P.Review, P.ReviewStatus, P.StatusID, convert(varchar(20),P.StatusWeight) as MappedStatus,
        convert(varchar(2),ROW_NUMBER() OVER (PARTITION BY P.USI ORDER BY P.Review)) rn,
        min(P.statusID) OVER (partition by P.USI) GFP,
        max(isnull(P.AnalystName,'')) over (partition by P.USI) AnalystName,
        max(isnull(P.AnalystCompletionDate,'')) over (partition by P.USI) AnalystCompletionDate,
        max(isnull(P.[Senior Analyst],'')) over (partition by P.USI) SeniorAnalyst,
        max(isnull(P.SeniorCompletionDate,'')) over (partition by P.USI) SeniorCompletionDate,
        max(isnull(P.QAAnalyst,'')) over (partition by P.USI) QAAnalyst,
        max(isnull(P.QAConfirmationDate,'')) over (partition by P.USI) QAConfirmationDate,
        sum(isnull(P.ExceptionCount,0)) over (partition by P.USI) ExceptionCount
 FROM  dbo.vwViewAllForPivot P
 INNER JOIN dbo.vwWorkstream WS on P.USI = WS.USI )

SELECT *
from  (  
       Select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'Review'+rn PvtCol, Review as PvtVal
       FROM   Reviews
       UNION
       Select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'Status'+rn PvtCol, ReviewStatus as PvtVal
       FROM   Reviews
       UNION
       select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'MappedStatus'+rn PvtCol, MappedStatus PvtVal
       FROM   Reviews
      ) src
pivot

( max(pvtval) for PvtCol in ([review1],[status1],[mappedstatus1]
                            ,[review2],[status2],[mappedstatus2]
                            ,[review3],[status3],[mappedstatus3]
                            ,[review4],[status4],[mappedstatus4]
                            ,[review5],[status5],[mappedstatus5]
                            ,[review6],[status6],[mappedstatus6]
                            ,[review7],[status7],[mappedstatus7]
                            ,[review8],[status8],[mappedstatus8])) pvt

I've requested that this question be closed as follows:

Accepted answer: 0 points for chtullu135's comment #37853376

for the following reason:

Although I chose to use the my original solution, I appreciate the time and effort you took. &nbsp;In addition, it gave other ways of looking at pivots

I want to cancel my request.  I meant to award points

Thank you for your time and effort.  Although I will be using my original solution, I found the information you presented very interesting

Well, thankyou sir, much appreciated.

And if you do want to delve deeper, you can check out contact details on my bio...