need max date value | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

need max date value

Hi! I have a program I’m writing in PROIV where I’m accessing an oracle table. It’s been a while since I’ve used SQL so I need some guidance please. <br /><br />Here’s what I have…<br /><br />SELECT MAX(BIL_AUDIT_DATE) INTO :$LAST_CHANGE FROM BIL_BILLING_AUDIT_TBL WHERE<br />BIL_POLICY_NUM = <img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />OLICY.ID AND BIL_POLICY_EFF_DATE = TO_DATE:)$POL.EFF,’YYYYMMDD’)<br />AND (NEW_BILLEE_TYPE = ‘L’ OR NEW_BILLEE_TYPE = ‘ ‘); <br /><br />Here’s what I’m needing to obtain…. <br />I need to get the most recent bil_audit_date that meets my Where requirements. There will typically be only 1 or 2 entries that does this. I have tried the MAX command but am not getting any results where I know I should be.<br /><br />Any help is greatly appreciated!<br /><br />Thanks! <br /><br />Lana<br /><br /><br />
subquery that selects the max (date) select * from mytable where a=z and b=y and date = (select max(date) from mytable where a=z and b=y)
I’m not familiar with Oracle’s syntax, but that looks like you want to assign the MAX(date) to a variable. If that’s correct, you might want to use something like this:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @dt DATETIME<br />SELECT @dt = MAX(BIL_AUDIT_DATE) <br /> FROM BIL_BILLING_AUDIT_TBL <br /> WHERE BIL_POLICY_NUM = <img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />OLICY.ID <br /> AND BIL_POLICY_EFF_DATE = CAST(@POL.EFF AS DATETIME)<br /> AND (NEW_BILLEE_TYPE = ‘L’ OR NEW_BILLEE_TYPE = ‘ ‘); <br />&lt; do something else with @dt &gt;<br /></font id="code"></pre id="code"><br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=></a>