How to set values at different time intervals?

Question

I have a table as under

AGREEMENTID         FeedbackDate               DispositionCode  CollectorId
0003SBML00151   2017-08-22 08:00:00.000                PTP        1
0004SBHL00705   2017-08-22 08:20:00.000                BPTP       1
0007SBML01987   2017-08-22 09:10:00.000                SET        1
0026MSS00108    2017-08-22 07:50:00.000                PTP        2
0026MSS00108    2017-08-22 10:30:00.000                NC         2
0026MSS00108    2017-08-22 10:30:10.000                DL         2
0026MSS00108    2017-08-22 15:47:00.000                CB         2
0026MSS00108    2017-08-22 19:52:00.000                BPTP       2

The final output will be

Collector 1 has made two Disposition at 8.00 hrs and 8.20 hrs. So against 8hrs column his disposition code is available. Also he made one Disposition at 9.10 hrs for which against 9hrs column his disposition code is available. Likewise for Collector 2.

What I have tried so far

Declare @T table
(AGREEMENTID varchar(50),
FeedbackDate varchar(50),
DispositionCode varchar(10),
[CollectorId] varchar(100)
)
Insert into @T 
SELECT '0003SBML00151','2017-08-22 08:00:00.000','PTP','1' union all
SELECT '0004SBHL00705','2017-08-22 08:20:00.000','BPTP','1' union all
SELECT '0007SBML01987','2017-08-22 09:10:00.000','BPTP','1' union all
SELECT '0026MSS00108','2017-08-21 07:50:00.000','PTP','2' union all
SELECT '0026MSS00108','2017-08-21 10:30:00.000','NC','2' union all
SELECT '0026MSS00108','2017-08-21 10:30:10.000','DL' ,'2' union all
SELECT '0026MSS00108','2017-08-21 15:47:00.000','CB' ,'2' union all
SELECT '0026MSS00108','2017-08-21 19:52:00.000','BPTP','2'

;with   HoursCte as
        (
        select  7 as Hrs
        union all
        select  Hrs + 1
        from    HoursCte
        where   Hrs < 22
        ),
 cte1 as(
SELECT 
    CollectorId
    ,AGREEMENTID
    ,FeedbackDate
    ,FeedbackTime = CONVERT(nvarchar,CAST(FeedbackDate as time),108)
    ,DispositionCode
FROM @T t1),
cte2 as(
Select *, FeedbackHrs = case when FeedbackTime between cast('07:00:00' as time) and cast('07:59:59' as time) then '7Hrs'
               when FeedbackTime between cast('08:00:00' as time) and cast('08:59:59' as time) then '8Hrs'
               when FeedbackTime between cast('09:00:00' as time) and cast('09:59:59' as time) then '9Hrs'
               when FeedbackTime between cast('10:00:00' as time) and cast('10:59:59' as time) then '10Hrs'
               when FeedbackTime between cast('11:00:00' as time) and cast('11:59:59' as time) then '11Hrs'
               when FeedbackTime between cast('12:00:00' as time) and cast('12:59:59' as time) then '12Hrs'
               when FeedbackTime between cast('13:00:00' as time) and cast('13:59:59' as time) then '13Hrs'
               when FeedbackTime between cast('14:00:00' as time) and cast('14:59:59' as time) then '14Hrs'
               when FeedbackTime between cast('15:00:00' as time) and cast('15:59:59' as time) then '15Hrs'
               when FeedbackTime between cast('16:00:00' as time) and cast('16:59:59' as time) then '16Hrs'
               when FeedbackTime between cast('17:00:00' as time) and cast('17:59:59' as time) then '17Hrs'
               when FeedbackTime between cast('18:00:00' as time) and cast('18:59:59' as time) then '18Hrs'
               when FeedbackTime between cast('19:00:00' as time) and cast('19:59:59' as time) then '19Hrs'
               when FeedbackTime between cast('20:00:00' as time) and cast('20:59:59' as time) then '20Hrs'
               when FeedbackTime between cast('21:00:00' as time) and cast('21:59:59' as time) then '21Hrs'
               when FeedbackTime between cast('22:00:00' as time) and cast('22:59:59' as time) then '22Hrs'
          end
From cte1)
Select 
    CollectorId
    ,Hrs = CAST(Hrs as Varchar(4)) + 'Hrs'
    ,FeedbackHrs
    ,DispositionCode
From HoursCte h
Left join cte2 c2 on CAST(h.Hrs as Varchar(4)) + 'Hrs' = c2.FeedbackHrs

And I am lost after that.

Answer 1

same approach with small modification:

