Expression syntax

[#1]

Hi,

I want to get the count of ids where the difference between two dates is greater than 30.  Please help me with the syntax.

Thanks,

Sudha.

[petter · Accepted solution]

I think this works for you:

[#3]

Thank you so much!!

[petter]

You're welcome

[maxgro]

you have a timestamp, not a date, try with

count({<UPDATE_DT={"<$(=timestamp(Today()-30))"}>} distinct CASE_ID)

or you can remove the time part from your timestamp in the load script, using

date(floor(UPDATE_DT)) in the load part of the script

LOAD

     CASE_ID,

     ....  

     date(floor(UPDATE_DT))  as UPDATE_DT,

      ...

     NM_FULL

     ;

SQL SELECT

  BEACON.VW_CASES.CASE_ID,

  BEACON.VW_CASES.STATUS,

  BEACON.VW_CASES.TYPE,

  BEACON.VW_CASES.CREATED_BY_NM,

  BEACON.VW_CASES.CREATE_DT,

  BEACON.VW_CASES.ASSIGNED_TO_NM,

  BEACON.VW_CASES.ACCOUNT_CD,

  BEACON.VW_CASES.ACCOUNT_LONG_NM,

  ASSIGNED_TO_HIREARCHY.ID_STD_MGR_DIRECT_NM,

  ASSIGNED_TO_HIREARCHY.ID_STD_MGR_LV4_NM,

  BEACON.VW_CASES.UPDATE_DT,

  UPDATED_BY.USER_NAME,

  BEACON.VW_CASES.ASSIGNED_TO,

  ASSIGNED_TO_HIREARCHY.NM_FULL

FROM

  BEACON.VW_CASES,

  BEACON.VW_WORKFORCE_JPMC_V3  ASSIGNED_TO_HIREARCHY,

  BEACON.VW_USERS  UPDATED_BY

WHERE

  ( BEACON.VW_CASES.UPDATE_BY_SID=UPDATED_BY.USER_SID(+)  )

  AND  ( ASSIGNED_TO_HIREARCHY.ID_STANDARD(+)=BEACON.VW_CASES.ASSIGNED_TO  )

  AND 

  (

   BEACON.VW_CASES.STATUS  NOT IN  ( 'Ops Resolved By Me','Resolved-Rejected-OpsTeam','Complete','Resolved-Completed','Resolved-Deleted','Complete-Passed','Complete-Failed','Deleted','Closed'  )

   AND

   (

    ASSIGNED_TO_HIREARCHY.ID_STD_MGR_LVL4  IN  ( 'N054267'  )

    OR

    BEACON.VW_CASES.ASSIGNED_TO  IN  ( 'N054267'  )

    OR

    ASSIGNED_TO_HIREARCHY.ID_STD_MGR_LVL5  IN  ( 'N054267'  )

   )

  );

and come back to the previous expression

count({<UPDATE_DT={"<$(=Date(Today()-30))"}>} distinct CASE_ID)