CODEWITHSUNDEEP
sql-servert-sqljoin
Jamie Moore
Jamie Moore

Reputation: 15

SELF JOIN matching rows based on hierarchy

In the query below I'm trying to match this subset of rows so I can do a DATEDIFF between the end date of the 1st record to the live date of the 2nd record; then the same with DATEDIFF between the 2nd and 3rd record. This should carry on until the last record has an end date of NULL as there could be a case of 2,3,4,5 ... rows that this needs to be done on. The comparison needs to work off the locationcode and productgroupcode being the same then have an order whereby the 1st record is the earliest livedate and the last record has the oldest livedate. I can't seem to work out the logic at all though. Any help would be greatly appreciated.

I've currently got a WHERE clause on a specific locationcode/productgroupcode purely from a testing perspective as the result set would be large and cumbersome to test with. 

SELECT wpa1.[id] 'WPA1_ID', 
       wpa2.[id] 'WPA2_Id',     
       wpa1.[planogramucr], 
       wpa1.[locationproductgroupucr], 
       wpa1.[locationcode], 
       wpa1.[productgroupcode], 
       wpa1.[categoryname], 
       wpa1.[assignmentstatus],
       wpa1.[livedate], 
       wpa1.[enddate], 
       DATEDIFF(day, wpa1.EndDate, wpa2.LiveDate) AS 'DateDiff_bet_live_end',
       wpa1.[lastmodifieddate] 
FROM   [Staging].[CustomerWorkAround].[customerplanasscc] wpa1 
       JOIN [CustomerWorkAround].[customerplanasscc] wpa2
       ON wpa1.Id = wpa2.Id
       AND wpa1.PlanogramUCR = wpa2.PlanogramUCR
       AND wpa1.LocationProductGroupUCR = wpa2.LocationProductGroupUCR
WHERE wpa1.EndDate IS NOT NULL -- Don't need enddates in the first table
AND wpa1.LocationCode = 102
AND wpa1.ProductGroupCode = 38
ORDER  BY  wpa1.locationcode, 
           wpa1.productgroupcode, 
           wpa1.livedate;

This is an example of the result set:

**WPA1_ID   WPA2_Id planogramucr    locationproductgroupucr locationcode    productgroupcode    categoryname    assignmentstatus    livedate    enddate DateDiff_bet_live_end   lastmodifieddate**
8586    8586    7d82d260-820b-4d7e-ab2d-594b6c6cefba    66ca4c51-b7d8-4943-b3c7-b69be010d2b9    102 38  FRESH MEAT - PORK   0   2019-09-24T00:00:00 2019-11-01T00:00:00 -38 2019-09-24T08:47:36
8773    8773    880c9242-9bca-4c5c-8a91-131c720d4b37    66ca4c51-b7d8-4943-b3c7-b69be010d2b9    102 38  FRESH MEAT - PORK   1   2019-11-01T00:00:00 2019-11-30T00:00:00 -29 2019-09-24T08:47:36
8860    8860    66948c90-022e-46a3-9f06-fc26e1d9d3a0    207b6ca4-fb7d-4c9e-9a19-da8a06a51e31    102 38  FRESH MEAT - PORK   1   2019-11-30T00:00:00     43797   2019-09-24T08:47:41

Upvotes: 0

Views: 79

Answers (1)

LordBaconPants
LordBaconPants

Reputation: 1414

I would try something along these lines: 

 select
     [id] as 'WPA1_ID'
     LEAD([id],1) over (order by livedate) as 'WPA2_ID'
     <stuff>
     DATEDIFF(day, EndDate, LEAD(LiveDate,1) over (partition by locationcode, productgroupcode order by livedate)) as DateDiff_bet_live_end
 from 
     [Staging].[CustomerWorkAround].[customerplanasscc] wpa1 
 where 
     <stuff>

So using the LEAD (or LAG, which ever one gives you the number you are after) will let you get the row ahead (or behind) of the one you are looking at by the number specified.

https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15

It might not be a complete answer, but hopefully that will give you somewhere new to start

Upvotes: 2

Related Questions