How to use CTE to Compare Values

Question

I need to return a 0 or 1 dependent on previous records. Example table:

DECLARE @x TABLE(ProductID INT, Failed bit, SampleDate date, LevelCode int);
INSERT @x VALUES
(101, 0, '20151201', 1),    
(101, 1, '20151205', 2),    
(101, 0, '20151206', 3),
(101, 1, '20151208', 2), 
(102, 1, '20151202', 1),
(102, 0, '20151204', 2),    
(102, 0, '20151205', 3),
(103, 0, '20160101', 1),
(103, 1, '20160102', 2),
(103, 0, '20160103', 2),
(104, 0, '20160101', 1),
(104, 0, '20160102', 2),
(104, 0, '20160103', 3);

The only thing we care about on the last record is the LevelCode (i.e. last record of each ProductID). Whether the last record passed/failed doesnt matter. We then look at all other records for that ProductID (so all records before the last record) and if there was a failure with the same LevelCode as the last record we set IsLastRunSameLevelAsPreviousRun to 1 else 0:

 ProductID     IsLastRunSameLevelAsPreviousRun
 101           1
 102           0
 103           1
 104           0

If there are no failures for a ProductID the IsLastRunSameLevelAsPreviousRun should return 0.

Any help or tips are very much appreciate.

Answer 1

Using LAG you can achieve your goal without any joins:

WITH cte AS
  (
    SELECT
      ProductID,
      SampleDate,
      MaxSampleDate = MAX(SampleDate) OVER (PARTITION BY ProductID),
      PrevFailed    = LAG(Failed, 1, 0) OVER (PARTITION BY ProductID, LevelCode
                                              ORDER BY SampleDate)
    FROM
      @x
  )
SELECT
  ProductID,
  IsLastRunSameLevelAsPreviousRun = PrevFailed
FROM
  cte
WHERE
  SampleDate = MaxSampleDate
;

The cte obtains the previous Failed state for the same LevelCode for each row within each ProductID group, returning 0 when there is no matching row. It also calculates the last date in each group to use it later to determine the last row in the group.

This is what it returns:

ProductID  SampleDate  MaxSampleDate  PrevFailed
---------  ----------  -------------  ----------
101        2015-12-01  2015-12-08     0
101        2015-12-05  2015-12-08     0
101        2015-12-08  2015-12-08     1
101        2015-12-06  2015-12-08     0
102        2015-12-02  2015-12-05     0
102        2015-12-04  2015-12-05     0
102        2015-12-05  2015-12-05     0
103        2016-01-01  2016-01-03     0
103        2016-01-02  2016-01-03     0
103        2016-01-03  2016-01-03     1
104        2016-01-01  2016-01-03     0
104        2016-01-02  2016-01-03     0
104        2016-01-03  2016-01-03     0

The main SELECT essentially just takes the last row of each group using the SampleDate = MaxSampleDate filter, pulling only ProductID and PrevFailed and also renaming the latter to IsLastRunSameLevelAsPreviousRun, so that the final output becomes what you want:

ProductID  IsLastRunSameLevelAsPreviousRun
---------  -------------------------------
101        1
102        0
103        1
104        0

Answer 2

One way to do it is to use ROW_NUMBER twice.

First number all rows of the table partitioned by the ProductID (CTE_RowNumbersAll) and get only the last row for each ProductID (CTE_LastAll).

Then number all failed rows (CTE_RowNumbersFailed) and get the last failed row for each ProductID (CTE_LastFailed).

Finally LEFT JOIN intermediary results together and use CASE to compare the LevelCode values.

Update

Based on you last comment I changed the query. CTE_RowNumbersFailed is now based on CTE_RowNumbersAll and there is an extra filter rnAll > 1 to remove the last row from consideration.

WITH
CTE_RowNumbersAll
AS
(
    SELECT
        ProductID
        ,Failed
        ,SampleDate
        ,LevelCode
        ,ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SampleDate DESC) AS rnAll
    FROM @x
)
,CTE_LastAll
AS
(
    SELECT
        ProductID
        ,LevelCode
    FROM CTE_RowNumbersAll
    WHERE rnAll = 1
)
,CTE_RowNumbersFailed
AS
(
    SELECT
        ProductID
        ,Failed
        ,SampleDate
        ,LevelCode
        ,ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SampleDate DESC) AS rnFailed
    FROM CTE_RowNumbersAll
    WHERE
        Failed = 1
        AND rnAll > 1
)
,CTE_LastFailed
AS
(
    SELECT
        ProductID
        ,LevelCode
    FROM CTE_RowNumbersFailed
    WHERE rnFailed = 1
)
SELECT
    CTE_LastAll.ProductID
    ,CASE WHEN CTE_LastAll.LevelCode = CTE_LastFailed.LevelCode 
        THEN 1 ELSE 0 END AS IsLastRunSameLevelAsPreviousRun
FROM
    CTE_LastAll
    LEFT JOIN CTE_LastFailed ON CTE_LastFailed.ProductID = CTE_LastAll.ProductID
ORDER BY CTE_LastAll.ProductID;

Result

+-----------+---------------------------------+
| ProductID | IsLastRunSameLevelAsPreviousRun |
+-----------+---------------------------------+
|       101 |                               1 |
|       102 |                               0 |
|       103 |                               1 |
|       104 |                               0 |
+-----------+---------------------------------+

Answer 3

Hope this helps

DECLARE @x TABLE(ProductID INT, Failed bit, SampleDate date, LevelCode int);

INSERT @x 
select * 
from
(VALUES (101, 0, '20151201', 1),
(101, 1, '20151205', 2),
(101, 0, '20151206', 3), (101, 1, '20151208', 2), (102, 1, '20151202', 1), (102, 0, '20151204', 2),
(102, 0, '20151205', 3), (103, 0, '20160101', 1), (103, 1, '20160102', 2), (103, 0, '20160103', 2), (104, 0, '20160101', 1), (104, 0, '20160102', 2), (104, 0, '20160103', 3)
) V(ProductID, Failed, SampleDate, LevelCode);


with T as (

select ProductID, 
RANK() OVER (PARTITION BY ProductID ORDER BY SampleDate desc) AS [Rank],
levelcode
from @x
where Failed = 1
)

select  FT.ProductID, FT.LevelCode,
    case
        when FT.LevelCode = ST.LevelCode then 1
        else 0
    END as IsLastRunSameLevelAsPreviousRun
FROM T AS FT
inner join T as ST on FT.ProductID = ST.ProductID and FT.[Rank]+1 = ST.[Rank]
WHERE FT.[Rank] = 1