Query Help - SQL Server Forums

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)

Query Help

Author

Topic

hisouka
Starting Member

28 Posts

Posted - 2009-05-18 : 05:22:09

Hi, i want to make a function or procedure with this scenario:

EmployeesTable
ID, Name, Age

EstimateTable
EstimateID, EmployeeID, Date, EstimateType

I want to select All Employee with their specific estimate.
Each employee have Multiple Entry in Estimatetable
I only want one to return ID, Name, Age, and only one EstimateType.

Criteria for EstimateType:
Most Recent Estimate of Employee
If Date is null it must Return EstimateType=ContractPrice which is you can only add one ContractPrice Type.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-18 : 05:27:41

[code]
select e.ID, e.Name, e.Age, e.EstimateType
from
(
select e.ID, e.Name, e.Age,
EstimateType = case when s.[Date] is null
then 'ContractPrice'
else s.EstimateType
end,
row_no = row_number() over (partition by e.ID order by s.[Date] desc)
from EmployeesTable e
inner join EstimateTable s on e.ID = s.EmployeeID
) e
where e.row_no = 1
[/code]

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

hisouka
Starting Member

28 Posts

Posted - 2009-05-18 : 05:32:19

quote:

Originally posted by khtan


select e.ID, e.Name, e.Age, e.EstimateType
from
(
    select e.ID, e.Name, e.Age, 
           EstimateType = case when s.[Date] is null
                               then 'ContractPrice'
                               else s.EstimateType
                               end, 
           row_no = row_number() over (partition by e.ID order by s.[Date] desc)
    from   EmployeesTable e
           inner join EstimateTable s on e.ID = s.EmployeeID
) e
where e.row_no = 1