Need help in SQL query used in attandance system

Question

1.67/5 (5 votes)

See more:

SQL-Server-2005

, +

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

Tejas Vaishnav

Updated 6-Aug-12 3:50am

v5

Add a Solution

Comments

Santhosh Kumar Jayaraman 6-Aug-12 9:59am

what do you want? stored proc or select query?

ZurdoDev 6-Aug-12 11:43am

What have you done so far? Do you know SQL?

Joan M 6-Aug-12 12:03pm

yes.

Sergey Alexandrovich Kryukov 6-Aug-12 12:58pm

It wasn't an alternative question. In fact, the correct answer is: 42.
--SA

Joan M 6-Aug-12 13:04pm

Yes ;)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2012-08-06T11:50:00

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).

SQL

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:

FloorID	GATEID	ICARD	IN	OUT
1	1	15580735	2012-06-08 10:12:52.000	NULL
1	2	15580735	2012-06-08 10:47:00.000	2012-06-08 10:47:01.000
2	3	15580735	2012-06-08 10:50:00.000	2012-06-08 10:05:54.000
2	4	15580735	2012-06-08 10:10:41.000	2012-06-08 10:52:12.000
3	5	15580735	2012-06-08 10:52:01.000	NULL
3	6	15580735	NULL	2012-06-08 10:52:02.000

This is not exactly what you want, but this is the point to start ;)

Philip Stuyck · Answer 2 · 2012-08-06T10:40:00

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.

Need help in SQL query used in attandance system

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0