All geek questions in one place

How to set values ​​with different time intervals?

I have a table 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 end result will be

enter image description here

So, collector 1 made two arrangements at 8.00 and 8.20 hours. So for column 8hrs, its delete code is available. In addition, he made one order at 9.10 a.m., for which a column was available for his order for 9 hours. Similarly for Collector 2.

What have i 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 got lost after that.

Help needed.

+4
source share
2 answers

The same can be achieved using PIVOT . I believe that the feedback date may include all daily entries. Demo

SELECT *
FROM 
(
    SELECT    CollectorId, 
              DispositionCode = (STUFF((
              SELECT ', ' + CONVERT(VARCHAR(10), t2.[DispositionCode], 103)
              FROM   @T as t2
              WHERE  t2.[CollectorId] = t.[CollectorId]
              AND   CAST(DATEPART(HOUR, t2.FeedbackDate) as VARCHAR(10)) = CAST(DATEPART(HOUR, t.FeedbackDate) as VARCHAR(10))
              FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')), 
              CAST(DATEPART(HOUR, FeedbackDate) as VARCHAR(10)) + 'Hrs' AS SearchHour
              FROM @T as t          
              GROUP BY  CollectorId, CAST(DATEPART(HOUR, FeedbackDate) as VARCHAR(10)) 
) PivotSource
 PIVOT (MAX([DispositionCode]) FOR SearchHour IN 
         ( [0Hrs],  [1Hrs],  [2Hrs],  [3Hrs],  [4Hrs],  [5Hrs], 
           [6Hrs],  [7Hrs],  [8Hrs],  [9Hrs], [10Hrs], [11Hrs], 
           [12Hrs], [13Hrs], [14Hrs], [15Hrs], [16Hrs], [17Hrs], 
           [18Hrs], [19Hrs], [20Hrs], [21Hrs], [22Hrs], [23Hrs])) as pvt
+4

:

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
+5

Source: https://habr.com/ru/post/1684185/

More articles:


All Articles