1 Reply Latest reply: Jul 17, 2014 3:53 PM by Manish Kachhia RSS

    Difference in Date

      Hi All

       

      I have a requirement , where i have to compare the STMNT_DT ,

      If the Period type = Year End , for each nick name i should compare the STMNT_DT

      If the date difference is > 730 days when comparing STMNT_DT witht the previous STMNT_DT record, i should display those Nick name records.

       

       

      Thanks

      Senthil

        • Re: Difference in Date
          Manish Kachhia

          Temp:

          LOAD *, NCKNM&AGENT&COUNTRY&UNIT as KEY Inline

          [

            NCKNM, AGENT, COUNTRY, UNIT, PERIOD TYPE, STMNT_DT

            100, ABC, ABC, UBIT, 6 Months, 31/06/2000

            100, ABC, ABC, UBIT, Year End, 31/12/2000

            100, ABC, ABC, UBIT, Year End, 31/12/2001

            100, ABC, ABC, UBIT, Year End, 31/12/2003

            100, ABC, ABC, UBIT, Year End, 31/12/2004

            100, ABC, ABC, UBIT, 6 Months, 31/12/2000

            100, ABC, ABC, UBIT, Year End, 31/12/2007

            100, ABC, ABC, UBIT, Year End, 31/12/2005

            100, ABC, ABC, UBIT, Year End, 31/12/2006

            100, ABC, ABC, UBIT, Year End, 31/12/2009

            100, ABC, ABC, UBIT, Year End, 31/12/2008

            100, ABC, ABC, UBIT, Year End, 31/12/2010

            101, ABC, ABC, UBIT, 6 Months, 31/06/2000

            101, ABC, ABC, UBIT, Year End, 31/12/2000

            101, ABC, ABC, UBIT, Year End, 31/12/2001

            101, ABC, ABC, UBIT, Year End, 31/12/2003

            101, ABC, ABC, UBIT, Year End, 31/12/2004

            101, ABC, ABC, UBIT, 6 Months, 31/12/2000

            101, ABC, ABC, UBIT, Year End, 31/12/2007

            101, ABC, ABC, UBIT, Year End, 31/12/2005

            101, ABC, ABC, UBIT, Year End, 31/12/2006

            101, ABC, ABC, UBIT, Year End, 31/12/2009

            101, ABC, ABC, UBIT, Year End, 31/12/2008

            101, ABC, ABC, UBIT, Year End, 31/12/2010

          ];

           

           

          Temp2:

          Load

            *,

            IF(KEY = Previous(KEY) and [PERIOD TYPE] = 'Year End' , STMNT_DT - Previous(STMNT_DT)) AS Difference

          Resident Temp

          Order By KEY, STMNT_DT ASC;

           

           

          Drop Table Temp;

           

           

          NoConcatenate

          Temp3:

          Load

            NCKNM,

            If(NOT ISNULL(Difference) and Difference >= 730, 'Yes','No') as Flag,

            AGENT, COUNTRY, UNIT, [PERIOD TYPE], Difference

          Resident Temp2;

           

           

          Drop Table Temp2;

          ===========================

           

          Now use Flag as your list box and selecting Yes from Flag List Box will give you desired result..