Complex Query - 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)

Complex Query

Author

Topic

qjam
Starting Member

19 Posts

Posted - 2007-08-23 : 03:35:18

Hi all,

I have a complex query which I can't seem to get working. The query needs to compare one database to another.

The database I'm running this against stores the current normal billable hours per employee, and the second database stores a history of any changes to the normal hours. For example, the tables in the individual databases would look like:

Database1.dbo.Employee
EmpCode, NormalHours
101, 35
303, 23

Database2.dbo.Hist
EmployeeNum, DateChanged, NormHrs
101, 10/02/2001, 22
101, 12/05/2003, 65
101, 01/01/2007, 42
303, 05/02/2007, 23

What I am attempting to do is list all the current normal hours in Database1 which have changed since the last record of the normal hours in Database2.

I have used this query in SQL Server:

SELECT TOP 100 PERCENT E.EmpCode, E.NormalHours, AP.*, getdate() AS TheDate
FROM dbo.Employee E LEFT OUTER JOIN
(SELECT P1.*
FROM Database2.dbo.Hist AS P1
WHERE P1.DateFrom IN
(SELECT TOP 1 P2.DateFrom
FROM Database2.dbo.Hist P2
WHERE P1.EmployeeNum = P2.EmployeeNum
ORDER BY P2.DateFrom DESC)) AP ON E.cEmpCode = AP.EmployeeNum
WHERE E.NormalHours <> AP.NormHrs
ORDER BY E.EmpCode

This in my head works, and it will join the Employee table with the last record for each employee in the Hist table. However, it seems to disregard the 'WHERE E.NormalHours <> AP.NormHrs' condition, and shows a line for every employee, regardless of if their normal hours have changed eg. even though the employee 303 has the same hours as their last record in the Hist table.

An example of the output would be:

E.EmpCode, E.NormalHours, EmployeeNum, DateChanged, NormHrs, TheDate
101, 35, 101, 01/01/2007, 42, 23/08/2007
303, 23, 101, 05/02/2007, 23, 23/08/2007

Is anyone able to help me? I feel like I'm beating my head against a brick wall

Cheers, qjam.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 03:52:47

[code]declare @employee table (empcode int, normalhours int)

insert @employee
select 101, 35 union all
select 303, 23

declare @hist table (employeenum int, datechanged smalldatetime, normhrs int)

insert @hist
select 101, '10/02/2001', 22 union all
select 101, '12/05/2003', 65 union all
select 101, '01/01/2007', 42 union all
select 303, '05/02/2007', 23

;with peso (employeenum, datechanged, normhrs, recid, thedate)
as (
select employeenum, datechanged, normhrs,
row_number() over (partition by employeenum order by datechanged desc),
dateadd(day, datediff(day, 0, current_timestamp), 0)
from @hist
)

select e.empcode,
e.normalhours,
p.employeenum,
p.datechanged,
p.normhrs,
p.thedate
from @employee as e
left join peso as p on p.employeenum = e.empcode
and e.normalhours <> p.normhrs
and p.recid = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-08-23 : 09:14:14

Hi,

Select e.empcode, e.normalhours, h2.Employeenum, h2.datechanged, h2.normhrs
From @employee e
Left join (Select Employeenum, max(datechanged) as datechanged
From @hist
Group By Employeenum) H1 on h1.Employeenum = e.empcode
Left join
@hist h2 on h1.datechanged = h2.datechanged and h1.Employeenum = h2.Employeenum
and e.normalhours <> h2.normhrs

qjam
Starting Member

19 Posts

Posted - 2007-08-23 : 20:14:54

Thanks PeterNeo and Peso. They both work great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources