Archives of the TeradataForum

Message Posted: Fri, 28 May 2010 @ 10:20:54 GMT

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

Hi Anusha,

Thanks so much for your concern, please find the below query and let me know your views. It is changing the count each time.

     SELECT T1.PERIOD,
            T1.CUST_ID,
            T1.VEH_MDL_CD,
            T1.VEH_MK_NM,
            T1.VEH_SRES_NM,
            T1.VIN_NB,
            T1.CUST_ACTN_DT,
            T1.NEW_VIN_NB,
            T1.NEW_CUST_ACT_DT,
            T1.NEW_VEH_MDL_CD,
            T1.NEW_VEH_MK_NM,
            T1.NEW_VEH_SRES_NM,
            1 TENYEARS_UNIT,
            ( ( ((CAST (T1.CUST_ACTN_DT AS DATE))- (CAST(T1.NEW_CUST_ACT_DT AS DATE)))
                       *12 (DEC) ) / 365 ) NBR_MONTHS
       FROM (
       SELECT PERIOD,
              CUST_ID,
              VEH_MDL_CD,
              VEH_MK_NM,
              VEH_SRES_NM,
              VIN_NB,
              CUST_ACTN_DT,
              NEW_VIN_NB,
              NEW_CUST_ACT_DT,
              NEW_VEH_MDL_CD,
              NEW_VEH_MK_NM,
              NEW_VEH_SRES_NM, ROW_NUMBER()
                        OVER(ORDER BY CUST_ID,CUST_ACTN_DT ASC, NEW_CUST_ACT_DT ASC) AS ALL_ROWNUM
               FROM (SELECT   A.CUST_ACTN_DT (FORMAT 'YYYY')(CHAR(4)) AS PERIOD,
                              A.CUST_ID, A.VEH_MDL_CD, A.VEH_MK_NM, A.VEH_SRES_NM,
                              A.VIN_NB, A.CUST_ACTN_DT, B.VIN_NB AS NEW_VIN_NB,
                              B.CUST_ACTN_DT AS NEW_CUST_ACT_DT,
                              B.VEH_MDL_CD AS NEW_VEH_MDL_CD,
                              B.VEH_MK_NM AS NEW_VEH_MK_NM,
                              B.VEH_SRES_NM AS NEW_VEH_SRES_NM
                         FROM V_WOFDWVW.INT_SET_CUST_RETN A,
                              V_WOFDWVW.INT_SET_CUST_RETN B
                        WHERE A.VIN_NB <> B.VIN_NB
                          AND A.CUST_ID = B.CUST_ID
                          AND CAST(B.CUST_ACTN_DT AS DATE) BETWEEN CAST(A.CUST_ACTN_DT AS DATE)
                          AND ADD_MONTHS (CAST(A.CUST_ACTN_DT AS DATE), +120 )
                      ) A
                     ) T1

     INNER JOIN
            (SELECT PERIOD,CUST_ID,VIN_NB, MIN(MIN_ROWNUM) AS MIN_ROWNUM
     FROM
            (SELECT PERIOD,
                    CUST_ID,VIN_NB, ROW_NUMBER()
                         OVER(ORDER BY CUST_ID, CUST_ACTN_DT ASC, NEW_CUST_ACT_DT ASC) AS MIN_ROWNUM
                 FROM
                 (SELECT    A.CUST_ACTN_DT (FORMAT 'YYYY')(CHAR(4)) AS PERIOD,
                                A.CUST_ID, A.VEH_MK_NM, A.VEH_SRES_NM, A.VIN_NB,
                                A.CUST_ACTN_DT, B.VIN_NB AS NEW_VIN_NB,
                                B.CUST_ACTN_DT AS NEW_CUST_ACT_DT,
                                B.VEH_MK_NM AS NEW_VEH_MK_NM,
                                B.VEH_SRES_NM AS NEW_VEH_SRES_NM
                           FROM V_WOFDWVW.INT_SET_CUST_RETN A,
                                V_WOFDWVW.INT_SET_CUST_RETN B
                          WHERE A.VIN_NB <> B.VIN_NB
                            AND A.CUST_ID = B.CUST_ID
                            AND CAST(B.CUST_ACTN_DT AS DATE) BETWEEN CAST(A.CUST_ACTN_DT AS DATE)
                            AND ADD_MONTHS (CAST(A.CUST_ACTN_DT AS DATE),+120)
                       ) A)B
             GROUP BY PERIOD, CUST_ID, VIN_NB) T2

       ON T1.PERIOD = T2.PERIOD
        AND T1.CUST_ID = T2.CUST_ID
        AND T1.ALL_ROWNUM = T2.MIN_ROWNUM
        AND t1.vin_nb = t2.vin_nb;

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