2

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:

Attendace System Backend

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);
3

You can use the Table Value Constructor in a cross apply to generate two rows for each row in the source table.

declare  @T table
(
  ID int identity primary key,
  DutyIn datetime,
  DutyOut datetime,
  DutyInManual datetime,
  DutyOutManual datetime
);


insert into @T values 
('2016-10-01T10:10:00', '2016-10-01T10:50:00', '2016-10-01T10:00:00', '2016-10-01T11:00:00');

select T.ID,
       S.DutyAuto,
       S.DutyManual
from @T as T
  cross apply (values(T.DutyIn, T.DutyInManual),
                     (T.DutyOut, T.DutyOutManual)) as S(DutyAuto, DutyManual);
  • This worked perfectly. I've updated answer with final query based on this one. – Hassan Gulzar Oct 25 '16 at 3:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.