Finding the latest date

Database
Enthusiast

Finding the latest date

Hi Everyone!

Aggregates cannot be used in WHERE clauses and I am trying to find a way to return the latest data  according to date (GRIP_DT).  To explain, within each table there are views of records:  For example today's view would have a set of 100 records and then yesterday's view would hold the same records minus any updates made in today's view.  Get it?  The difference is all in the updates that take place daily. 

Now my query hard codes for the GRIP_DT in which I would return the latest or most up to date records.  i cannot use MAX(GRIP_DT) in the WHERE clause.  Using the HAVING condition could work but finding the date in which the tables were updates were performed could be yesterday or 2, 3 days before.

How can I write the query to get the most up to date records?

SELECT

       CASE
         WHEN  (CURRENT_DATE - CAST(b.CREAT_TMSTMP AS DATE)) <=30 THEN '0 - 30 DAYS'
         WHEN (CURRENT_DATE - CAST(b.CREAT_TMSTMP AS DATE)) BETWEEN 31 AND 60 THEN '31 - 60 DAYS'
         WHEN (CURRENT_DATE - CAST(b.CREAT_TMSTMP AS DATE)) BETWEEN 61 AND 90 THEN '61 - 90 DAYS'
         ELSE '90 + DAYS'
       END
       AS AGE_BY_BUCKET
      ,  (CURRENT_DATE - CAST(b.CREAT_TMSTMP AS DATE)) AS DAYS_OUT
      , d.DOMN_NM AS CATEGORY
      , b.WKFLW_STS_ID AS "TYPE"
      , a.INCIDENT_ID
      , e.TRM_RSK_IND_NM_1 AS RISK_REASON
      , c.WKFLW_STS_NM AS STAT_DISC_EN
      , CASE
                         WHEN g.ORG_UNT_CD IS NULL THEN 'UNKNOWN TERRITORY'
                         ELSE g.ORG_UNT_CD    
                         END AS TERRITORY
      ,  CASE TEAM  
         WHEN  'TEAM001' THEN 'WARNINGS'
         WHEN  'TEAM002' THEN 'PROBE'
         WHEN  'TEAM003' THEN 'GOVERNANCE'
         WHEN  'TEAM004' THEN 'BANKING'
         WHEN  'TEAM005' THEN 'MSS'
         WHEN  'TEAM006' THEN 'HRT'
            WHEN  'TEAM007' THEN 'HRT GOVERNANCE'
            WHEN  'TEAM008' THEN 'HRT LEGAL'
            WHEN  'TEAM009' THEN 'HRT ACQUISITION'
           WHEN  'TEAM010' THEN 'HRT RETENTION'
            WHEN  'TEAM011' THEN 'HRT HV'
            WHEN  'TEAM012' THEN 'STEERING COMMITTEE'
            WHEN  'TEAM013' THEN 'ACCT CLOSURES'
            WHEN  'TEAM014' THEN 'CLIENT EVALUATIONS'
            WHEN  'TEAM015' THEN 'HRC HV COMMITTEE'
            WHEN  'TEAM016' THEN 'OTHER BANKING'
            WHEN  'TEAM017' THEN 'OTHER CAPITAL MARKET'
            WHEN  'TEAM018' THEN 'WORLD WEALTH MANAGEMENT'
            WHEN  'TEAM019' THEN 'US WM'
            WHEN  'TEAM020' THEN 'SURVEILLANCE'
            WHEN  'TEAM021' THEN 'INSURANCE'
            WHEN  'TEAM022' THEN 'SPECIAL UNIT'
            WHEN  'TEAM023' THEN 'COMPLIANCE'
         END
          AS INCIDENT_TEAM
       ,  f.ANLYST_FRST_NM AS FIRST_NAME
       , f.ANLYST_LST_NM AS LAST_NAME
     , MAX((CURRENT_TIMESTAMP (FORMAT 'MM/DD/YYYY HH:Mi:SS')(CHAR(20)))) AS RUN_DATE
     
FROM
(
     SELECT
                       SCHEMA.ACM_WARNING_HDR_DLY.WARNING_id AS INCIDENT_ID
                      ,SCHEMA.ACM_WARNING_HDR_DLY.WARNING_KEY
                      ,SCHEMA.ACM_WARNING_HDR_DLY.FIU_TEAM_NM AS TEAM
                   
        FROM
                          SCHEMA.ACM_WARNING_HDR_DLY
        WHERE  SCHEMA.ACM_WARNING_HDR_DLY.GRIP_DT = CURRENT_DATE - 2   
             
     UNION
     
     SELECT
                       SCHEMA.ACM_CSE_DLY.CSE_ID AS INCIDENT_ID
                      ,SCHEMA.ACM_CSE_DLY.CSE_KEY
                      ,SCHEMA.ACM_CSE_DLY.TEAM_NM AS TEAM
     WHERE  SCHEMA.ACM_CSE_DLY.GRIP_DT = CURRENT_DATE-7               
) AS a

INNER
JOIN           

     SCHEMA.ACM_WKFLW_WORKITEM_DLY b
     ON
     a.WARNING_key=b.enty_key
INNER
JOIN    
 
(
                     SELECT WKFLW_STS_ID, WKFLW_STS_CD, WKFLW_STS_NM, ACTV_IND, MAX(GRIP_DT) AS LATEST_UPDATE
                     FROM SCHEMA.ACM_WKFLW_STS_DLY
                     GROUP BY WKFLW_STS_ID, WKFLW_STS_CD, WKFLW_STS_NM, ACTV_IND
) AS c
     ON
     b. wkflw_sts_id = c.wkflw_sts_id
INNER
JOIN

(
                        SELECT DOMN_ID, DOMN_NM,DOMN_CD,MAX(GRIP_DT) AS LATEST_DATE
                        FROM SCHEMA.ACM_DOMN_DLY
                        HAVING DOMN_CD LIKE'%AML%' AND DOMN_NM NOT LIKE '%test%'  
                        GROUP BY  DOMN_ID, DOMN_NM,DOMN_CD
) AS  d
         ON
      d.domn_id = b.domn_id

LEFT
OUTER
JOIN         

(
                        SELECT e1.WARNING_KEY, e1.TRM_RSK_IND_NM_1
                        FROM SCHEMA.ACM_WARNING_DTL_EDD_DLY e1
                        QUALIFY ROW_NUMBER() OVER(PARTITION BY e1.WARNING_KEY ORDER BY e1.WARNING_KEY)=1 
) AS e
      ON
      e.WARNING_key=a.WARNING_key 

LEFT
OUTER
JOIN
      
      SCHEMA.ACM_ANLYST_DLY f
      ON
      f.ANLYSTid = CAST(b.creat_by_ANLYSTid AS DECIMAL (38,0))
LEFT
OUTER
JOIN
                        SCHEMA.ACM_ORG_UNT_DLY g
                        ON
         g.ORG_UNT_ID = b.ORG_UNT_ID

WHERE
     (
     c.WKFLW_STS_CD IN ('S_AML_AL_001','S_EIM_AL_001','S_EIM_CA_001','S_EIM_CA_004')
AND
                      INCIDENT_TEAM IN ('PROBE')
                                                          
                    )                       
GROUP BY
                        AGE_BY_BUCKET
                       , DAYS_OUT
                       , CATEGORY
                       ,"TYPE"
                       , INCIDENT_ID
                       , RISK_REASON
                       , STAT_DISC_EN
                       , TERRITORY
                       , INCIDENT_TEAM
                       , FIRST_NAME
                       , LAST_NAME

                      
3 REPLIES
Senior Apprentice

Re: Finding the latest date

I don't fully understand what you want, but it seems like you might utilize OLAP functions.

QUALIFY RANK() OVER (PARTITION BY ??? ORDER BY GRIP_DT DESC) = 1

or

QUALIFY GRIP_DT = MAX(GRIP_DT) OVER (PARTITION BY ???)
Enthusiast

Re: Finding the latest date

Thanks Dieter for the response.  What I am trying to do is query each table so that I may return the latest or most up to date data.   Each night the tables are updated and each record plus any additional records will need to be queried.  Rather than hard code by using a HAVING clause (HAVING MAX(Grip_Dt)=current_date-1) I was looking for a better and more accurate way to find the latest record -- remembering that it is not a guarantee that updates would have taken place (ie.  it could current_date-2, or current_date-3).   I now it  sounds complicated but this is the situation I have been dealt.

Senior Apprentice

Re: Finding the latest date

Then it's a perfect match for those OLAP functions :-)