[ad_1]
親愛なるみんな、
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
USERID CHECKTIME CHECKTYPE VERIFYCODE SENSORID
解決策 1
このようなサンプルコードを設定しました
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
..
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[^] 関数と % (係数)[^] オペレータがそれを修正します。 例えば
;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 値
– コメントで言及しました
見積もり:最初のチェックインと最後のチェックアウトまでの時間だけを使用したい。
たとえば、単純なグループで実行できます
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 テーブルで日付が欠落している場所を強調表示します。
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;
d.userid が null の場合、その日付のデータはありませんでした。
このように生成された日付の永続的なテーブルを用意することをお勧めします。結合は同じです。 そうすれば、非稼働日を簡単にマークアップできます。詳細については、このリンクを参照してください。 SQL Server での日付ディメンションまたはカレンダー テーブルの作成[^]
[ad_2]
コメント