I'm redesigning an attendance system used in my company. Originally, an attendance sheet was rendered on a Grid that was getting week-by-week data Pivoted. I've opted for a Scheduler control by DevExpress. A sign-in and a sign-out are tow be represented as two 'appointments' now.
The Scheduler requires a very specific data source for appointments. Here is my attendance table:
The DutyIn_Manual is what the employee reports and the DutyIn is the actual time captured. DutyIn_Manual is to be shown on the Scheduler. Similarly, DutyOut_Manual goes into view.
Now, I need to split one row into two: One for Duty In and one for Duty Out.
Any help greatly appreciated.
Update The solution by @Mikael Eriksson worked perfectly.
Here's my updated query:
DECLARE @T TABLE ( Id INT IDENTITY PRIMARY KEY, AttendanceId INT, StaffNumber VARCHAR(50), DutyDate DATE, DutyIn TIME, DutyOut TIME, DutyInManual TIME, DutyOutManual TIME ); INSERT INTO @T SELECT [iSAID], [cStaffNo], [dtDutyDate], [dtDutyIN], [dtDutyOUT], [dtDutyIN_Manual], [dtDutyOUT_Manual] FROM [Attendance].[dbo].[StaffAttendance]; SELECT T.Id, T.AttendanceId, T.StaffNumber, T.DutyDate, S.DutyAuto, S.DutyManual, S.DutyType FROM @T AS T CROSS APPLY ( VALUES (T .DutyIn, T.DutyInManual, 'In'), (T .DutyOut, T.DutyOutManual, 'Out') ) AS S (DutyAuto, DutyManual, DutyType);