Getting mulitple records but only one wanted 1

[patrussell]

I have a query that is supposed to pull all employees that have clocked in for the past 16 hours but have not clocked out. We are using this for a head count should and emergency occur. The problem I am having is that with the query below I search on the clock in time and look for a null in the clock out time. This works great unless the employee double swiped their time card. In that intance I get two clock in times a few seconds apart but only the latest in time will get the clock out time later. This leaves an active clock in time with a null clock out for an employee that is not on site. Obviously this is bad for what we want to do with this query.

Here is the query

SELECT DISTINCT attendance.laborcode,
				labor.name,
				MAX(attendance.startdate),
				attendance.finishdate
FROM	[db1].maximo3.dbo.attendance AS attendance 
			INNER JOIN
				[db1].maximo3.dbo.labor AS labor 
					ON attendance.laborcode = labor.laborcode
WHERE	(attendance.startdate >= DATEADD(hh, - 16, CURRENT_TIMESTAMP)) 
		AND (labor.la2 = 'lou') 
GROUP BY attendance.laborcode, labor.name,attendance.finishdate
ORDER BY labor.name

And the results set from that query.

laborcode name (No column name) finishdate
47077 Person 1 4/8/2010 22:32:50 NULL
47077 Person 1 4/8/2010 22:32:54 4/9/2010 8:18:07
46843 Person 2 4/9/2010 6:56:47 NULL
46894 Person 3 4/9/2010 7:22:10 NULL
46805 Person 4 4/8/2010 23:13:42 NULL
47025 Person 5 4/9/2010 7:10:12 NULL
46938 Person 6 4/8/2010 22:56:05 4/9/2010 7:30:35
46938 Person 6 4/8/2010 18:54:13 4/8/2010 19:24:03
46952 Person 7 4/9/2010 3:19:48 NULL
46789 Person 8 4/8/2010 18:40:14 NULL
46789 Person 8 4/8/2010 18:40:17 4/9/2010 3:30:18

The records in bold are examples of the "false" result.

Any help in resolving this is greatly appreciated.




Pat Russell

 

[gmmastros]

What version of SQL Server are you using? SQL 2000, 2005, 2008?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

you can use some sort of buffer to first only get the max start date for each employee, and then only get the finish date for that employee, this way, you essentially eliminate any double clock ins by only looking at the last clock in.

the buffer can be a temp table, derived table, cte or whatever.

--------------------
Procrastinate Now!

 

[patrussell]

Sorry George...I'm using SQL2008.

I thought about using the temp table but was not quite sure how to set up the query to do that. I tinker with SQL but am by no means very proficient.


Pat Russell

 

[gmmastros]

Pat,

There are likely to be many ways to accomplish this query. My thinking was along the same lines as Crowley. To only consider the last (most recent) startdate.

In this case, you could use the Row_Number function to get the last start date. Something like this...

Select  LaborCode, StartDate, EndDate,
		Row_Number() Over(Partition By LaborCode Order By StartDate DESC ) As TheRow
From    Attendance
Where   Attendance.StartDate > DateAdd(Hour, -16, CURRENT_TIMESTAMP)

When you run the query above, you will see the relevant columns from the attendance table. When there are multiple rows within the last 16 hours, you will notice that they are numbered with the most recent always being 1. We can use this to our advantage by next linking with the other table.

Something like this:

SELECT DISTINCT attendance.laborcode,
                labor.name,
                attendance.startdate,
                attendance.finishdate
FROM    [!](
        Select  LaborCode, StartDate, EndDate,
        		Row_Number() Over(Partition By LaborCode Order By StartDate DESC ) As TheRow
        From	[db1].maximo3.dbo.attendance As Attendance
        Where   Attendance.StartDate > DateAdd(Hour, -16, CURRENT_TIMESTAMP)
        ) AS attendance[/!]
            INNER JOIN
                [db1].maximo3.dbo.labor AS labor
                    ON attendance.laborcode = labor.laborcode
WHERE   [!]Attendance.TheRow = 1[/!]
        And (labor.la2 = 'lou')
ORDER BY labor.name

Notice that I removed the MAX aggregate from the start date and I removed the group by too. I suspect you can remove the distinct clause too. You probably don't need it, and if it's not needed, it will only cause the query to execute slower.

Lastly, there may be some mistakes in the code because I don't have access to your tables. Hopefully the concept and the explanation I gave will compensate for any actual mistakes in the code.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

since you're using 2008, I'd take advantage of cte's, so something like:

;with s as
(
select person, max(StDate) as StDate from table
)
select
s.StDate
, t.FinishDate
from
s inner join
table t on t.person = s.person

obviously you have to modify this to use the correct tables/links and stuff...

--------------------
Procrastinate Now!

 

[markros]

Take a look at these two links explaining this problem in details
Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2

PluralSight Learning Library

 

[patrussell]

George,

I tried the query you suggested but am getting an incorrect syntax error near the keyword 'Over'. I checked the online help for this function but cannot see anything wrong with the syntax.

Here is what I have:

SELECT DISTINCT attendance.laborcode,
                labor.name,
                attendance.startdate,
                attendance.finishdate
FROM    (        
			Select  attendance.LaborCode, 
					attendance.StartDate, 
					attendance.EndDate,
					attendance.Row_Number() Over(Partition By attendance.LaborCode Order By attendance.StartDate DESC ) As TheRow
			From    [db1].maximo3.dbo.attendance As Attendance
			Where   Attendance.StartDate > DateAdd(Hour, -16, CURRENT_TIMESTAMP)        ) 
			AS attendance
	INNER JOIN
		[db1].maximo3.dbo.labor AS labor
			ON attendance.laborcode = labor.laborcode
WHERE   Attendance.TheRow = 1
        And (labor.la2 = 'lou')
ORDER BY labor.name

The database I am writing the query in is SQL2008, the database I am pulling from is probably SQL2000 or SQL2005. The source database is not one I have control over. Does this make any difference?

Thanks again for the help...

Pat Russell

 

[patrussell]

I just verified that the source database is SQL2000.

Sorry for the confusion if this messes things up.

Pat Russell

 

[gmmastros]

Sorry for the confusion if this messes things up.

It does kinda mess things up. Windowing functions (like row_number) were introduced in SQL2005. For this query to work the way it should, we'll need to mimic the functionality of the Row_Number function in SQL2000. It isn't terribly difficult, but it's not as easy or convenient as it is in sql2005/8.

Something like this...

Set NoCount ON

Declare @Temp Table(RowId Int Identity(1,1), LaborCode Int, StartDate DateTime, EndDate DateTime)

Insert
Into    @Temp(LaborCode, StartDate, EndDate)
Select  LaborCode, StartDate, EndDate
From    [db1].maximo3.dbo.attendance As Attendance
Where   StartDate > DateAdd(Hour, -16, CURRENT_TIMESTAMP)
Order By LaborCode, StartDate

SELECT DISTINCT attendance.laborcode,
                labor.name,
                attendance.startdate,
                attendance.finishdate
FROM    (        
           Select  T.LaborCode, T.StartDate, T.EndDate
           From	@Temp T
		           Inner Join (
			           Select	LaborCode, Max(RowId)  As MaxRowId
			           From	@Temp
			           Group By LaborCode
			           ) As A
			           On T.LaborCode = A.LaborCode
           Where	A.MaxRowId = T.RowId 
        )
            AS attendance
    INNER JOIN
        [db1].maximo3.dbo.labor AS labor
            ON attendance.laborcode = labor.laborcode
WHERE   (labor.la2 = 'lou')
ORDER BY labor.name

Notice this time we are using a temp table with an identity column. There are various hoops that we jump through just to get the last row with all of its related data.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Please take a moment to read the suggested links. They present SQL Server 2000 solutions as well as SQL Server 2005+ solutions.

PluralSight Learning Library

 

[patrussell]

George,

With a few modifications specific to my set up the query you gave worked great! Thank you.

Markros,

I did look at the links you provided and there is definately some good information there but it will take me some time to digest it. Right now the quicker solution for me (and the easiest to understand) was the one provided by George.

Thank you all for the help.

Pat Russell