Declare @T table
(AGREEMENTID varchar(50),
FeedbackDate varchar(50),
DispositionCode varchar(10),
[CollectorId] varchar(100)
)
Insert into @T 
SELECT '0003SBML00151','2017-08-22 08:00:00.000','PTP','1' union all
SELECT '0004SBHL00705','2017-08-22 08:20:00.000','BPTP','1' union all
SELECT '0007SBML01987','2017-08-22 09:10:00.000','BPTP','1' union all
SELECT '0026MSS00108','2017-08-21 07:50:00.000','PTP','2' union all
SELECT '0026MSS00108','2017-08-21 10:30:00.000','NC','2' union all
SELECT '0026MSS00108','2017-08-21 10:30:10.000','DL' ,'2' union all
SELECT '0026MSS00108','2017-08-21 15:47:00.000','CB' ,'2' union all
SELECT '0026MSS00108','2017-08-21 19:52:00.000','BPTP','2' 

    ;with cte1 as(
SELECT 
    CollectorId
    ,AGREEMENTID
    ,FeedbackDate
    ,FeedbackTime = CONVERT(nvarchar,CAST(FeedbackDate as time),108)
    ,DispositionCode
FROM @T t1)

Select *, case when FeedbackTime between cast('07:00:00' as time) and cast('07:59:59' as time) then '7Hrs'
               when FeedbackTime between cast('08:00:00' as time) and cast('08:59:59' as time) then '8Hrs'
               when FeedbackTime between cast('09:00:00' as time) and cast('09:59:59' as time) then '9Hrs'
               when FeedbackTime between cast('10:00:00' as time) and cast('10:59:59' as time) then '10Hrs'
               when FeedbackTime between cast('11:00:00' as time) and cast('11:59:59' as time) then '11Hrs'
               when FeedbackTime between cast('12:00:00' as time) and cast('12:59:59' as time) then '12Hrs'
               when FeedbackTime between cast('13:00:00' as time) and cast('13:59:59' as time) then '13Hrs'
               when FeedbackTime between cast('14:00:00' as time) and cast('14:59:59' as time) then '14Hrs'
               when FeedbackTime between cast('15:00:00' as time) and cast('15:59:59' as time) then '15Hrs'
               when FeedbackTime between cast('16:00:00' as time) and cast('16:59:59' as time) then '16Hrs'
               when FeedbackTime between cast('17:00:00' as time) and cast('17:59:59' as time) then '17Hrs'
               when FeedbackTime between cast('18:00:00' as time) and cast('18:59:59' as time) then '18Hrs'
               when FeedbackTime between cast('19:00:00' as time) and cast('19:59:59' as time) then '19Hrs'
               when FeedbackTime between cast('20:00:00' as time) and cast('20:59:59' as time) then '20Hrs'
               when FeedbackTime between cast('21:00:00' as time) and cast('21:59:59' as time) then '21Hrs'
               when FeedbackTime between cast('22:00:00' as time) and cast('22:59:59' as time) then '22Hrs'
          end collectedhours into #test
From cte1
select * from #test

select CollectorId,  isnull([7Hrs], '')as[7Hrs], 
 isnull([8Hrs], '')as[8Hrs], 
 isnull([9Hrs], '')as[9Hrs], 
 isnull([10Hrs],'')as[10Hrs], 
 isnull([11Hrs],'')as[11Hrs], 
 isnull([12Hrs],'')as[12Hrs], 
 isnull([13Hrs],'')as[13Hrs], 
 isnull([14Hrs],'')as[14Hrs], 
 isnull([15Hrs],'')as[15Hrs], 
 isnull([16Hrs],'')as[16Hrs], 
 isnull([17Hrs],'')as[17Hrs],
 isnull([18Hrs],'')as[18Hrs], 
 isnull([19Hrs],'')as[19Hrs], 
 isnull([20Hrs],'')as[20Hrs], 
 isnull([21Hrs],'')as[21Hrs], 
 isnull([22Hrs] ,'')as[22Hrs]
 from (
select distinct CollectorId,collectedhours,stuff((select ','+DispositionCode from #test a where a.CollectorId=b.CollectorId
and a.collectedhours=b.collectedhours
 group by CollectorId,DispositionCode for xml path('')), 1,1,'')DispositionCode
 from #test b)z
 pivot
 (max(DispositionCode) for  collectedhours in([7Hrs], [8Hrs], [9Hrs], [10Hrs], [11Hrs], [12Hrs], [13Hrs], [14Hrs], [15Hrs], [16Hrs], [17Hrs],
 [18Hrs], [19Hrs], [20Hrs], [21Hrs], [22Hrs]))as pvt