【解決方法】SQL の zk 出席ログから checkinout テーブルのインポートを分離したい

親愛なるみんな、

SQLでzk出席マシンを接続し、データをインポートしました。しかし、checkinout テーブルでは、checktime と呼ばれる 1 つの列にすべてが表示されます。チェックインとチェックアウトを異なる列に表示したい。

テーブル名 CHECKINOUT とカラム名 checktime.

USERID      CHECKTIME
----------- -----------------------
1           2022-10-04 10:25:44.000
1           2022-10-04 10:25:46.000
1           2022-10-04 10:30:55.000
1           2022-10-04 11:39:23.000
2           2022-10-07 14:05:22.000
2           2022-10-08 09:19:58.000
2           2022-10-08 15:34:53.000

私が試したこと：

select * from CHECKINOUT

SQL

USERID      CHECKTIME               CHECKTYPE VERIFYCODE  SENSORID

解決策 1

このようなサンプルコードを設定しました

SQL

declare @demo table (userid int, checktime datetime);
insert into @demo (userid, checktime) values
 (1,'2022-10-04 10:25:44.000')
,(1,'2022-10-04 10:25:46.000')
,(1,'2022-10-04 10:30:55.000')
,(1,'2022-10-04 11:39:23.000')
,(2,'2022-10-07 14:05:22.000')
,(2,'2022-10-08 09:19:58.000')
,(2,'2022-10-08 15:34:53.000');

を使用することにより、リード [^] 関数すべての行とそれぞれについて、リスト内の次の日時を取得できます userid ..

SQL

select 
	userid, checktime, 
	LEAD(checktime, 1) 
		OVER (partition by userid order by userid, checktime) as nextcheck
from @demo;

注意してください partition by の中に over 句。
それは私にこれらの結果を与えます：

userid	checktime				nextcheck
1		2022-10-04 10:25:44.000	2022-10-04 10:25:46.000
1		2022-10-04 10:25:46.000	2022-10-04 10:30:55.000
1		2022-10-04 10:30:55.000	2022-10-04 11:39:23.000
1		2022-10-04 11:39:23.000	NULL
2		2022-10-07 14:05:22.000	2022-10-08 09:19:58.000
2		2022-10-08 09:19:58.000	2022-10-08 15:34:53.000
2		2022-10-08 15:34:53.000	NULL

ご覧のとおり、重複があります- nextcheck 行1になる checktime 行 2 で、次の組み合わせを使用できます。 ROW_NUMBER[^] 関数と％（係数）[^] オペレータがそれを修正します。例えば

SQL

;with cte as
(
	select 
		userid, checktime, 
		LEAD(checktime, 1) 
			OVER (partition by userid order by userid, checktime) as nextcheck, 
		Row_number() OVER (partition by userid order by userid, checktime) as rn
	from @demo
)
select userid, checktime as checkin, nextcheck as checkout
from cte
where rn % 2 = 1;

これらの結果を与える

userid	checkin					checkout
1		2022-10-04 10:25:44.000	2022-10-04 10:25:46.000
1		2022-10-04 10:30:55.000	2022-10-04 11:39:23.000
2		2022-10-07 14:05:22.000	2022-10-08 09:19:58.000
2		2022-10-08 15:34:53.000	NULL

注意点:
– これは、機器が正常に機能していること、およびユーザーがチェックインとチェックアウトについて訓練を受けていることを前提としています。
– ユーザー 2 はチェックインしたがチェックアウトしていないことに注意してください – したがって、NULL 値
– コメントで言及しました

見積もり：

最初のチェックインと最後のチェックアウトまでの時間だけを使用したい。

たとえば、単純なグループで実行できます

SQL

select userid, MIN(checktime) as checkin, MAX(checktime) as checkout
from @demo
group by userid;

与える

userid	checkin						checkout
1		2022-10-04 10:25:44.000	2022-10-04 11:39:23.000
2		2022-10-07 14:05:22.000	2022-10-08 15:34:53.000

ただし、userid = 2 の場合、これは実際にはチェックアウト時間ではなく、最後のチェックイン時間であることに注意してください (そして、それらはまだそこにあります)。
したがって、最初に (上記のように) チェックイン/チェックアウトの完全なリストを実行してから、それらの結果の最小/最大を実行することをお勧めします。
あなたがそれを解決するのに十分なはずです

OP コメントの後に編集します。次のクエリは、2 つの日付の間のすべての日付のリストを生成し、上の @demo テーブルで日付が欠落している場所を強調表示します。

SQL

declare @startdate date = '2022-10-04';
declare @enddate date = '2022-10-10';
WITH q AS
    (
    SELECT  @startdate AS datum
    UNION ALL
    SELECT  dateadd(DAY, 1,datum)
    FROM    q
    WHERE dateadd(DAY, 1,datum) < @enddate
    )
SELECT  datum, d.userid, d.checktime
FROM    q
left outer join @demo d on CAST(d.checktime as date) = q.datum;