WITH b AS ( SELECT code, ROW_NUMBER() OVER (PARTITION BY code ORDER BY bdt ) n, bdt, edt FROM #b ), a0 AS ( SELECT code, bdt, (SELECT MIN(n) FROM b WHERE #a.code = b.code AND #a.bdt <= b.edt ) n, fz FROM #a ), a AS ( SELECT a0.code, a0.bdt, a0.n, a0.fz, CASE WHEN a0.bdt <= b.bdt THEN b.bdt ELSE a0.bdt END AS fbdt FROM a0 JOIN b ON a0.code = b.code AND a0.n = b.n ), c AS ( SELECT a.code, a.bdt, a.n, a.fz - DateDiff(minute,a.fbdt,b.edt) AS fz, a.fbdt, CASE WHEN b.edt <= DateAdd(minute,a.fz,a.fbdt) THEN b.edt ELSE DateAdd(minute,a.fz,a.fbdt) END AS fedt FROM a JOIN b ON a.code = b.code AND a.n = b.n UNION ALL SELECT c.code, c.bdt, b.n, c.fz - DateDiff(minute,b.bdt,b.edt) AS fz, b.bdt AS fbdt, CASE WHEN b.edt <= DateAdd(minute,c.fz,b.bdt) THEN b.edt ELSE DateAdd(minute,c.fz,b.bdt) END AS fedt FROM c JOIN b ON c.code = b.code AND c.n+1 = b.n WHERE c.fz > 0 ) --SELECT * FROM c ORDER BY code, bdt, n UPDATE #a SET fbdt = t.fbdt, fedt = t.fedt FROM #a, ( SELECT code, bdt, MIN(fbdt) fbdt, MAX(fedt) fedt FROM c GROUP BY code, bdt ) t WHERE #a.code = t.code AND #a.bdt = t.bdtSELECT * FROM #a code bdt fz fbdt fedt ---------- ----------------------- ----------- ----------------------- ----------------------- a01 2014-09-02 08:00:00.000 200 2014-09-02 09:00:00.000 2014-09-02 13:20:00.000 a01 2014-09-05 09:00:00.000 560 2014-09-05 09:00:00.000 2014-09-06 09:20:00.000
SELECT code,
ROW_NUMBER() OVER (PARTITION BY code
ORDER BY bdt
) n,
bdt,
edt
FROM #b
),
a0 AS (
SELECT code,
bdt,
(SELECT MIN(n)
FROM b
WHERE #a.code = b.code
AND #a.bdt <= b.edt
) n,
fz
FROM #a
),
a AS (
SELECT a0.code,
a0.bdt,
a0.n,
a0.fz,
CASE WHEN a0.bdt <= b.bdt THEN
b.bdt
ELSE
a0.bdt
END AS fbdt
FROM a0
JOIN b
ON a0.code = b.code
AND a0.n = b.n
),
c AS (
SELECT a.code,
a.bdt,
a.n,
a.fz - DateDiff(minute,a.fbdt,b.edt) AS fz,
a.fbdt,
CASE WHEN b.edt <= DateAdd(minute,a.fz,a.fbdt) THEN
b.edt
ELSE
DateAdd(minute,a.fz,a.fbdt)
END AS fedt
FROM a
JOIN b
ON a.code = b.code
AND a.n = b.n UNION ALL SELECT c.code,
c.bdt,
b.n,
c.fz - DateDiff(minute,b.bdt,b.edt) AS fz,
b.bdt AS fbdt,
CASE WHEN b.edt <= DateAdd(minute,c.fz,b.bdt) THEN
b.edt
ELSE
DateAdd(minute,c.fz,b.bdt)
END AS fedt
FROM c
JOIN b
ON c.code = b.code
AND c.n+1 = b.n
WHERE c.fz > 0
)
--SELECT * FROM c ORDER BY code, bdt, n
UPDATE #a
SET fbdt = t.fbdt,
fedt = t.fedt
FROM #a,
(
SELECT code,
bdt,
MIN(fbdt) fbdt,
MAX(fedt) fedt
FROM c
GROUP BY code, bdt
) t
WHERE #a.code = t.code
AND #a.bdt = t.bdtSELECT * FROM #a
code bdt fz fbdt fedt
---------- ----------------------- ----------- ----------------------- -----------------------
a01 2014-09-02 08:00:00.000 200 2014-09-02 09:00:00.000 2014-09-02 13:20:00.000
a01 2014-09-05 09:00:00.000 560 2014-09-05 09:00:00.000 2014-09-06 09:20:00.000