現在、次のような従業員の時間エントリのリストがあります。
TimeSheetId TechnicianUserId TimeSheetDate FromDatetime ToDatetime
1215286 4730 2020-11-10 2020-11-10 14:15:00.000 2020-11-10 15:15:00.000
1215965 4730 2020-11-10 2020-11-10 15:15:00.000 2020-11-10 15:45:00.000
1215969 4730 2020-11-10 2020-11-10 15:45:00.000 2020-11-10 17:45:00.000
1215972 4730 2020-11-10 2020-11-10 17:45:00.000 2020-11-10 23:45:00.000
1215967 4730 2020-11-10 2020-11-10 23:45:00.000 2020-11-10 23:59:00.000
1215968 4730 2020-11-11 2020-11-11 00:00:00.000 2020-11-11 00:15:00.000
1215978 4730 2020-11-11 2020-11-11 00:15:00.000 2020-11-11 00:30:00.000
1215980 4730 2020-11-11 2020-11-11 16:00:00.000 2020-11-11 16:30:00.000
1215979 4735 2020-11-11 2020-11-11 00:30:00.000 2020-11-11 08:30:00.000
時間の処理方法のため、行6と7のTimeSheetDateを1日ロールバックする必要があります。これは、その前の行と技術的に連続した時間であり、すべてが11月ではなく11月10日に発生するようにシステムによって処理されるためです。 11.行8（行7の直後ではない）または行9（異なる従業員番号）をロールバックしないでください。
ネストされたクエリを試行すると、行6のみがキャッチされます。TimeSheetIdなしでCTEを試行すると（それを信頼する方法を実装する方法がわからないため）、反復と爆弾をいつ停止するかがわかりません。アウト。行6と7の両方を確実にキャッチするにはどうすればよいですか？
私が試みたCTE：
;WITH CTEDummyData (
[TechnicianUserId]
,[TimeSheetDate]
,[FromDatetime]
,[ToDatetime]
)
AS (
SELECT [TechnicianUserId]
,[TimeSheetDate]
,[FromDatetime]
,[ToDatetime]
FROM @DummyTime
UNION ALL
SELECT [TechnicianUserId]
,CASE
WHEN DATEDIFF(MINUTE,LAG([ToDateTime]) OVER (ORDER BY [FromDatetime]),[FromDatetime]) < 2
AND CAST([FromDatetime] AS DATE) <> LAG([TimeSheetDate]) OVER (ORDER BY [FromDatetime])
THEN DATEADD(DAY,-1,[TimeSheetDate])
ELSE [TimeSheetDate]
END
,[FromDatetime]
,[ToDatetime]
FROM CTEDummyData
)
SELECT *
FROM CTEDummyData
OPTION (MAXRECURSION 24)
SQLループを使用して、必要なすべてのデータが得られるまでデータを繰り返し更新するソリューションを試しました。
DECLARE @NOMOREFORMATTINGNEEDED AS BIT = 0;
IF (EXISTS(SELECT * FROM SYS.tables WHERE name like '%TempDummyTimeTable%'))
BEGIN
Drop table #TempDummyTimeTable;
END
SELECT * INTO #TempDummyTimeTable FROM DummyTimeTable;
WHILE (@NOMOREFORMATTINGNEEDED = 0)
BEGIN
update tl set TimeSheetDate = t.TimeSheetDate from #TempDummyTimeTable tl
join #TempDummyTimeTable t on tl.TechnicianUserId = t.technicianuserid
and ABS(DATEDIFF (MINUTE, tl.FromDatetime, t.ToDatetime)) <= 1
and tl.TimeSheetDate <> t.TimeSheetDate
IF (not exists(select * from #TempDummyTimeTable tl JOIN #TempDummyTimeTable t on tl.TechnicianUserId = t.technicianuserid
and ABS(DATEDIFF (MINUTE, tl.FromDatetime, t.ToDatetime)) <= 1
and tl.TimeSheetDate <> t.TimeSheetDate ) )
BEGIN
SET @NOMOREFORMATTINGNEEDED = 1
END
END
select * from #TempDummyTimeTable
基本的に、元のテーブルから新しい一時テーブルを作成します。次に、同じ従業員の別の行と連続しているが、TimeSheetDateが異なる行のインスタンスがあるかどうかを確認してから、そのTimeSheetDateを更新します。フラグ@NOMOREFORMATTINGNEEDEDがtrueに設定されるまで、ループ内でこれを繰り返します。このフラグをtrueに設定するのは、異なるTimeSheetDateを持つ連続レコードがもうないことを確認するチェックです。このチェックでは、一方のFromDateTimeがもう一方のToDateTimeから1分以内であることを確認しました。これがあなたのために働くかどうか私に知らせてください。
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加