CODEWITHSUNDEEP
t-sqlsql-server-2012
Capanga
Capanga

Reputation: 77

How to accumulate values tsql

I have to solve a problem and don't know how to do it. Im using SQL Server 2012.

I have the data like this schema:

 -----------------------------------------------------------------------------------
   DriverId  | BeginDate | EndDate | NextBegin | Rest in | Drive Time | Drive
             |           |         | Date      | Hours   | in Minutes | KM            
 -----------------------------------------------------------------------------------
    integer     datetime   datetime   datetime    integer    integer     decimal(10,3)

 Rest in hours = EndDate - NextBeginDate
 Drive Time in Minutes = BeginDate - EndDate

I have to search the first rest => 36 hours then

 Do
   Compute how many days are 
   SUM(DriveTime) 
   SUM(TotalKM)
 until next rest => 36 hours
 IF No More Rest EXIT DO
 Loop

From the begining to the first Rest is discard From the last Rest to the end is discard

I have data in excel sheet you can download from here: Download Excel with data example

I'm sorry for my english, I hope you can understand and help me, thank you in advance.

Upvotes: 0

Views: 137

Answers (1)

Jerrad
Jerrad

Reputation: 5290

There are several parts to the query. The first part pulls out the rows where Rest is >= 36 and assigns a row number. The result is stored in a CTE called BigRest.

with BigRest(RowNumber, DriverId, BeginDate, EndDate)
as
(
select ROW_NUMBER() over(partition by d.DriverId order by d.DriverId, d.BeginDate) RowNumber,d.DriverId, d.BeginDate, d.EndDate
from Drive d 
where d.Rest >= 36
)

Then I assign the row number from BigRest to each row in Drive (which is what I'm calling the table that has all the data in it) based on the BeginDate. So the data is effectively segmented by the days where Rest >= 36. Each segment gets a number called DriveGroup.

;with Grouped(DriverId, BeginDate, EndDate, DriveTime, DriveKM, DriveGroup)
as
(
select d.DriverId, d.BeginDate, d.EndDate, d.Drivetime, d.DriveKM, (select Top 1 RowNumber from BigRest b where b.DriverId = d.DriverId and b.BeginDate >= d.BeginDate order by b.BeginDate)
from Drive d 
)

Finally, I select the data from Grouped, cross applying it with some aggregate data from itself. We can filter out the rows where the DriveGroup is 1 or null because those represent the beginning and end rows that don't matter (the "do nothing" rows).

select distinct DriverId, MinBeginDate BeginDate, MaxEndDate EndDate, DATEDIFF(D, MinBeginDate, MaxEndDate)+1 Days, DriveTimeSum Drive, DriveKMSum KM
from
(
select g.DriverId, g.BeginDate, g.EndDate, g.DriveGroup, g.DriveTime, c.DriveTimeSum, c.DriveKMSum, c.MinBeginDate, c.MaxEndDate
from Grouped g
cross apply(select SUM(g2.DriveTime) DriveTimeSum, 
               SUM(g2.DriveKM) DriveKMSum, 
               MIN(g2.BeginDate) MinBeginDate, 
               MAX(g2.EndDate) MaxEndDate 
               from Grouped g2 
               where g2.DriverId = g.DriverId 
               and g2.DriveGroup = g.DriveGroup) as c
where g.DriveGroup is not null
and g.DriveGroup > 1
) x

Here's a SQL Fiddle

I'd encourage you to look at the results at each step of the query to see what's actually going on.

Upvotes: 1

Related Questions