Select where rows does not match for multiple state record entries

Question

0.00/5 (No votes)

See more:

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

Posted 16-Jul-18 13:22pm

Member 4581741

Updated 18-Jul-18 3:11am

v2

Add a Solution

Comments

Mike V Baker 18-Jul-18 10:53am

As I read your description I'm confused by the records you believe would be in the result set. Take for example ID 1. You have records where the ID and the state match. 1 & OH, 1 & CO, 1 & NC. The types of each of these records do not match Ret <> DetRet. This is true for each of these sets so all these six records for ID 1 would qualify ID=ID AND State=State AND Type<>Type. The only ones that I see that would NOT fall into this are ID3 & State CA, and ID4 & State PA. Those are the only ones that would be excluded according to your criteria since their types are equal.

Member 4581741 19-Jul-18 1:56am

Sorry if the question is not clear.
"You have records where the ID and the state match. 1 & OH, 1 & CO, 1 & NC. The types of each of these records do not match Ret <> DetRet."
The "Type" of 1st entry of each state should match. ID 1 has "Det" as type for each state for 1st entry, and 2nd entry for each state for ID 1 has Type "Redet". So that's good.
For ID3 and ID4 it does not match.
Also, Det and ReDet are just example of Types. It can be any one of 5 values.

Mike V Baker 19-Jul-18 13:00pm

I don't think I would trust the results given the columns you're describing here. The reason being that you're concerned with 1st entry, 2nd entry, 3rd entry, etc for each state and you're relying on 'natural order' to figure out which is 1st and 2nd. You don't have any indexing to control what's 1st, 2nd, 3rd.... To my knowledge there's nothing that guarantees the results will come to you in that natural order when you query. You have to create a column such as EntryOrder to use as a control and sort. You could create a composite index on ID + State + EntryOrder. (If I were doing this I'd consider using a separate table and a foreign key for states.) [1,OH,1], [1,OH,2], [1,CO,1], [1,CO,2], [1,NC,1], [1,NC,2]. Even after doing that I'm not sure it can be done in a single shot of sql. You can order by ID, EntryOrder, State to get them in the right order, group by ID, and EntryOrder and then concatenate the types and put the result in a temporary table. Then select from the table where the concatenated types are not equal. There's something else I was just looking at here https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server but I'm not sure it'll work for you. It doesn't include the comparison component.

Member 4581741 19-Jul-18 18:11pm

There is id field but CreateDate can be used to know the sequence of the entries for each state.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Select where rows does not match for multiple state record entries

Add your solution here

Preview 0

Select where rows does not match for multiple state record entries

Add your solution here

Preview 0

Existing Members

...or Join us