How to compare current record with previous records

Question

Is there a way to group by and compare current record to previous records when reading from a table. My table looks something like below.

ID    PASSED    LEVEL    CREATEDATE
100   1         2        2016-01-10 07:35:02.123
100   1         2        2016-01-10 07:05:33.135
100   0         2        2016-01-09 08:35:29.123
100   1         2        2016-01-08 09:35:30.123
100   1         1        2016-01-07 11:35:31.123
101   1         1        2016-01-10 08:45:12.123
102   1         1        2016-01-10 09:45:22.222
102   1         1        2016-01-09 08:15:29.123
103   0         1        2016-01-10 07:35:02.123
103   1         2        2016-01-09 06:35:29.023

Ideally, I need to return a BIT value for each ID in the table.

The condition of the BIT value is to return a 1 for each ID when PASSED is 1 and LEVELS are different by CREATEDATE DESC order.

So in my example table the BIT value for

• ID 100 would return 1 because PASSED is 1 on the first and fourth record (the level changes on the fourth record).
• ID 101 would return a BIT value of 0 because there is only 1 PASSED record with a value of 1.
• ID 102 would return a BIT value of 0 because the LEVELs are the same for both records even though PASSED was 1 on both records.
• ID 103 would return 0 because both records did not PASS even though LEVELs are different.

Hoping a query could be structured in such a way that the response would be like this:

ID    STATUS_PASSED
100   1
101   0
102   0
103   0

Thanks for any help provided.

Answer 1

This seemed like a relational division problem to me:

DECLARE @results TABLE ( ID INT NOT NULL, PASSED BIT NOT NULL, LEVEL INT NOT NULL, CREATEDATE DATE NOT NULL, PRIMARY KEY ( ID, LEVEL, PASSED, CREATEDATE ) )
DECLARE @levels TABLE ( LEVEL INT PRIMARY KEY )

INSERT INTO @levels
VALUES ( 1 ), ( 2 ) --, ( 3 )    
INSERT INTO @results
VALUES
    ( 100, 1, 2, '1 Oct 2016' ),
    ( 100, 0, 2, '1 Sep 2016' ),
    ( 100, 1, 2, '1 Aug 2016' ),
    ( 100, 1, 1, '1 Jul 2016' ),
    ( 101, 1, 1, '1 Oct 2016' ),
    ( 102, 1, 1, '1 Oct 2016' ),
    ( 102, 1, 1, '1 Sep 2016' ),
    ( 103, 0, 1, '1 Oct 2016' ),
    ( 103, 1, 2, '1 Sep 2016' )
    ,
    ( 200, 0, 1, '1 Sep 2016' ),        -- No pass at all
    ( 201, 1, 1, '1 Sep 2016' ),        -- 1 pass at level 1 only
    ( 202, 1, 1, '1 Sep 2016' ),        -- 1 pass at each level 
    ( 202, 1, 2, '1 Sep 2016' ),        -- 1 pass at each level 
    ( 203, 1, 2, '1 Sep 2016' ),        -- 1 pass at level 2 only 
    ( 204, 0, 1, '1 Sep 2016' ),        -- No pass at either level
    ( 204, 0, 2, '1 Sep 2016' )         -- No pass at either level

    SELECT ID, 
        CASE 
            WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
              AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
            THEN 1
            ELSE 0 
        END overallPass
    FROM @results
    GROUP BY ID

I have not included the CREATEDATE column in the calculation as it doesn't seem to add a lot. Also, does it really make sense for someone to pass a course at the same level twice (as per ID 100 in your sample data)? Does the order in which they pass the courses really matter? If so, then this could cause a bug in my code where Sum of PASSED >= COUNT ( DISTINCT LEVEL ). Pre-aggregating the data would solve this, eg

SELECT ID,
    CASE 
        WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
          AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
        THEN 1
        ELSE 0 
    END overallPass
FROM ( SELECT ID, LEVEL, MAX( CAST( PASSED AS INT ) ) PASSED FROM @results GROUP BY ID, LEVEL ) x
GROUP BY ID

If the date really is important, please post back and we'll need a slightly more complicated query, basically the PASSED status associated with the max date per ID.

HTH