Hey guys,
I am having trouble getting my query to select the max date for a specific record value. Here is an example of the table
NAME AGENCY HISTORY DATE
TEST 00922 2014-02-12
TEST 90212 2012-02-12
I want my query to return the last history date occurring only on agencies that start with 9. So in my example table, the date should be 2012-02-12.
But this query fails:
Wouldn't SQL evaluate both conditions? I get blank records when I run it so my logic must be way off.
I am having trouble getting my query to select the max date for a specific record value. Here is an example of the table
NAME AGENCY HISTORY DATE
TEST 00922 2014-02-12
TEST 90212 2012-02-12
I want my query to return the last history date occurring only on agencies that start with 9. So in my example table, the date should be 2012-02-12.
But this query fails:
Code:
SELECT A.MBR_SSN_NBR,
A.MBR_HIST_SVC_CR_DT
FROM DSNP.PR01_T_MBR_HIST A
where left(A.agty_id_cd, 1) = '9'
and A.MBR_HIST_SVC_CR_DT = (select max(c.MBR_HIST_SVC_CR_DT) from dsnp.pr01_t_mbr_hist c where c.mbr_ssn_nbr = A.mbr_ssn_nbr)
Wouldn't SQL evaluate both conditions? I get blank records when I run it so my logic must be way off.