Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I am developing attendance system which will read data from device, and use that data to maintain employee attendance.
 
my data is like this.
FloorID	GATEID	ICARD	          DATE	         TIME	      STATUS
    2	4	15580735	06-08-12	09:47:52	IN
    2	3	15580735	06-08-12	10:05:54	OUT
    1	1	15580735	06-08-12	10:05:34	IN
    2	4	15580735	06-08-12	10:10:41	IN
    1	2	15580735	06-08-12	10:11:50	OUT
    1	1	15580735	06-08-12	10:12:52	IN
    1	2	15580735	06-08-12	10:47:00	IN
    1	2	15580735	06-08-12	10:47:01	OUT
    2	3	15580735	06-08-12	10:50:00	IN
    2	4	15580735	06-08-12	10:52:12	OUT
    3	5	15580735	06-08-12	10:52:01	IN
    3	6	15580735	06-08-12	10:52:01	OUT
    3	6	15580735	06-08-12	10:52:02	OUT
    
 
Case for enter new entery and update existing entery
need to use only same floor data to update entry
seconds will be ignored from time part, need to use only HH:MI
IN  Always New Entry
OUT 1) Check Related IN is there Update Out
    2) If no IN Found Add New Entry for out
    3) If same Out Entry more then one Update first IN Entry add remaining Out as New Entry
like this
Desiered output[^]
Posted 6-Aug-12 3:44am
Edited 6-Aug-12 3:50am
v5
Comments
Santhosh Kumar J at 6-Aug-12 9:59am
   
what do you want? stored proc or select query?
ryanb31 at 6-Aug-12 11:43am
   
What have you done so far? Do you know SQL?
Joan Murt at 6-Aug-12 12:03pm
   
yes.
Sergey Alexandrovich Kryukov at 6-Aug-12 12:58pm
   
It wasn't an alternative question. In fact, the correct answer is: 42. --SA
Joan Murt at 6-Aug-12 13:04pm
   
Yes ;)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

First of all: NEVER use DATE, TIME, STATUS as the name of columns! There are reserved words for MS SQL!
 
In the below example, i change the names of columns and i use temporary table (# -> temporary).
IF NOT OBJECT_ID(N'#DeviceData',N'T') IS NULL
	DROP TABLE #DeviceData
 
CREATE TABLE #DeviceData(FloorID INT, GATEID INT, ICARD INT, DATE_TIME DATETIME, STAT NVARCHAR(50))
 
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (2, 4, 15580735, '06-08-12 09:47:52', 'IN')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (2, 3, 15580735, '06-08-12 10:05:54', 'OUT')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (1, 1, 15580735, '06-08-12 10:05:34', 'IN')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (2, 4, 15580735, '06-08-12 10:10:41', 'IN')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (1, 2, 15580735, '06-08-12 10:11:50', 'OUT')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (1, 1, 15580735, '06-08-12 10:12:52', 'IN')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (1, 2, 15580735, '06-08-12 10:47:00', 'IN')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (1, 2, 15580735, '06-08-12 10:47:01', 'OUT')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (2, 3, 15580735, '06-08-12 10:50:00', 'IN')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (2, 4, 15580735, '06-08-12 10:52:12', 'OUT')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (3, 5, 15580735, '06-08-12 10:52:01', 'IN')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (3, 6, 15580735, '06-08-12 10:52:01', 'OUT')
INSERT INTO #DeviceData (FloorID, GATEID, ICARD, DATE_TIME, STAT)
    VALUES (3, 6, 15580735, '06-08-12 10:52:02', 'OUT')
 
DECLARE @cols NVARCHAR(100)
DECLARE @dt NVARCHAR(1000)
DECLARE @pt NVARCHAR(2000)
 
SET @cols = STUFF((SELECT DISTINCT '],[' + STAT
					FROM #DeviceData
					ORDER BY '],[' + STAT
			FOR XML PATH('')),1,2,'') + ']'
 
SET @dt = 'SELECT * FROM #DeviceData'
 
SET @pt = 'SELECT FloorID, GATEID, ICARD, ' + @cols + ' ' +
		'FROM (' + @dt + ') AS DT ' + 
		'PIVOT (MAX(DATE_TIME) FOR STAT IN(' + @cols + ')) AS PT ' + 
		'ORDER BY PT.ICARD '
EXEC (@pt)
 
DROP TABLE #DeviceData
 
Result:

FloorIDGATEIDICARDINOUT
11155807352012-06-08 10:12:52.000NULL
12155807352012-06-08 10:47:00.0002012-06-08 10:47:01.000
23155807352012-06-08 10:50:00.0002012-06-08 10:05:54.000
24155807352012-06-08 10:10:41.0002012-06-08 10:52:12.000
35155807352012-06-08 10:52:01.000NULL
3615580735NULL2012-06-08 10:52:02.000
This is not exactly what you want, but this is the point to start Wink | ;)
  Permalink  
Comments
Tejas_Vaishnav at 7-Aug-12 1:43am
   
thnaks Losmac. but your query is not working for me. there is some difference between your query output and what i want. that's why i put the link for what is desired out put for data. can you please see that and try to provide an other solutions that's great for me if you can. thanks once again.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You need to execute a self join on the table that you are using.
The kind of join seems to me a cross join because you can have ins without outs but you can also have outs without ins.
You first select only the ins, and then the second time you select only the outs
the join condition will be a bit complicated. But this will get you started already.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 576
1 OriginalGriff 183
2 Tadit Dash 170
3 Sergey Alexandrovich Kryukov 156
4 Peter Leow 130
0 Sergey Alexandrovich Kryukov 9,325
1 OriginalGriff 5,473
2 Peter Leow 4,150
3 Maciej Los 3,540
4 Abhinav S 3,333


Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 6 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid