Date gap analysis

Analytics
N/A

Date gap analysis

Hi All,

I am trying to find out the records with dates overlapped or continous and then making it as a single record with the minimum date to the max date.

Here is the example of what i am trying to achieve.

Table 1







Party_product
Party_id Product_id Start_dt End_dt
500 1 1. Jan. 2011 30. Jan. 2011
500 1 15. Feb. 2011 28. Feb. 2011
500 1 1. Mar. 2011 30. Mar. 2011
500 2 10. Apr. 2011 15. Oct. 2011
500 2 1. Jun. 2011 18. Oct. 2011
500 3 1. Jan. 2011 31. Jan. 2011
500 4 1. Jan. 2011 30. Jan. 2011
500 4 15. Feb. 2011 28. Feb. 2011
500 5 1. Mar. 2011 30. Mar. 2011
500 5 10. Apr. 2011 15. Oct. 2011
500 5 1. Jun. 2011 18. Oct. 2011

Table 2





Product
Product_id Segment Name
1 Corporate
2 Corporate
3 Corporate
4 Retail
5

Retail

Resultant Table







Party_Segment
Party_id Segment Name Start_dt End_dt
500 Corporate 1. Jan. 2011 30. Jan. 2011
500 Corporate 15. Feb. 2011 30. Mar. 2011
500 Corporate 10. Apr. 2011 18. Oct. 2011
500 Retail 1. Jan. 2011 30. Jan. 2011
500 Retail 15. Feb. 2011 30. Mar. 2011
500 Retail 10. Apr. 2011 18. Oct. 2011

I tried the Row Preceeding option also the overlap function, but I am still not able to get the proper result. Please help me to solve this, incase you have encounter this scenario earlier or please point me to any existing thread talks about the same.

Thanks in advance.

Tags (1)
4 REPLIES
N/A

Re: Date gap analysis

Depends on your DB release

in 13.1 the following might give what you need

  1. convert start_dt and end_dt into a single period data type
  2. use EXPAND ON to generate one row per day 
  3. Use OLAP Function sorted by date to check if previous row contains prev date if not set 1 else 0 (coalsece this to 1 - first record)
  4. Use OLAP function to do a cummulative sum on the column of 3 - this give you the periods
  5. do a min and max by period identivier

prio to 13.1 do a product join tp system calendar to achive the same

and don't forget the join to table two ;-)

Re: Date gap analysis

I could really use some help in writing this query. I am new to Teradata and would appreciate any help on this issue.  This issue has been a pain in my neck for the last 6 months and I cannot seem to find a way to consolidate consecutive date spans and/or overlapping date spans into one span (From date and TO Date)...I beseech you for some help on getting this written so I can finally get some relief.

Thank you so much

John

N/A

Re: Date gap analysis

on which realease are you?

Re: Date gap analysis

Hi,

Please look at this post by dieter back in 2010,

http://forums.teradata.com/forum/database/how-to-find-the-continious-records-based-on-key-column#com...

This must help you... if I understand your situation correctly.

Thanks!

PV