I'm trying to subtract extra hours from a row when the total hours > 10 and add the subtracted hours to the next rows. For example, the data table looks like this:

ID            StartTime                EndTime            Type   Rate   Location
123    2016-03-03 22:00:00.000  2016-03-04 10:00:00.000    B     2.5      VA
123    2016-03-04 10:00:00.000  2016-03-04 20:00:00.000    A     2.5      VA

As long as the IDs are the same, if the total hours > 10 and the type is B, I'd like to subtract the extra hours and add it to the next row. Other column values are just the same. For example, since the first row has 12 hours and the type is B, I want to subtract 2 hours from the first row and add the two hours to the next row. So the final data will look like this:

ID            StartTime                EndTime            Type   Rate   Location
123    2016-03-03 22:00:00.000  2016-03-04 08:00:00.000    B     2.5      VA
123    2016-03-04 08:00:00.000  2016-03-04 20:00:00.000    A     2.5      VA

You can see the EndTime of the first row and the StartTime of the second row have been adjusted. I tried this but couldn't really figure it out.

select 
   case when Type = 'B' and DATEDIFF(HOUR, StartTime , EndTime) > 10
   then DATEADD(HOUR, DATEDIFF(HOUR, StartTime , EndTime) ,EndTime) else EndTime as EndTime,

I believe the current code is not accurate and couldn't really figure out how to add the subtracted hours to the next row.

Thank you.

CodePudding user response：

This is fairly trivial - if a little ugly - with the help of the lag window function, which can look back within a defined window of data and use the values held in other rows.

Given the ambiguity in what your actual logic is, I have added two versions of the calc required, for which you can see the different output against ID = 124.

Query

declare @t table(ID int,StartTime datetime,EndTime datetime,[Type] varchar(10),Rate decimal(10,2),[Location] varchar(10));
insert into @t values
 (123,'2016-03-03 22:00:00.000','2016-03-04 10:00:00.000','B',2.5,'VA')
,(123,'2016-03-04 10:00:00.000','2016-03-04 20:00:00.000','A',2.5,'VA')
,(124,'2016-03-03 01:00:00.000','2016-03-03 12:00:00.000','C',2.5,'VA')
,(124,'2016-03-03 12:00:00.000','2016-03-04 10:00:00.000','B',2.5,'VA')
,(124,'2016-03-04 11:00:00.000','2016-03-04 11:00:00.000','A',2.5,'VA')
,(125,'2016-03-02 12:00:00.000','2016-03-03 10:00:00.000','A',2.5,'VA')
,(125,'2016-03-03 10:00:00.000','2016-03-03 11:00:00.000','A',2.5,'VA')
,(126,'2016-03-03 08:00:00.000','2016-03-03 10:00:00.000','B',2.5,'VA')
,(126,'2016-03-03 10:00:00.000','2016-03-03 11:00:00.000','A',2.5,'VA')
;

select ID

      ,case when lag(Type) over (partition by ID order by StartTime) = 'B'
             and datediff(minute
                         ,lag(StartTime) over (partition by ID order by StartTime)
                         ,lag(EndTime) over (partition by ID order by StartTime)
                         ) >= 600
            then dateadd(hour,10,lag(StartTime) over (partition by ID order by StartTime))
            else StartTime
            end as StartTimeContiguous

      ,case when lag(Type) over (partition by ID order by StartTime) = 'B'
             and datediff(minute
                         ,lag(StartTime) over (partition by ID order by StartTime)
                         ,lag(EndTime) over (partition by ID order by StartTime)
                         ) >= 600
            then dateadd(minute
                        ,600 - datediff(minute
                                       ,lag(StartTime) over (partition by ID order by StartTime)
                                       ,lag(EndTime) over (partition by ID order by StartTime)
                                       )
                        ,StartTime
                        )
            else StartTime
            end as StartTimeSubtraction

      ,case when Type = 'B' and datediff(minute,StartTime,EndTime) >= 600
            then dateadd(hour,10,StartTime)
            else EndTime
            end as EndTime
      ,[Type]
      ,Rate
      ,[Location]
from @t
order by ID
        ,StartTime;

Output

CodePudding user response：

If I have not missunderstood you, this would be what you want:

with cte 
as
(
  select 
  row_number() over (order by ID, Type desc) rn,
  *,
  datediff(s, StartTime, EndTime) - (60 * 60 * 10) DiffSeconds
  from MyTable
),
final 
as
(
select 
  *,
  sum(DiffSeconds) over (PARTITION BY ID ORDER BY ID, Type DESC) SumDiffSeconds 
  from cte
)
select 
  ID,
  case
    when SumDiffSeconds > 0 and Type != 'B' then dateadd(s, -SumDiffSeconds, StartTime) 
    else StartTime 
  end NewStartTime,
  case
    when SumDiffSeconds > 0 and Type = 'B' then dateadd(s, -SumDiffSeconds, EndTime) 
    else EndTime 
  end NewEndTime,
  Type, Rate, Location
from final

If it is not, please provide more sample data and expected results, and I will adjust the select.

DbFiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cb511db70f8822e1e184aa1a3cceca01

Results are: