Join and date range - 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

Old Forums

CLOSED - General SQL Server

Join and date range

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-10 : 09:25:50

Ruth writes "I need to find the appropriate rate to apply to hours worked on the given date.

Input tables:

DateWork
Employee
NumHours
DateWorked

EmployeeRate
Employee
Rate
EffectiveDate

Sample data:

DateWork
Jane Doe 8 11/1/02
Jane Doe 8 12/1/02
Jane Doe 8 12/15/02

Employee Rate
Jane Doe $9 10/1/01
Jane Doe $10 10/1/02
Jane Doe $11 12/10/02

Desired output: (Name, datework, pay)

Jane Doe 11/1/02 $72
Jane Doe 12/1/02 $80
Jane Doe 12/15/02 $88

Thanks for your help."

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-10 : 18:27:08

Are you sure you want the lowest rate.

Should the results be 80, 80, 88.

Well here it is for the closest rate date before worked date.

SELECT A.EmpName AS EmpName, A.Hours AS Hours, A.WorkDate, A.RateDate AS RateDate, dbo.x_EmpRate.Rate AS Rate,
A.Hours * dbo.x_EmpRate.Rate AS Pay
FROM (SELECT dbo.x_DateWork.EmpName, dbo.x_DateWork.Hours, dbo.x_DateWork.[Date] AS WorkDate, MAX(dbo.x_EmpRate.[Date]) AS RateDate
FROM dbo.x_EmpRate INNER JOIN
dbo.x_DateWork ON dbo.x_EmpRate.[Date] < dbo.x_DateWork.[Date] AND dbo.x_EmpRate.EmpName = dbo.x_DateWork.EmpName
GROUP BY dbo.x_DateWork.EmpName, dbo.x_DateWork.Hours, dbo.x_DateWork.[Date]) A INNER JOIN
dbo.x_EmpRate ON A.EmpName COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.x_EmpRate.EmpName AND A.RateDate = dbo.x_EmpRate.[Date]

If you want it the other way then change the max to min.
I used different table names you'll have to modify those.

Edited by - ValterBorges on 10/10/2002 18:27:41

Edited by - ValterBorges on 10/10/2002 18:28:52

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources