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?
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'
, (CURRENT_DATE - CAST(b.CREAT_TMSTMP AS DATE)) AS DAYS_OUT
, d.DOMN_NM AS CATEGORY
, b.WKFLW_STS_ID AS "TYPE"
, e.TRM_RSK_IND_NM_1 AS RISK_REASON
, c.WKFLW_STS_NM AS STAT_DISC_EN
WHEN g.ORG_UNT_CD IS NULL THEN 'UNKNOWN TERRITORY'
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'
, 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
SCHEMA.ACM_WARNING_HDR_DLY.WARNING_id AS INCIDENT_ID
,SCHEMA.ACM_WARNING_HDR_DLY.FIU_TEAM_NM AS TEAM
WHERE SCHEMA.ACM_WARNING_HDR_DLY.GRIP_DT = CURRENT_DATE - 2
SCHEMA.ACM_CSE_DLY.CSE_ID AS INCIDENT_ID
,SCHEMA.ACM_CSE_DLY.TEAM_NM AS TEAM
WHERE SCHEMA.ACM_CSE_DLY.GRIP_DT = CURRENT_DATE-7
) AS a
SELECT WKFLW_STS_ID, WKFLW_STS_CD, WKFLW_STS_NM, ACTV_IND, MAX(GRIP_DT) AS LATEST_UPDATE
GROUP BY WKFLW_STS_ID, WKFLW_STS_CD, WKFLW_STS_NM, ACTV_IND
) AS c
b. wkflw_sts_id = c.wkflw_sts_id
SELECT DOMN_ID, DOMN_NM,DOMN_CD,MAX(GRIP_DT) AS LATEST_DATE
HAVING DOMN_CD LIKE'%AML%' AND DOMN_NM NOT LIKE '%test%'
GROUP BY DOMN_ID, DOMN_NM,DOMN_CD
) AS d
d.domn_id = b.domn_id
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
f.ANLYSTid = CAST(b.creat_by_ANLYSTid AS DECIMAL (38,0))
g.ORG_UNT_ID = b.ORG_UNT_ID
c.WKFLW_STS_CD IN ('S_AML_AL_001','S_EIM_AL_001','S_EIM_CA_001','S_EIM_CA_004')
INCIDENT_TEAM IN ('PROBE')
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
QUALIFY GRIP_DT = MAX(GRIP_DT) OVER (PARTITION BY ???)
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.