Friday, January 24

Miscellaneous Tasks 3


CREATE TABLE Schedules
(
        Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        EmpName NVARCHAR(255) NOT NULL,
        StartDate DATETIME NOT NULL,
        EndDate DATETIME NOT NULL,
        Roster  NVARCHAR(50) NOT NULL
);
GO

INSERT INTO Schedules
values('Jiss','20-Oct-13','26-Oct-13','_*___**'),
      ('Jom','20-Oct-13','26-Oct-13','*_*__*_'); 
-------------------------------------------------------------------------------
The Input table Table looks Like this

Id
EmpName
StartDate
EndDate
Roster
1
Jiss
20-Oct-13
26-Oct-13
_*___**
2
Jom
20-Oct-13
26-Oct-13
*_*__*_

The Roster Column shows the day worked i.e that person is present on that day between StartDate and EndDate
The symbol  * shows that person is present for that day and
The symbol  _  shows that person is absent  for that day
For eg:  Jiss is absent for 20,22,23,24 and Jom is absent for 21,23,24,26

The output table should like this
EmpName
Date
HasWorked
Jiss
20/10/2013
0
Jom
20/10/2013
1
Jiss
21/10/2013
1
Jom
21/10/2013
0
Jiss
22/10/2013
0
Jom
22/10/2013
1
Jiss
23/10/2013
0
Jom
23/10/2013
0
Jiss
24/10/2013
0
Jom
24/10/2013
0
Jiss
25/10/2013
1
Jom
25/10/2013
1
Jiss
26/10/2013
1
Jom
26/10/2013
0

Note:From Roster Column HasWorked column has to be determined,
     For Present day by 1 and Absent day by 0 for each date

Solution:


WITH DateList
AS
(

SELECT startdate [Date] ,ID,empname,
         substring(Roster, 1, 1) as Chars,
         stuff(Roster, 1, 1, '') as Roster,
         1 as RowID from Schedules
UNION all
SELECT [Date] +1,ID,empname,
         substring(Roster, 1, 1) as Chars,
         stuff(Roster, 1, 1, '') as Roster,
         RowID + 1 as RowID FROM DateList WHERE [Date] <(select  enddate from Schedules where ID=1)
)
SELECT EmpName,CONVERT(VARCHAR(10), [Date],120) [Date],
case when Chars = '_' then 0
else 1
end HasWorked FROM DateList order by [Date]