Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 6 down vote accepted

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
share|improve this answer

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 |
+-----------+---------------------------------+
share|improve this answer
    
@Vladmir this is very close, but if I change the last 3 records for ProductID 104 to this: (104, 0, '20160101', 1), (104, 0, '20160102', 2), (104, 1, '20160103', 3). Then the IsLastRunSameLevelAsPreviousRun is set to 1. Eventhough there wasnt a failure with the same LevelCode as the last run (i.e. the 2 previous records for ProductID 104 did not fail on LevelCode 3). – obautista Feb 16 at 4:36
    
Well, in that case the last row is (104, 1, '20160103', 3). The last row is a failure, so any LevelCode would match itself. In the question you say: "When the LevelCode on the most recent record (regardless of the Failed value)" So, my code does just that - looks up the last row regardless of the Failed value. If you need a different logic, please edit the question and add few more sample rows and a more detailed explanation of the required result. – Vladimir Baranov Feb 16 at 4:40
    
At the moment, if last row of a ProductID is a failure, then IsLastRunSameLevelAsPreviousRun will be set to 1, regardless of any previous rows. – Vladimir Baranov Feb 16 at 4:41
1  
Sorry for not being clear. The only thing we care about on the last record is the LevelCode. Whether the last record pass/failed doesnt matter. We then look at all other records (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. – obautista Feb 16 at 4:47
    
I updated the query based on your last comment. – Vladimir Baranov Feb 16 at 4:55

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
share|improve this answer
    
This only returns 1 record (ProductID = 101). Anyway it could return like noted in my original post? – obautista Feb 16 at 3:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.