Fetch records based on next value

Database
Enthusiast

Fetch records based on next value

 

Could some one please help me with the below sceanrio.

 

I will be getting multiple versions of floor plan data from source which has extract_date, code and quantity. 

Based on the extract date, i have to populate valid_from and valid_to columns in my landing table.

Valid from is same as extract date and valid_to should be date-1 of next extract date.

Rules to populate valid_to column: 

1. If the storeno & code combination has next version, then valid_to should be next extract_date -1

2. If the floor plan does not have next version and if the max extarct date and extract date of this combination is same then set the valid_to as high end date (9999-12-31)

3. If the storeno & code combination doesnot have next version but there next versions available, the valid to should be next extract date -1 

 

Source Data:

extract_datestorenoCodeQuantityComments
2017-07-072015A2Q-3TR2V1
2017-09-062015A2Q-3TR2V2
2017-09-192015A2Q-3TR2V3
2017-10-012015A2Q-3TR3V4
2017-07-072017A2Q-3TR2V1
2017-07-072019A2Q-3TR2V1
2017-09-062019A2Q-3TR2V2

 

Output:

storenoCodeQuantityvalid_fromvalid_to
2015A2Q-3TR22017-07-072017-09-05
2015A2Q-3TR22017-09-062017-09-18
2015A2Q-3TR22017-09-192017-09-30
2015A2Q-3TR32017-10-019999-12-31
2017A2Q-3TR22017-07-072017-09-05
2019A2Q-3TR22017-07-072017-09-05
2019A2Q-3TR22017-09-06

2017-09-18

 

I was able to achive rule 1 and 2 using the below query. Can you please help me with rule 3 

select
storeno
,code
,quantity
,extract_date as valid_from
,MAX(extract_date) over (order by extract_date desc) as max_extract_date
,MAX(extract_date -1 ) over (partition by storeno,code order by storeno,code,extract_date rows between 1 following and 1 following) as valid_to_tmp
,CASE WHEN valid_to_tmp IS NULL AND max_extract_date = extract_date THEN '9999-12-31'
END AS valid_to_tmp2
,COALESCE ( valid_to_tmp, valid_to_tmp2, current_date) AS valid_to

FROM source_data

 

Thanks in advance

 


Accepted Solutions
Junior Contributor

Re: Fetch records based on next value

That's a bit tricky, you probably need nested OLAP-functions.

This seems to match you description:

SELECT 
   dt.*,
   CASE
     -- special case: maximum date (probably per code?) 
     WHEN Max(valid_from) Over (PARTITION BY code) = valid_from 
       THEN DATE '9999-12-31'
     ELSE Coalesce(valid_to       -- there's a next date, use it
                  ,Min(valid_to)  -- there's no next date -> find the next valid_to per code
                   Over (PARTITION BY code
                         ORDER BY valid_from, valid_to
                         ROWS BETWEEN 1 Following AND Unbounded Following)
                  )
   END AS valid_to
FROM
 (
   SELECT
      storeno
      ,code
      ,quantity
      ,extract_date AS valid_from
      ,Min(extract_date)  -- next date for each store/code
       Over (PARTITION BY code,storeno
             ORDER BY extract_date
             ROWS BETWEEN 1 Following AND 1 Following)-1 AS valid_to
   FROM source_data
 ) AS dt

 

1 ACCEPTED SOLUTION
3 REPLIES
Enthusiast

Re: Fetch records based on next value

Divya,

1) can you clarify if the 3rd rule is correct or any typo?

 

2) Also, can you confirm your output record of store-2017 is correct? i.e the storeno=2017 valid_to = 2017-09-05 ( extract_date - 1 of V2 of different store-2019 not same store - 2017.).. is this logically correct?

 

2017A2Q-3TR22017-07-072017-09-05

 

3) How did you arrive the -  storeno=2019 V2 record's valid_To value as 2017-09-18? ( last record)

4) your query has some syntax err.. can you post correct query

 

Junior Contributor

Re: Fetch records based on next value

That's a bit tricky, you probably need nested OLAP-functions.

This seems to match you description:

SELECT 
   dt.*,
   CASE
     -- special case: maximum date (probably per code?) 
     WHEN Max(valid_from) Over (PARTITION BY code) = valid_from 
       THEN DATE '9999-12-31'
     ELSE Coalesce(valid_to       -- there's a next date, use it
                  ,Min(valid_to)  -- there's no next date -> find the next valid_to per code
                   Over (PARTITION BY code
                         ORDER BY valid_from, valid_to
                         ROWS BETWEEN 1 Following AND Unbounded Following)
                  )
   END AS valid_to
FROM
 (
   SELECT
      storeno
      ,code
      ,quantity
      ,extract_date AS valid_from
      ,Min(extract_date)  -- next date for each store/code
       Over (PARTITION BY code,storeno
             ORDER BY extract_date
             ROWS BETWEEN 1 Following AND 1 Following)-1 AS valid_to
   FROM source_data
 ) AS dt

 

Enthusiast

Re: Fetch records based on next value

@acumens, Thanks for checking. Rule and data are correct. If the store and code combination doesnot have next record and if the maximum extract date and the extract date of the combination is different, we have to check for the next available extarct date and set the valid to as extract_date -1.

@dnoeth Many thanks Dieter, It is working as expected.