Hi, i have the below table structure and i need help with the query
ID Disp Type State CreateDate
1 I5 Det OH 1/1/2018 08:42:00
1 I5 Det CO 1/1/2018 08:43:00
1 I5 Det NC 1/2/2018 05:16:00
1 I5 ReDet OH 1/3/2018 14:02:00
1 I5 ReDet CO 1/3/2018 14:03:00
1 I5 ReDet NC 1/3/2018 14:04:00
2 I5 Det FL 1/4/2018 16:07:00
2 I5 ReDet FL 1/4/2018 16:07:00
3 I5 Det FL 1/4/2018 10:05:02
3 I5 ReDet CA 1/4/2018 10:05:03
3 I5 ReDet FL 1/5/2018 09:17:00
3 I5 ReDet CA 1/6/2018 12:04:00
4 I5 Det PA 1/7/2018 10:27:00
4 I5 Det NE 1/8/2018 10:14:00
4 I5 Det PA 1/8/2018 13:43:00
4 I5 ReDet NE 1/9/2018 09:01:00
What i want is below:
1. For each ID that has multiple states, the Type should match the states in the order they were entered.
Ex:
ID 1:
OH -> Det 1st entry(1/1/2018) matches
CO -> Det 1st entry(1/1/2018) and
NC -> Det 1st entry(1/2/2018)
And
OH -> ReDet 2st entry(3/1/2018) matches
CO -> ReDet 2st entry(3/1/2018) and
NC -> ReDet 2st entry(3/1/2018)
ID 2 does not have multiple states
ID 3 - 1st entry of each state's type does not match:
FL -> Det 1st entry's (1/4/2018) type does not match
CA -> ReDet 1st entry(1/5/2018)
ID 4 - 2st entry of each state's type does not match:
PA -> Det 1st entry(1/7/2018) type matches
NE -> Det 1st entry(1/8/2018)
But
PA -> Det 1st entry(1/8/2018) type does not match
NE -> ReDet 1st entry(1/9/2018)
I want records where an ID has multiple states and the 1st,2nd, 3rd,... record type of each state does not match
As per the table the output ID's would be 3 and 4.
I have below as a starting query:
SELECT * FROM dbo.tableLog AS log1
JOIN dbo.tableLog AS log2 ON log2.ID = log1.ID AND log2.state = log1.state
WHERE log1.type <> log2.type
but i think i am no where near for what i want.
Any help would be really appreciated.
What I have tried:
SELECT * FROM dbo.tableLog AS log1
JOIN dbo.tableLog AS log2 ON log2.ID = log1.ID AND log2.state = log1.state
WHERE log1.type <> log2.type