Hi Gani,
We can get the desired result set using the CTEs to get the filtered result and STUFF function on it to concatenate the Employee names.
Note : Your test data contains invalid Date (ie
2012-5-
2012..)
Go through the following code.
--Creating Temp table
CREATE TABLE [dbo].[Temp](
[EmpId] [int] NULL,
[EmpName] [varchar](30) NULL,
[StartTime] [datetime] NULL
)
-- Insert Data into Temp table
INSERT INTO [Temp]([EmpId],[EmpName],[StartTime])VALUES
(101,'Abdul','2012-05-01 08:40:30.000'),
(101,'Abdul','2012-05-01 09:40:30.000'),
(101,'Abdul','2012-05-01 12:20:30.000'),
(102,'Raju','2012-05-01 08:15:30.000'),
(102,'Raju','2012-05-01 12:40:30.000'),
(102,'Raju','2012-05-01 16:20:30.000'),
(103,'Albert','2012-05-01 08:40:30.000'),
(103,'Albert','2012-05-01 10:40:30.000'),
(103,'Albert','2012-05-01 12:20:30.000'),
(103,'Albert','2012-05-01 14:40:30.000'),
(103,'Albert','2012-05-01 15:40:30.000'),
(103,'Albert','2012-05-01 17:10:30.000'),
(103,'Albert','2012-05-01 17:15:30.000'),
(104,'Raghu','2012-05-01 08:00:30.000'),
(104,'Raghu','2012-05-01 09:40:30.000'),
(104,'Raghu','2012-05-01 12:20:30.000'),
(102,'Johnson','2012-05-01 08:00:30.000'),
(102,'Johnson','2012-05-01 12:40:30.000'),
(102,'Johnson','2012-05-01 16:20:30.000')
DECLARE @FromDate DATETIME ='2012-05-01';
WITH TimeSheetCte as (
SELECT DATEADD(hh,8,@FromDate) DateValue
UNION ALL select DATEADD(mi,15,DateValue)
FROM TimeSheetCte where DATEADD(mi,15,DateValue) < DATEADD(hh,17,@FromDate)
)
,DateRanges AS(
SELECT DateValue StartTime,DATEADD(mi,15,DateValue) EndTime FROM TimeSheetCte
)
,UsersWithTimeSlot As(
SELECT CONVERT(VARCHAR,DateRanges.StartTime,108) StatRange,CONVERT(VARCHAR,DateRanges.EndTime,108) EndRange
,ISNULL(Temp.EmpName,'-') EmpName,
ISNULL(CONVERT(VARCHAR,Temp.StartTime,108),'-') UserStartTime
,ISNULL(CONVERT(VARCHAR,Temp.StartTime,111),'-') StartDate
FROM DateRanges LEFT JOIN Temp ON Temp.StartTime >= DateRanges.StartTime
AND Temp.StartTime <=DateRanges.EndTime
)
SELECT COUNT(*) [No.Transactions], Info.StartDate,StatRange As StartRange,EndRange
,REPLACE(STUFF( (
SELECT ','+ EmpName FROM UsersWithTimeSlot
WHERE StatRange = Info.StatRange AND EndRange = Info.EndRange
FOR XML PATH('')),1,1,''
),',',' and ') EmployeeName
FROM (SELECT * fROM UsersWithTimeSlot WHERE EmpName <> '-' AND UserStartTime <> '-') Info
Group By Info.StatRange,Info.EndRange,Info.StartDate