Compare start date and end with consecutive records and merge

Database
Enthusiast

Compare start date and end with consecutive records and merge

Hi,

I have a table from where I need to check for a job code and period column.

Below is the sample input and expected output.

INPUT

IDStartDateEndDateJob code
11/16/201712/31/999911
11/5/20151/15/201711
11/10/20141/4/201522
21/16/201712/31/999911
21/5/20151/15/201722
21/10/20141/4/201511

EXPECTED OUTPUT

IDStartDateEndDateJob code
11/5/201512/31/999911
11/10/20141/4/201522
21/16/201712/31/999911
21/5/20151/15/201722
21/10/20141/4/201511

The problem statement is to merge the record on period if there is no Job code change.

With normal min and max on start date, I am able to get the output for ID 1, but for ID 2 as there is break in job code want to keep them as 3 records instead of two with a overlaping time.

 

Could anyone help with a query.

 

Thanks 

Akram

 


Accepted Solutions
Junior Contributor

Re: Compare start date and end with consecutive records and merge

I forgot to add more details:

The output columns in RETURNS must match exactly the order and data type of the input columns in VARIANT_TYPE.

If any of those columns is a Unicode (var)char the size must be doubled, e.g. VarChar(10) -> VarChar(20) Character Set Unicode.

 

1 ACCEPTED SOLUTION
8 REPLIES
Junior Contributor

Re: Compare start date and end with consecutive records and merge

There are some functions based on Periods, but you have to adjust your logic (Periods include the start, but exclude the end):

WITH cte AS
 (
   SELECT id,
PERIOD(StartDate-1, EndDate) AS pd, -- create a period
Job_code FROM tab ) SELECT id,
Begin(pd)+1, End(pd), -- split the period
job_code FROM TABLE (Td_Normalize_Meet (NEW VARIANT_TYPE(cte.ID, cte.Job_code) ,cte.pd) RETURNS (ID INT ,Job_code INT ,pd PERIOD(DATE) ,Cnt INTEGER) HASH BY ID, Job_code LOCAL ORDER BY ID, Job_code, pd ) dt

 

Enthusiast

Re: Compare start date and end with consecutive records and merge

Thank you so much !!

 

Tried the replicate but getting below erorr :

The output grouping column list does not match with input grouping column list.

Junior Contributor

Re: Compare start date and end with consecutive records and merge

I forgot to add more details:

The output columns in RETURNS must match exactly the order and data type of the input columns in VARIANT_TYPE.

If any of those columns is a Unicode (var)char the size must be doubled, e.g. VarChar(10) -> VarChar(20) Character Set Unicode.

 

Enthusiast

Re: Compare start date and end with consecutive records and merge

Thankyou so much again Dieter.

Just awesome. 

One more query, if I have to insert the result set into a table it is not allowing me to.

And went through your post and found that w cannot build a view on CTE.

So if I want to get rid of the CTE, can I change the table structure by replacing the date columns to a Period column and directly use it as

TABLE (TD_NORMALIZE_MEET (NEW VARIANT_TYPE(mytable.ID, mytable.JobCode),mytable.pd), or is there any workaround to get the data from CTE into another table.

Junior Contributor

Re: Compare start date and end with consecutive records and merge

You can create a view doing the period calculation:

REPLACE VIEW myview AS         
   SELECT id,
      PERIOD(StartDate-1, EndDate) AS pd, -- create a period
      Job_code
   FROM tab
;

and then use this instead of the cte:

SELECT id,
   Begin(pd)+1, End(pd), -- split the period 
   job_code 
FROM    
        TABLE (Td_Normalize_Meet
                (NEW VARIANT_TYPE(myView.ID, myView.Job_code)
                ,myView.pd)
        RETURNS (ID INT 
                ,Job_code INT
                ,pd PERIOD(DATE)
                ,Cnt INTEGER)
        HASH BY ID, Job_code
        LOCAL ORDER BY ID, Job_code, pd
        ) dt

 

Enthusiast

Re: Compare start date and end with consecutive records and merge

Thanks again Dieter :) Case Solved :)

Enthusiast

Re: Compare start date and end with consecutive records and merge

Hi Dieter,

 

We have noramilsed the data now based on the period, however we have another problem where we want ot ignore the end date and calcualte the end date as the new records from date -1.

For Example:

INPUT :

ID Start Date End DateCode
12/16/20172/16/2018a
12/17/20182/17/2019b
13/18/20163/18/2020c
12/16/20165/20/2016d

OUTPUT:

ID Start Date End DateCode
12/16/20163/17/2016d
13/18/20162/15/2017c
12/16/20172/16/2017a
12/17/2018EOTb

 

Thanks in Advance.

Junior Contributor

Re: Compare start date and end with consecutive records and merge

There's no LAG function in Teradata, but it's easy to emulate:

MIN(start_date)
OVER (PARTITION BY ID
      ORDER BY start_date
      ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)

returns the previous row's start_date, now subtract 1 and add a COALESCE to replace the final NULL.