Unable to get data with CTE in Desired Format

• shahhirenj

  Newbie

  Points: 1

  More actions

  April 18, 2015 at 1:10 am

  #299919

  Hi All,

  I am newbie and was working on one Report. I need some urgent help.

  I am unable to get the report in desired format with the CTE.

  

  I have written below script but not getting it it how I need it.

  USE [BPMAINDB]

  CREATE TABLE [dbo].[Master](

  [EMPLOYEENAME] [varchar](50) NULL,

  [WORKDAY] [datetime] NULL,

  [ACTIVITYSTARTTIME] [datetime] NULL,

  [ACTIVITYENDTIME] [datetime] NULL,

  [ACTIVITYNAME] [varchar](20) NULL

  )

  GO

  --DAY1 -- ACTIVITY1

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person1','2015-03-30 00:00:00.000','2015-03-30 12:00:00.000','2015-03-30 14:00:00.000', N'Activity1')

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person2','2015-03-30 00:00:00.000','2015-03-30 14:00:00.000','2015-03-30 16:00:00.000', N'Activity1')

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person3','2015-03-30 00:00:00.000','2015-03-30 16:00:00.000','2015-03-30 18:00:00.000', N'Activity1')

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person4','2015-03-30 00:00:00.000','2015-03-30 12:00:00.000','2015-03-30 14:00:00.000', N'Activity1')

  --DAY1 -- ACTIVITY2

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person5','2015-03-30 00:00:00.000','2015-03-30 12:00:00.000','2015-03-30 14:00:00.000', N'Activity2')

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person6','2015-03-30 00:00:00.000','2015-03-30 14:00:00.000','2015-03-30 16:00:00.000', N'Activity2')

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person7','2015-03-30 00:00:00.000','2015-03-30 14:00:00.000','2015-03-30 16:00:00.000', N'Activity2')

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person7','2015-03-30 00:00:00.000','2015-03-30 16:00:00.000','2015-03-30 18:00:00.000', N'Activity2')

  --DAY2 -- ACTIVITY1

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person8','2015-03-31 00:00:00.000','2015-03-31 10:00:00.000','2015-03-31 12:00:00.000', N'Activity1')

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person9','2015-03-31 00:00:00.000','2015-03-31 10:00:00.000','2015-03-31 12:00:00.000', N'Activity1')

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person10','2015-03-31 00:00:00.000','2015-03-31 14:00:00.000','2015-03-31 16:00:00.000', N'Activity1')

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person10','2015-03-31 00:00:00.000','2015-03-31 16:00:00.000','2015-03-31 18:00:00.000', N'Activity1')

  --DAY2 -- ACTIVITY2

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person8','2015-03-31 00:00:00.000','2015-03-31 16:00:00.000','2015-03-31 18:00:00.000', N'Activity2')

  INSERT [dbo].[Master] ([EMPLOYEENAME], [WORKDAY], [ACTIVITYSTARTTIME], [ACTIVITYENDTIME], [ACTIVITYNAME]) VALUES (N'Person9','2015-03-31 00:00:00.000','2015-03-31 14:00:00.000','2015-03-31 16:00:00.000', N'Activity2')

  Select * From master

  ;WITH CTE (RowID, WORKDAY, ACTIVITYNAME,EMPLOYEENAME, [1000-1200],[1200-1400],[1400-1600],[1600-1800],[1800-2000]) AS

  (

  SELECT ROW_NUMBER()

  OVER (ORDER BY WORKDAY,ACTIVITYNAME,EMPLOYEENAME,ACTIVITYSTARTTIME) AS RowID, WORKDAY,ACTIVITYNAME,EMPLOYEENAME, --,ACTIVITYSTARTTIME, ACTIVITYENDTIME,

  (case when (convert(varchar(5),ACTIVITYSTARTTIME,108) >= '10:00' and convert(varchar(5),ACTIVITYSTARTTIME,108) < '12:00')

  OR (convert(varchar(5),ACTIVITYENDTIME,108) >= '10:00' and convert(varchar(5),ACTIVITYENDTIME,108) <= '12:00') then 1 else 0 end) AS [1000-1200],

  (case when (convert(varchar(5),ACTIVITYSTARTTIME,108) >= '12:00' and convert(varchar(5),ACTIVITYSTARTTIME,108) < '14:00')

  OR (convert(varchar(5),ACTIVITYENDTIME,108) >= '12:00' and convert(varchar(5),ACTIVITYENDTIME,108) <= '14:00') then 1 else 0 end) AS [1200-1400],

  (case when (convert(varchar(5),ACTIVITYSTARTTIME,108) >= '14:00' and convert(varchar(5),ACTIVITYSTARTTIME,108) < '16:00')

  OR (convert(varchar(5),ACTIVITYENDTIME,108) >= '14:00' and convert(varchar(5),ACTIVITYENDTIME,108) <= '16:00') then 1 else 0 end) AS [1400-1600],

  (case when (convert(varchar(5),ACTIVITYSTARTTIME,108) >= '16:00' and convert(varchar(5),ACTIVITYSTARTTIME,108) < '18:00')

  OR (convert(varchar(5),ACTIVITYENDTIME,108) >= '16:00' and convert(varchar(5),ACTIVITYENDTIME,108) <= '18:00') then 1 else 0 end) AS [1600-1800],

  (case when (convert(varchar(5),ACTIVITYSTARTTIME,108) >= '18:00' and convert(varchar(5),ACTIVITYSTARTTIME,108) < '20:00')

  OR (convert(varchar(5),ACTIVITYENDTIME,108) >= '18:00' and convert(varchar(5),ACTIVITYENDTIME,108) <= '20:00') then 1 else 0 end) AS [1800-2000]

  FROM Master

  )

  SELECT CTE.WORKDAY, CTE.ACTIVITYNAME,

  case when [1000-1200] = 1 then m.EMPLOYEENAME end AS [1000-1200],case when [1200-1400] = 1 then m.EMPLOYEENAME end AS [1200-1400],

  case when [1400-1600] = 1 then m.EMPLOYEENAME end AS [1400-1600],case when [1600-1800] = 1 then m.EMPLOYEENAME end AS [1600-1800],

  case when [1800-2000] = 1 then m.EMPLOYEENAME end AS [1800-2000]

  FROM CTE

  INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY WORKDAY,ACTIVITYNAME,EMPLOYEENAME,ACTIVITYSTARTTIME) AS RowID, * from Master) m

  on CTE.ROWID = m.RowID