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.
Go to Top of Page

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
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-31 : 14:29:04
Please show us some sample data.
Go to Top of Page

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




Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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)
Go to Top of Page

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.
Go to Top of Page

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"?
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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]
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -