All Forums Analytics
Sudhar 7 posts Joined 01/11
14 Nov 2011
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.

ulrich 816 posts Joined 09/09
15 Nov 2011

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 ;-)

 

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

joquinn238 4 posts Joined 02/12
16 Mar 2012

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

ulrich 816 posts Joined 09/09
16 Mar 2012

on which realease are you?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

AB75151 20 posts Joined 06/09
25 Apr 2012

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#comment-12490

 

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

Thanks!

PV

You must sign in to leave a comment.