CODEWITHSUNDEEP
sqlnetezza
boomoto
boomoto

Reputation: 311

Select Statement Complicated data transformation

Ok I have 2 tables.

Table A is has a list of transactions for a given client, looks like this

ClientID Transaction  Start      Stop
1          1          2014-01-01 2014-01-25

Table B is a list of events, looks like this:

ClientID Event StartE      StopE
1         1    2014-01-05 2014-01-08
1         2    2014-01-10 2014-01-12
1         3    2014-01-14 2014-01-16

I want to "join" these tables together to get the following table:

ClientID Transaction  EFF         ENDS
1          1          2014-01-01  2014-01-05
1          1          2014-01-05  2014-01-08
1          1          2014-01-08  2014-01-10
1          1          2014-01-10  2014-01-12
1          1          2014-01-12  2014-01-14
1          1          2014-01-14  2014-01-16
1          1          2014-01-16  2014-01-25

I have come up with some solutions but it fails in some cases this is example where my code fails. I would like to try and approach this fresh.

Upvotes: 0

Views: 33

Answers (1)

ChrisV
ChrisV

Reputation: 1309

Something like this, not tested so not sure it works. Basically I'm UNION-ing all the dates into one column along with appropriate transaction number, then ordering by date and adding a row number, then joining that result set to itself to pair each row up with the subsequent row.

;WITH ordered AS (
    SELECT ROW_NUMBER OVER (ORDER BY [Date]) AS Row, ClientID, Transaction, [Date]
    FROM (
        SELECT ClientID, Transaction, StartE AS [Date]
        FROM B JOIN A ON StartE BETWEEN A.Start AND A.Stop

        UNION

        SELECT ClientID, Transaction, StopE AS [Date]
        FROM B JOIN A ON StopE BETWEEN A.Start AND A.Stop

        UNION

        SELECT ClientID, Transaction, Start AS [Date]
        FROM A

        UNION

        SELECT ClientID, Transaction, Start AS [Date]
        FROM A
    ) combineddates
)
SELECT o1.ClientID, o1.Transaction, o1.[Date] AS Start, o2.[Date] AS Stop
FROM ordered o1 JOIN ordered o2 ON o2.Row = o1.Row + 1

Upvotes: 1

Related Questions