CODEWITHSUNDEEP
sqlsql-serverselectwhere-clause
Zefurion
Zefurion

Reputation: 45

MS SQL Advanced Select where something has been swapped

My problem is that I need to find clients who has been swapped between two companies for more than 2 times, so:

We have company A and B with Ids for example 1 and 2. The Client has been swapped from company A to B, it has been logged to another table History.

Now i need to Select clients who has beed swapped from company A to B THEN from company B to A and THEN again from company A to B (in the end two swaps)

I need to say that I dont know how to do it. Any ideas? Many thanks for every answer.

Edit:

@jarlh example:

Table Clients
ID        Name       CompanyId
1         Pikachu     63
2         Mew          34
3         Reptide     63

Table History

ID   ClientId   CompanyId
1     1            34
2     1            63
3     1            34
4     1            63
5     2            34
6     2            73
7     2            34
8     2            34
9     3            34
10    3            84
11    3             63
12    3            34
13    3            34
14    3            63

Result:

Id       ClientId
1        1
2        3

Upvotes: 1

Views: 72

Answers (2)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

If your two companies are fixed, and you only want one direction A->B->A and don't care about B->A->B, you can find from History all that have Company B and existed in A both before and after that.

Something like:

SELECT * FROM HistoryTable ht
WHERE CompanyID = 63 
AND EXISTS (SELECT * FROM HistoryTable h1 
            WHERE h1.ClientID = ht.ClientID 
            AND h1.CompanyID = 34
            AND h1.ID < ht.ID)
AND EXISTS (SELECT * FROM HistoryTable h2 
            WHERE h2.ClientID = ht.ClientID 
            AND h2.CompanyID = 34
            AND h2.ID > ht.ID)

EDIT: With 3 swaps, it get a bit more complicated:

SELECT * FROM HistoryTable ht 
WHERE CompanyID IN (63,34)
AND EXISTS (SELECT * FROM HistoryTable h1 
            WHERE h1.ClientID = ht.ClientID 
            AND h1.CompanyID IN (34,63)
            AND h1.CompanyID <> ht.CompanyID
            AND h1.ID < ht.ID)
AND EXISTS (SELECT * FROM HistoryTable h2 
            WHERE h2.ClientID = ht.ClientID 
            AND h2.CompanyID IN (34,63)
            AND h2.CompanyID <> ht.CompanyID
            AND h2.ID > ht.ID
            AND EXISTS (SELECT * FROM HistoryTable h3 
                        WHERE h3.ClientID = h2.ClientID 
                        AND h3.CompanyID IN (34,63)
                        AND h3.CompanyID <> h2.CompanyID
                        AND h3.ID > h2.ID))

I also added for swaps in both directions. In case of A->B->A->B, ht table should represent the B - 2nd company, and we check if exists A before it (h1), and if there is A after it (h2) which also have B after itself (h3)

Upvotes: 1

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

The simplest way to do it is just to join your history table on itself 4 times and use joins and where to filter results. It will work, but is very horrible performance-wise. Use it only if you don't have a lot of data or are not in rush.

SELECT * FROM HistoryTable h1
INNER JOIN HistoryTable h2 ON h2.ClientID = h1.ClientID 
                           AND h2.Id >h1.ID 
                           AND h2.CompanyID <> h1.CompanyID
INNER JOIN HistoryTable h3 ON h3.ClientID = h2.ClientID 
                           AND h3.Id >h2.ID 
                           AND h3.CompanyID = h1.CompanyID
INNER JOIN HistoryTable h4 ON h4.ClientID = h3.ClientID 
                           AND h4.Id >h3.ID 
                           AND h4.CompanyID = h2.CompanyID
WHERE h1.CompanyID IN (34,63)
AND h2.CompanyID IN (34,63)

Upvotes: 1

Related Questions