All Forums Database
samit_9999 37 posts Joined 03/06
06 Mar 2008
Finding Continuos Span

Hi,I have a table with the following data.I want to find the emp nbr's start date and end date.If there is a gap in span which exceeds more than 3 years than i want to find the most continuos spanIn the below example the emp was active continuosly from 1/1/2005 to 06/30/2008 with no breaks so the results should be Start Date = 01/01/2005 , End Date = 06/30/2008Emp Nbr Start Date End Date1 01/01/2005 03/31/20051 04/01/2005 12/31/20051 01/01/2006 06/30/20061 07/01/2006 12/31/20061 01/01/2007 12/31/20071 01/01/2008 06/30/2008In the below example the emp was active from 1/1/1998 to 06/30/2008.There was a break in span from 12/31/2001 to 01/01/2005 which is more than 3 years so the results should be Start Date = 01/01/2005 , End Date = 06/30/2008Emp Nbr Start Date End Date1 01/01/1998 12/31/20011 01/01/2005 03/31/20051 04/01/2005 12/31/20051 01/01/2006 06/30/20061 07/01/2006 12/31/20061 01/01/2007 12/31/20071 01/01/2008 06/30/2008In the below example the emp was active from 1/1/2002 to 06/30/2008.There was a break in span from 12/31/2003 to 01/01/2005 which is less than 3 years so the results should be Start Date = 01/01/2003 , End Date = 06/30/2008Emp Nbr Start Date End Date1 01/01/2002 12/31/20031 01/01/2005 03/31/20051 04/01/2005 12/31/20051 01/01/2006 06/30/20061 07/01/2006 12/31/20061 01/01/2007 12/31/20071 01/01/2008 06/30/2008Please let me know how to acheive the above results in a single query in teradataThanks

joedsilva 505 posts Joined 07/05
07 Mar 2008

To be honest I am little confused with requirements (may be because these are the moth hours for me...)In the second sample shouldn't you be picking the range01/01/1998 12/31/2001 which is 4 yrs compared toStart Date = 01/01/2005 , End Date = 06/30/2008which is only 3.5 yrs ?? so the longest duration is the first one ?And in the third example your probably meant Start Date = 01/01/2002 , End Date = 06/30/2008 ?? not Start Date = 01/01/2003 , End Date = 06/30/2008Assuming these are typos, following is a crap query which I hadn't had the time to cleanup much :)SELECT Y.EMP_NBR, C4.CALENDAR_DATE START_DATE, Y.END_DATEFROM ( SELECT X.EMP_NBR, X.END_DATE ,MUL,C3.DAY_OF_CALENDAR ,SUM(MUL*(C1.DAY_OF_CALENDAR - C2.DAY_OF_CALENDAR)) OVER(PARTITION BY EMP_NBR ORDER BY START_DATE ROWS UNBOUNDED PRECEDING) +(C3.DAY_OF_CALENDAR - C1.DAY_OF_CALENDAR) TOTDAYS FROM ( SELECT EMP_NBR ,START_DATE ,END_DATE ,COALESCE(MAX(D.START_DATE) OVER (PARTITION BY D.EMP_NBR ORDER BY D.START_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), START_DATE) PREV_START_DATE ,COALESCE(MAX(D.END_DATE) OVER (PARTITION BY D.EMP_NBR ORDER BY D.START_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), END_DATE ) PREV_END_DATE ,CASE WHEN ADD_MONTHS(PREV_END_DATE, 36) > D.START_DATE THEN 1 ELSE 0 END MUL FROM DATA003 D ) X INNER JOIN SYS_CALENDAR.CALENDAR C1 ON X.START_DATE = C1.CALENDAR_DATE INNER JOIN SYS_CALENDAR.CALENDAR C2 ON X.PREV_START_DATE = C2.CALENDAR_DATE INNER JOIN SYS_CALENDAR.CALENDAR C3 ON X.END_DATE = C3.CALENDAR_DATE) Y INNER JOIN SYS_CALENDAR.CALENDAR C4 ON Y.DAY_OF_CALENDAR-Y.TOTDAYS = C4.DAY_OF_CALENDARQUALIFY RANK() OVER(PARTITION BY EMP_NBR ORDER BY TOTDAYS DESC) = 1ORDER BY EMP_NBR, START_DATE ; Emp_Nbr START_DATE END_DATE 1 01/01/2005 06/30/2008 2 01/01/1998 12/31/2001 3 01/01/2002 06/30/2008

dnoeth 4628 posts Joined 11/04
07 Mar 2008

Hi Samit,this solution is quite similar to Joe's:SELECT emp, MIN(start_date) AS start_d, MAX(end_date) AS end_dFROM ( SELECT emp, start_date, end_date,-- check if the previous date passes your condition of "no gap > 3 years" and do a running total calculating a "grp" number of similar rows SUM(CASE WHEN ADD_MONTHS(prev_end_date, 36) < start_date THEN 1 ELSE 0 END) OVER (PARTITION BY emp ORDER BY start_date ROWS UNBOUNDED PRECEDING) AS grp FROM ( SELECT emp, start_date, end_date, MIN(end_date) OVER (PARTITION BY emp ORDER BY end_date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_end_date FROM dropme ) dt ) dtGROUP BY emp, grpQUALIFY RANK() OVER ( PARTITION BY emp ORDER BY end_d - start_d DESC) = 1Using that approach you can solve almost any problem with OLAP functions :-)Dieter

Dieter

samit_9999 37 posts Joined 03/06
15 May 2008

Thank You guys

You must sign in to leave a comment.