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.
Best Answer
Using LAG you can achieve your goal without any joins:
The
cte
obtains the previousFailed
state for the sameLevelCode
for each row within eachProductID
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:
The main SELECT essentially just takes the last row of each group using the
SampleDate = MaxSampleDate
filter, pulling onlyProductID
andPrevFailed
and also renaming the latter toIsLastRunSameLevelAsPreviousRun
, so that the final output becomes what you want: