How To Reference A Previous Record

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

General SQL Server Forums

New to SQL Server Programming

How To Reference A Previous Record

Author

Topic

wiu81
Starting Member

10 Posts

Posted - 2015-03-31 : 13:40:17

Hello,

I have a query that shows me a list of what employees that are on site assuming that employee badged in correctly. My problem is I need to know when an employee has two entries in a row that are "In" without have an "Out" entry.

For example, if John badges in at 8:00 Am and leaves without badging out, when he arrives the next day at 8:00 AM and badges in the system simply would show him as in with no record of him ever leaving correctly.

I need to gauge the size of this problem, but I am not sure how to return the correct result. Can I formulate a query that would display a Who's In list where the previous entry was NOT and Out?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-31 : 14:04:47

Are you on SQL 2012 or later? If so it is easier and faster to do it using windowing functions. If you are on an older version:

SELECT
	e.*,o.*
FROM
	YourTable o
	CROSS APPLY
	(
		SELECT TOP (1) *
		FROM
			YourTable e
		WHERE 
			o.EmployeeId = e.EmployeeId
			AND e.Datestamp < o.Datestamp
		ORDER BY
			e.Datestamp DESC
	) o
WHERE
	o.BadgeType = 'OUT'
	AND e.BadgeType <> 'IN';

Since I have no idea about what columns your table has or what kind of data it has, this is a guess. Posting DDL for tables with sample data usually can get you better answers.

wiu81
Starting Member

10 Posts

Posted - 2015-03-31 : 14:15:35

I will need to do some research on the CROSS APPLY function. I am using SQL SERVER 2008R2.

Here is my current query that shows a log of Ins "IN" and Outs "O".

SELECT TOP 1000 [Name]
,[TimeDate]
,[Loc]
,[Dev]
,[TNA]
,[Company]
,[ID]
,[Code]
,[TempLevel]
,[Department]
,[LName]
,[FName]
,[X_EntryTime]
,[InDevice]
,[OutDevice]
,[LaborRate]
FROM [Acsdata].[dbo].[x_temptrack]
Order By TimeDate DESC

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-31 : 14:29:04

Please show us some sample data.

wiu81
Starting Member

10 Posts

Posted - 2015-03-31 : 15:11:15

SELECT TOP 1000 [Name]
,[TimeDate]
,[TNA]
,[Code]
,[LName]
,[FName]
,[X_EntryTime]
,[InDevice]
,[OutDevice]

FROM [Acsdata].[dbo].[x_temptrack]
Order By TimeDate DESC

Name TimeDate TNA Code LName FName X_EntryTime InDevice OutDevice
ExitTurnstile 2015-03-31 15:02:38.000 O 33575 D01 Fruit Receiving 3 2015-03-31 06:37:39.000 Entry Turnstile Exit Turnstile

Exit Turnstile 2015-03-31 15:02:35.000 O 33570 N01 Fruit Receiving 3 2015-03-31 06:37:36.000 Entry Turnstile Exit Turnstile

Exit Turnstile 2015-03-31 15:02:26.000 O 33236 N09 Quality 3 2015-03-31 06:35:10.000 Entry Turnstile Exit Turnstile

Exit Turnstile 2015-03-31 15:01:51.000 O 33080 D02 Maintenance 2 2015-03-31 09:22:44.000 Entry Turnstile Exit Turnstile

Does this help? Sorry for the alignment issues

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-31 : 15:25:27

Looks like your sample data "only" shows records with both In and Out.
Can you show samples of In records without Out?

wiu81
Starting Member

10 Posts

Posted - 2015-03-31 : 15:29:38

That's really my problem. I dont have that record. I have the query from the previous post that is a log of IN and OUT and I have a query for who is currently IN. I currently do not have a way to account for someone who has consecutive IN entries.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-31 : 15:36:21

Ok - going out on a limb here and guessing that you might find your "In only" records either by:

where rtrim(isnull(OutDevice,''))=''

or by:

where cast(timedate as date)>cast(x_entrytime as date)

wiu81
Starting Member

10 Posts

Posted - 2015-03-31 : 15:36:37

This should help some. Here is an example.... This is using a different table and perhaps the table I should be using to answer my question.

SELECT TOP 1000 [TimeDate]
,[Loc]
,[Event]
,[Dev]
,[IO]
,[IOName]
,[Code]
,[LName]
,[FName]
,[Opr]
,[Ws]
,[xChecked]
,[X_EntryTime]
FROM [Acslog].[dbo].[EvnLog]
Where FName = 'Processing 1' and LName = 'D16'
ORDER BY TimeDate DESC

TimeDate Loc Event Dev IO IOName Code LName FName Opr Ws xChecked X_EntryTime
2014-06-03 14:24:49.000 6 14 2 16 Entry Turnstile 33103 D16 Processing 1 4462 NULL NULL

TimeDate Loc Event Dev IO IOName Code LName FName Opr Ws xChecked X_EntryTime
2014-06-01 14:20:15.000 6 8 2 16 Entry Turnstile 33103 D16 Processing 1 4462 NULL NULL

