Archives of the TeradataForum

Message Posted: Fri, 28 May 2010 @ 13:59:32 GMT

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

Hi Syed,

you could use a Common Table Expression for the ROW_NUMBER, but you probybly don't need it at all.

Dieter

     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
            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,
            ROW_NUMBER() OVER(
            ORDER BY
              CUST_ID,
              CUST_ACTN_DT ASC,
              NEW_CUST_ACT_DT ASC
            ) AS ALL_ROWNUM
          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)
        ) T1
     QUALIFY
        t1.all_row_num = MIN(all_row_num)
                         OVER(PARTITION BY PERIOD, CUST_ID, VIN_NB)

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