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.
Best Answer
This seemed like a relational division problem to me:
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 whereSum of PASSED >= COUNT ( DISTINCT LEVEL )
. Pre-aggregating the data would solve this, egIf the date really is important, please post back and we'll need a slightly more complicated query, basically the
PASSED
status associated with themax date per ID
.HTH