This is an example of back to back Entry results.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-31 : 15:51:41

Looks like the IO is a code for the type of entry, and 16 = "In records". Which code represent "Out records"?

wiu81
Starting Member

10 Posts

Posted - 2015-03-31 : 15:53:37

Ideally I would like to query the entire EvnLog for entries with consecutive Entry post.

wiu81
Starting Member

10 Posts

Posted - 2015-03-31 : 15:55:54

I believe the 16 is the number assigned to the Input Output unit. In this case its the turnstile. I also believe the DEV of 2 is an entry and 3 is an exit.

TimeDate Loc Event Dev IO IOName Code LName FName Opr Ws xChecked X_EntryTime
2014-08-25 22:41:47.000 6 8 2 16 Entry Turnstile 33103 D16 Processing 1 4462 NULL NULL

TimeDate Loc Event Dev IO IOName Code LName FName Opr Ws xChecked X_EntryTime
2014-08-25 15:04:01.000 6 8 3 16 Exit Turnstile 33103 D16 Processing 1 4462 NULL 2014-08-25 08:24:27.000

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-31 : 16:01:33

Try this:

with cte
  as (select *
            ,row_number() over(partition by fname,lname order by timedate) as rn
        from acslog.dbo.evnlog
     )
select a.*
  from cte as a
       inner join cte as b
               on b.lname=a.lname
              and b.fname=a.fname
              and b.rn=a.rn+1
 where a.dev<>3
   and b.dev=2

wiu81
Starting Member

10 Posts

Posted - 2015-03-31 : 16:25:15

Here are some examples of the results. I need to be able to narrow this down some more but im not sure how to manipulate your query. For example what would need to be changed to show only "Loc" 6, "IO" 16 and results from this month only?

TimeDate Loc Event Dev IO IOName Code LName FName Opr Ws xChecked X_EntryTime rn
2004-05-12 14:34:10.000 5 10 -1 0 Delaware Tank Farm 0 NULL NULL 267

2004-05-12 14:37:26.000 5 10 -1 0 Delaware Tank Farm 0 NULL NULL 277

2004-05-12 14:49:16.000 5 10 -1 0 Delaware Tank Farm 0 NULL NULL 287

2004-05-12 15:03:10.000 5 104 7 0 Rear Office Entry 0 NULL NULL 303

Thank you for all your help to this point. Its greatly appreciated. Im learning many things on the fly.

wiu81
Starting Member

10 Posts

Posted - 2015-03-31 : 16:36:21

Sorry im not familiar with Common Table Expressions. Im reading up on that now.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-31 : 16:36:42

[code]with cte
as (select *
,row_number() over(partition by fname,lname order by timedate) as rn
from acslog.dbo.evnlog
where loc=5
and io=16
and timedate>=cast('20150301' as datetime)
)
select a.*
from cte as a
inner join cte as b
on b.lname=a.lname
and b.fname=a.fname
and b.rn=a.rn+1
where a.dev<>3
and b.dev=2[/code]

wiu81
Starting Member

10 Posts

Posted - 2015-03-31 : 16:57:21

Ok I think I have it!!!

With your query I was able to make a couple other changes and received the following result.

with cte
as (select *
,row_number() over(partition by fname,lname order by timedate) as rn
from acslog.dbo.evnlog
where loc=6
and LName Like 'd%'
and timedate>=cast('20150301' as datetime)
)
select a.*
from cte as a
inner join cte as b
on b.lname=a.lname
and b.fname=a.fname
and b.rn=a.rn+1
where a.dev<>3
and b.dev=2
Order By LName

2015-03-14 23:02:45.000 6 8 2 16 Entry Turnstile 33575 D01 Fruit Receiving 3 4600 NULL NULL 25

2015-03-10 06:28:27.000 6 8 2 16 Entry Turnstile 33058 D01 Processing 2 4477 NULL NULL 1

I then went back to the Evnlog to verify the transactions.

SELECT TOP 1000 [TimeDate]
,[Loc]
,[Event]
,[Dev]
,[IO]
,[IOName]
,[Code]
,[LName]
,[FName]
,[Opr]
,[Ws]
,[xChecked]
,[X_EntryTime]
FROM [Acslog].[dbo].[EvnLog]
Where FName='Fruit Receiving 3' and LName='D01'
Order by TimeDate DESC

TimeDate Loc Event Dev IO IOName Code LName FName Opr Ws xChecked X_EntryTime
2015-03-14 23:03:07.000 6 14 2 16 Entry Turnstile 33575 D01 Fruit Receiving 3 4600 NULL NULL

TimeDate Loc Event Dev IO IOName Code LName FName Opr Ws xChecked X_EntryTime
2015-03-14 23:02:45.000 6 8 2 16 Entry Turnstile 33575 D01 Fruit Receiving 3 4600 NULL NULL

I was able to find the duplicate Entry post on 3-14.

Thank you x1000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources