sql - 如何设置不同时间间隔的值？

Question

我有一个如下表格

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

最终输出为

收集者 1 在 8.00 和 8.20 进行了两次处置。因此，在 8 小时专栏中，他的处置代码可用。他还在 9.10 做出了一项处置，在 9 小时栏，他的处置代码可用。对于 Collector 2 也是如此。

到目前为止我已经尝试过

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

在那之后我迷失了。

Answer 1

相同的方法，稍作修改:

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