Difference in Date

[#1]

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

[MK_QSL]

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..