How to get Max date from Union Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to get Max date from Union Query

Author

Topic

khufiamalik
Posting Yak Master

120 Posts

Posted - 2008-08-19 : 06:13:19

Hello All,

select Basic_Pay*60/100 from
(
select Emp_Code,Basic_Pay,[Start_Date],End_Date,Fiscal_Year_ID
from EMP_Salary
union
select Emp_Code,Basic_Pay,[Start_Date],End_Date,Fiscal_Year_ID from EMP_Salary_History
) as Table2
where Emp_Code='Sys_EMP1' and Fiscal_Year_ID=1
and [Start_Date]=(select MAX([Start_Date]) from Table2)

I want to check the maximum date selected by Union Query.
Can any one help me?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-19 : 06:16:29

[code];WITH Table2 (Emp_Code, Basic_Pay, Start_Date, End_Date, Fiscal_Year_ID)
(
SELECT Emp_Code,Basic_Pay,[Start_Date],End_Date,Fiscal_Year_ID
FROM EMP_Salary
UNION
SELECT Emp_Code,Basic_Pay,[Start_Date],End_Date,Fiscal_Year_ID FROM EMP_Salary_History
) AS Table2
SELECT Basic_Pay*60/100
FROM Table2
WHERE Emp_Code='Sys_EMP1' AND Fiscal_Year_ID=1
AND [Start_Date]=(SELECT MAX([Start_Date]) FROM Table2)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 06:17:36

Use a CTE :-

;
With CTE(Emp_Code,Basic_Pay,[Start_Date],End_Date,Fiscal_Year_ID)AS
(
select Emp_Code,Basic_Pay,[Start_Date],End_Date,Fiscal_Year_ID 
from EMP_Salary
union
select Emp_Code,Basic_Pay,[Start_Date],End_Date,Fiscal_Year_ID from EMP_Salary_History

)
select  Basic_Pay*60/100 from CTE
where Emp_Code='Sys_EMP1' and Fiscal_Year_ID=1 
and [Start_Date]=(select MAX([Start_Date]) from CTE)