Archives of the TeradataForum

Message Posted: Tue, 31 Oct 2006 @ 13:09:37 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Prahlad,

I had posted this SQL few months back. It may be useful or may need modification.

     /*
     Start date: 2006-06-01 End date: 2006-08-01 Result: 2
     Start date: 2006-07-01 End date: 2006-08-01 Result: 1
     Start date: 2006-07-23 End date: 2006-08-01 Result: 1 */
     SEL
     Start_Dte
     ,End_Dte
     ,CASE
     -- First day of month
     WHEN EXTRACT(DAY FROM Start_Dte) = 1 AND EXTRACT(DAY FROM End_Dte) = 1
     AND (EXTRACT(YEAR FROM Start_Dte) = EXTRACT(YEAR FROM End_Dte))
     THEN EXTRACT(MONTH FROM End_Dte) - EXTRACT(MONTH FROM Start_Dte)
     -- Year apart
     WHEN EXTRACT(DAY FROM Start_Dte) = 1 AND EXTRACT(DAY FROM End_Dte) = 1
     AND ABS(EXTRACT(YEAR FROM Start_Dte) - EXTRACT(YEAR FROM End_Dte) ) = 1
     THEN EXTRACT(MONTH FROM End_Dte) + 12 - EXTRACT(MONTH FROM Start_Dte)
     -- N year apart
     WHEN EXTRACT(DAY FROM Start_Dte) = 1 AND EXTRACT(DAY FROM End_Dte) = 1
     AND ABS(EXTRACT(YEAR FROM Start_Dte) - EXTRACT(YEAR FROM End_Dte) ) > 1
     THEN EXTRACT(MONTH FROM End_Dte) + 12 - EXTRACT(MONTH FROM Start_Dte) +
     (EXTRACT(YEAR FROM End_Dte) - EXTRACT(YEAR FROM Start_Dte)-1 ) * 12
     -- Any day of month and same year
     WHEN EXTRACT(DAY FROM Start_Dte) > 1 AND EXTRACT(DAY FROM End_Dte) = 1
     AND ABS(EXTRACT(YEAR FROM Start_Dte) - EXTRACT(YEAR FROM End_Dte) ) = 0
     THEN EXTRACT(MONTH FROM End_Dte) + 12 - EXTRACT(MONTH FROM Start_Dte) +
     (EXTRACT(YEAR FROM End_Dte) - EXTRACT(YEAR FROM Start_Dte)-1 ) * 12
     -- Any day of month and different year
     WHEN EXTRACT(DAY FROM Start_Dte) > 1 AND EXTRACT(DAY FROM End_Dte) > 1
     AND ABS(EXTRACT(YEAR FROM Start_Dte) - EXTRACT(YEAR FROM End_Dte) ) > 0
     THEN EXTRACT(MONTH FROM End_Dte) + 12 - (EXTRACT(MONTH FROM Start_Dte))
     + (EXTRACT(YEAR FROM End_Dte) - EXTRACT(YEAR FROM Start_Dte)-1 ) * 12

     END AS Mth_Diff
     ,EXTRACT(MONTH FROM start_Dte)
     ,EXTRACT(MONTH FROM End_Dte)
     , (EXTRACT(YEAR FROM End_Dte) - EXTRACT(YEAR FROM Start_Dte)-1 ) * 12 as
     in_yrs
     FROM test
     ORDER BY 1

Thanks,

Vinay Bagare

	<Prev	Next>		<<First	<Prev	Next>	Last>>