SQL Server Performance Forum – Threads Archive
how to return max date from these date?
Hi Alli have these tables in one DB: A, B, C, D, Demo. they all have a column called last_update_dt. the query need to return the last update date from these max(last_update_dt). is there a function in SQL server that can put all these data in it and return the maximum value. New_dt= function_name( Max(A.last_update_dt) , Max(B.last_update_dt ), max(C.last_update_dt ), max(D_update_dt ), max(Demo.last_update_dt ) ) thanks in advance for any idea.
select max(last_update_dt)
from
(
select last_update_dt = max(last_update_dt) from A
union all
select last_update_dt = max(last_update_dt) from B
union all
select last_update_dt = max(last_update_dt) from C
union all
select last_update_dt = max(last_update_dt) from D
union all
select last_update_dt = max(last_update_dt) from DEMO
) t KH
thanks for quit reply. but my query look more complicated. i try to modify the coding in
CASE
when max(A.last_update_dt) >(Demo.last_update_dt) THEN max(A.last_update_dt)
when max(B.last_update_dt) > (Demo.last_update_dt) THEN max(B.last_update_dt)
when max(C.last_update_dt) >(Demo.last_update_dt) THEN max(C.last_update_dt)
when max(D.last_update_dt) > (Demo.last_update_dt) THEN max(D.last_update_dt)
Else (Demo.last_update_dt)
End i need this section to return the maximum last_update_dt from Max(A.last_update_dt) , Max(B.last_update_dt ), max(C.last_update_dt ), max(D_update_dt ), Demo.last_update_dt )
—————————original query——————— SELECT
DISTINCT
Demo.Recip_ID,
Demo.Prov_Num as DM_Prov_Num,
‘ ‘,
Demo.Certified_Dt,
Demo.Recert_Dt,
(CASE
WHEN D.Screen_Claims_Flag = ‘Y’ THEN ‘Y’
ELSE ‘N’
END) as Breast_Screen_Flag,
(CASE
WHEN C.Screen_Claims_Flag = ‘Y’ THEN ‘Y’
ELSE ‘N’
END) as Cervical_Screen_Flag,
‘ ‘,
‘ ‘,
(CASE
WHEN B.Followup_Claims_Flag = ‘Y’ THEN ‘Y’
ELSE ‘N’
END) as Breast_Followup_Flag,
(CASE
WHEN A.FollowUp_Claims_Flag = ‘Y’ THEN ‘Y’
ELSE ‘N’
END) as Cervical_Followup_Flag,
‘ ‘,
‘ ‘,
Demo.Recip_Last_Name,
Demo.Recip_First_Name,
Demo.Recip_Middle_Init,
CASE
when max(A.last_update_dt) >(Demo.last_update_dt) THEN max(A.last_update_dt)
when max(B.last_update_dt) > (Demo.last_update_dt) THEN max(B.last_update_dt)
when max(C.last_update_dt) >(Demo.last_update_dt) THEN max(C.last_update_dt)
when max(D.last_update_dt) > (Demo.last_update_dt) THEN max(D.last_update_dt)
Else (Demo.last_update_dt)
End
FROM Demo LEFT OUTER JOIN
B ON (Demo.Recip_ID = B.Recip_ID AND Demo.Prov_Num = B.Prov_Num) LEFT OUTER JOIN
D ON (Demo.Recip_ID = D.Recip_ID AND Demo.Prov_Num =D.Prov_Num) LEFT OUTER JOIN
A ON (Demo.Recip_ID = A.Recip_ID AND Demo.Prov_Num = A.Prov_Num) LEFT OUTER JOIN
C ON (Demo.Recip_ID =C.Recip_ID AND Demo.Prov_Num = C.Prov_Num)
WHERE
Demo.Certified_Dt IS NOT NULL and
Demo.Recert_Dt IS NOT NULL
and Demo.recip_ID =’019A0106962943′
group BY Demo.Recip_ID,
Demo.Prov_Num,
Demo.Certified_Dt,
Demo.Recert_Dt,
D.Screen_Claims_Flag,
C.Screen_Claims_Flag,
B.Followup_Claims_Flag,
A.FollowUp_Claims_Flag,
Demo.Recip_Last_Name,
Demo.Recip_First_Name,
Demo.Recip_Middle_Init,
Demo.last_update_dt
Use UNION, each query joining the DEMO table with the A, B, C or D table, with the MAX aggregate date, and with constant expressions for the flags (‘Y’ and ‘N’).
Not sure if I understand you correctly, but this might give you some ideas:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9193 —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
]]>