CREATE TABLE PatientsEmployeesSchedule
([ID] int, [EmployeeID] int, [PatientID] int, [Day] datetime, [From] datetime, [To] datetime)
;
INSERT INTO PatientsEmployeesSchedule
([ID], [EmployeeID], [PatientID], [Day], [From], [To])
VALUES
(1 , 123, 456, '2015-10-27 00:00:00', '1900-01-01 00:20:00', '1900-01-01 00:08:00')
;
This query uses CROSS APPLY and VALUES to generate 2 rows output for each one row of input.
select
ca.*
from PatientsEmployeesSchedule pes
cross apply (
values
(pes.ID, pes.EmployeeID, pes.PatientID, pes.[Day], pes.[From], dateadd(hour,24,0))
, (pes.ID, pes.EmployeeID, pes.PatientID, dateadd(day,1,pes.[Day]), dateadd(hour,24,0), pes.[To])
) ca ([ID], [EmployeeID], [PatientID], [Day], [From], [To])
where pes.[From] > pes.[To]
| ID | EmployeeID | PatientID | Day | From | To |
|----|------------|-----------|---------------------------|---------------------------|---------------------------|
| 1 | 123 | 456 | October, 27 2015 00:00:00 | January, 01 1900 00:20:00 | January, 02 1900 00:00:00 |
| 1 | 123 | 456 | October, 28 2015 00:00:00 | January, 02 1900 00:00:00 | January, 01 1900 00:08:00 |
also see http://sqlfiddle.com/#!6/c57b5/5DECLARE @Employee TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
EmployeeID INT,
PatientID INT,
[Day] DATE,
[From] TIME,
[To] TIME
);
DECLARE @EmployeeOut TABLE
(
ID INT NULL,
EmployeeID INT,
PatientID INT,
[Day] DATE,
[From] TIME,
[To] TIME
);
INSERT INTO @Employee
VALUES (123, 456, '20151027', '20:00', '08:00');
INSERT INTO @EmployeeOut
SELECT ID,EmployeeID,PatientID,[Day],[From],CASE WHEN [To]<[From] THEN '12:00' ELSE [To] END FROM @Employee
INSERT INTO @EmployeeOut
SELECT ID,EmployeeID,PatientID,CONVERT(DATE,CASE WHEN [To]<[From] THEN DATEADD(D,1,[Day]) ELSE [Day] END) [DAY],[From],[To] FROM @Employee
SELECT * FROM @Employee
SELECT * FROM @EmployeeOut
SELECT
ca.*
from PatientsEmployeesSchedule pes
CROSS APPLY (
VALUES
(pes.ID, pes.EmployeeID, pes.PatientID, pes.[Day], pes.[From], '18991231')
, (pes.ID, pes.EmployeeID, pes.PatientID, DATEADD(day, 1, pes.[Day]), '18991230', pes.[To])
) ca ([ID], [EmployeeID], [PatientID], [Day], [From], [To])
WHERE pes.[From] > pes.[To]
union all
select ID,EmployeeID,PatientID,[Day],[from],[TO] from PatientsEmployeesSchedule pes
WHERE pes.[From] <= pes.[To]
Open in new window