SQL Assistance Please - 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 2008 Forums

Transact-SQL (2008)

SQL Assistance Please

Author

Topic

BBarn
Starting Member

14 Posts

Posted - 2014-06-12 : 12:32:16

SQL2k8r2:

I have some IN/OUT door data that I need to parse out for pairs of in/out and highlight missing swipes. Below is the data, you can see most of the INs have a cooresponding out, but not always.

I am trying to get the data to be something like this:

DATE In_Time Out_Time Missing Scan(Which one in/out)

I have tried to subtract, self-join and pivot to no avail. Any assistance would be greatly appreciated. If I missed something that will make it easier to explain my case, please let me know!

CNT|DATE_TIME|DIR|IN_DATE|xTime
1|Jun 4 2014 7:52AM|IN|6/4/2014|7:52
2|Jun 4 2014 8:30AM|OUT|6/4/2014|8:30
3|Jun 4 2014 8:36AM|IN|6/4/2014|8:36
4|Jun 4 2014 9:27AM|IN|6/4/2014|9:27
5|Jun 4 2014 9:27AM|OUT|6/4/2014|9:27
6|Jun 4 2014 9:29AM|IN|6/4/2014|9:29
7|Jun 4 2014 9:29AM|OUT|6/4/2014|9:29
8|Jun 4 2014 11:18AM|OUT|6/4/2014|11:18
9|Jun 4 2014 11:20AM|IN|6/4/2014|11:20
10|Jun 4 2014 11:21AM|OUT|6/4/2014|11:21
11|Jun 4 2014 11:21AM|IN|6/4/2014|11:21
12|Jun 4 2014 2:14PM|IN|6/4/2014|14:14
13|Jun 4 2014 2:14PM|OUT|6/4/2014|14:14
14|Jun 4 2014 2:16PM|IN|6/4/2014|14:16
15|Jun 4 2014 2:16PM|OUT|6/4/2014|14:16
16|Jun 4 2014 3:59PM|OUT|6/4/2014|15:59
17|Jun 4 2014 4:00PM|IN|6/4/2014|16:00
18|Jun 4 2014 4:00PM|OUT|6/4/2014|16:00
19|Jun 4 2014 4:28PM|IN|6/4/2014|16:28
20|Jun 4 2014 4:28PM|OUT|6/4/2014|16:28

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-12 : 13:39:20

Can you put your data in a consumable format (create table and insert) so we can run queries against it? Also, given the sample data, what do you want for output?

BBarn
Starting Member

14 Posts

Posted - 2014-06-12 : 14:32:19

This is actually a select statement from a 3rd party SQL table. I will give you the table "structure" that the query outputs rather than the whole table.

CREATE TABLE DoorActivity
(
DATE_TIME DATETIME,
DIR varchar(3),
IN_DATE DATETIME,
xTIME varchar(5)
)

Insert statement:
INSERT INTO DoorActivity VALUES ('Jun 4 2014 7:52AM','IN','6/4/2014','07:52')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 8:30AM','OUT','6/4/2014','08:30')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 8:36AM','IN','6/4/2014','08:36')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 9:27AM','IN','6/4/2014','09:27')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 9:27AM','OUT','6/4/2014','09:27')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 9:29AM','IN','6/4/2014','09:29')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 9:29AM','OUT','6/4/2014','09:29')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 11:18AM','OUT','6/4/2014','11:18')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 11:20AM','IN','6/4/2014','11:20')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 11:21AM','OUT','6/4/2014','11:21')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 11:21AM','IN','6/4/2014','11:21')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 2:14PM','IN','6/4/2014','14:14')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 2:14PM','OUT','6/4/2014','14:14')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 2:16PM','IN','6/4/2014','14:16')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 2:16PM','OUT','6/4/2014','14:16')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 3:59PM','OUT','6/4/2014','15:59')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 4:00PM','IN','6/4/2014','16:00')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 4:00PM','OUT','6/4/2014','16:00')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 4:28PM','IN','6/4/2014','16:28')
INSERT INTO DoorActivity VALUES ('Jun 4 2014 4:28PM','OUT','6/4/2014','16:28')

Desired Output:
DATE_TIME | In_time | Out_Time | Diff (Out-In) | Missing (IN/OUT)
6/12/2014 | 08:00 | 12:00 | 240 {Minutes} | N/A
6/12/2014 | | 15:00 | | IN
6/12/2014 | 15:10 | 16:00 | 60 | N/A

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-12 : 18:24:09

I see absolutely no correlation between the input data and the desired output.

BBarn
Starting Member

14 Posts

Posted - 2014-06-13 : 07:45:07

This query get's pretty darn close to the desired results. The problem is that I can't seem to find a way to identify the missing pairs. One problem with this query is the join which will throw out the non-matched record. Using a left join generated some strange behavior with the view representing IN's. The results were all the first IN of the day for each record.

The two views are identical except that one is limited to in's and one is limited to out's.

select i.date_time,
i.name,
convert(varchar, i.indate, 101) as indate,
i.intime,
o.outtime,
datediff(mi, CAST(i.intime as TIME), CAST(o.outtime as TIME)) as diff
from vw_in i
join vw_out o on o.name = i.name and o.cnt = i.cnt
where convert(date, i.indate, 101) = '6/4/2014'
order by i.name, i.intime

The other attempt that gets pretty close and shows promise, but getting a pivot or single line output (using case) is problematic for me. Both of these lead me to believe the solution is there, I just haven't found it yet.

SELECT ROW_NUMBER() OVER(PARTITION BY CH.LAST_NAME + ' ' + CH.FIRST_NAME ORDER BY CH.LAST_NAME + ' ' + CH.FIRST_NAME, rh.date_time) AS CNT,
convert(varchar,rh.DATE_TIME, 0) [DATE_TIME],
ch.FIRST_NAME + ' ' + ch.LAST_NAME [NAME],
case rh.READER_ID
WHEN 1 then 'IN'
WHEN 2 then 'OUT'
WHEN 3 then 'IN'
WHEN 4 then 'OUT'
end as DIR,
RH.DATE_TIME AS IN_DATE,
case rh.READER_ID
WHEN 1 then CONVERT(CHAR(5), DATE_TIME, 108)
WHEN 3 then CONVERT(CHAR(5), DATE_TIME, 108)
end as in_time,
case rh.READER_ID
WHEN 2 then CONVERT(CHAR(5), DATE_TIME, 108)
WHEN 4 then CONVERT(CHAR(5), DATE_TIME, 108)
end as OUT_time
FROM READER_HISTORY rh
JOIN EVENT_CODES ec ON ec.TYPE_CODE = rh.TYPE_CODE
JOIN READER_PORTS rp ON rp.READER_ID = rh.READER_ID
JOIN CARDHOLDERS ch ON ch.EMPLOYEE_ID = rh.EMPLOYEE_ID
WHERE rh.TYPE_CODE = '65' --type_code 65 means valid access
AND rh.READER_ID IN(1,2,3,4) --1=Main in, 2=Main Out, 3=Rear In, 4=Rear Out
and rh.DATE_TIME > '6/4/2014'
and rh.DATE_TIME < '6/5/2014'

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-06-13 : 12:32:22

Well, you really haven't explained what the expected output would be so this could easily be way off the mark. This should find the missing elements.

;with OrderedDoorActivity
as   (
select da.*, row_number() over (order by DATE_TIME, DIR) rn
from DoorActivity da
)
select
   d1.date_time, 
   case
      when d1.dir = 'IN' or d2.dir = 'IN' then 'OUT'
      else 'IN'
      end Missing_Scan
from
   OrderedDoorActivity d1
inner join
   OrderedDoorActivity d2
      on  d1.rn = d2.rn - 1
      and d1.dir = d2.dir

If this isn't useful, you'll need to elaborate, with examples (they really help!), on what you want.

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

BBarn
Starting Member

14 Posts

Posted - 2014-06-13 : 13:36:20

Ok, so the output I would like would be the following.


DATE_TIME | USER | In_time | Out_Time | Diff (Out-In)| Missing (IN/OUT)
6/12/2014 | FRED | 08:00   | 12:00    | 240 {Minutes}|    
6/12/2014 | FRED | 15:00   |          |              | IN 
6/12/2014 | FRED | 15:10   | 16:00    | 60	          |

The ultimate use for this is to review time the user states they were present versus the time the system says they were. The problem is the user has to swipe to open a door for entry into the office and is supposed to for exit, however, you can simply open the door to get out (Code states the door cannot lock from the inside for obvious reasons).

The goal here is two fold. First, calculate the time in the building based on card swipes. Secondly is to identify the users that are not adhering to policy by not swiping out.

The issue is that with the two direct queries that I posted, when you have two IN's or two OUT's next to each other, the calculation of time/duration will go negative. I believe the first thing that needs to be done to make this work is to pair the IN and OUT records to each other, filter out the ones that are not bidirectional pairs then identify what is missing to prevent pairing.

Once the sequence goes off (two of the same direction), all subsequent calculations are off (durations are negative) because the "IN first:OUT last" sequences are off. Somehow I either need to reset on the next IN and ignore everything in between the doubles (for the calculation) or have some other logic that can remove the "un-pairable" records before the calculation then add them back in after the calcs are complete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources