在考勤系统中使用的SQL查询中需要帮助 [英] Need help in SQL query used in attandance system

问题描述

我正在开发一种考勤系统，该系统将从设备读取数据，并使用该数据来维持员工的出勤率.

我的数据就是这样.

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[^]

推荐答案

首先:切勿使用DATE，TIME，STATUS作为列名！ MS SQL有保留字！

在下面的示例中，我更改了列的名称，并使用了临时表(#->临时表).

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

结果:

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

这不完全是您想要的，但这是开始的要点；)

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

您需要在正在使用的表上执行自连接.
对我而言，这种连接似乎是交叉连接，因为您可以在没有ins的情况下有ins，但在没有ins的情况下也可以有outs.
您首先仅选择ins，然后第二次仅选择outs
加入条件会有点复杂.但这已经可以帮助您开始.

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.

这篇关于在考勤系统中使用的SQL查询中需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！