How to aggregate report on the number of days trip for vehicle

Question

Hi All,

SQL version:

Lets say I have the database table consist of vehicle trip.

The vehicle trip have start and end for every trip done by the vehicle. How can I get the number of days trip for vehicle done based on the trip done by vehicle. I have done some SQL query based on study but seems like my query need some changes to get the expected result. The expected result should be:

REGISTRATIONNUMBER tripdays

NDS7297 1

NDT2877 3

But I get like below after run the query

REGISTRATIONNUMBER tripdays

NDS7297 1

NDT2877 4

-- DDL and sample data population, start
DECLARE @tbl TABLE (REGISTRATIONNUMBER varchar(255), TRIPSTART datetime, TRIPEND datetime, 
MAXSPEED float, IMPACT_COUNT int, TRIP_ID int)

INSERT INTO @tbl (REGISTRATIONNUMBER, TRIPSTART, TRIPEND, MAXSPEED, IMPACT_COUNT, TRIP_ID) VALUES
('NDS7297', '2022-10-30 08:59', '2022-10-30 09:09', 73, 0, 1658361240),
('NDS7297', '2022-10-30 09:29', '2022-10-30 09:37', 84, 0, 1658377595),
('NDT2877', '2022-11-03 11:31', '2022-11-05 11:56', 0, 0, 1658471861),
('NDT2877', '2022-11-05 11:31', '2022-11-05 15:56', 0, 0, 1658471862),
('NDT2877', '2022-11-05 15:56', '2022-11-05 17:56', 0, 0, 1658471863)

;with T1 as(
  select REGISTRATIONNUMBER,CONVERT(varchar(100),TRIPSTART,111) as TRIPSTART,
    CONVERT(varchar(100), TRIPEND,111) as TRIPEND from @tbl
  group by REGISTRATIONNUMBER,CONVERT(varchar(100),TRIPSTART,111),CONVERT(varchar(100), TRIPEND,111)
),T2 as(
  select REGISTRATIONNUMBER,sum(DATEDIFF(day,TRIPSTART, TRIPEND) + 1) as tripdays from T1 group by REGISTRATIONNUMBER
)

SELECT * FROM T2

Answer 1

If gaps must be excluded, then try something like this:

;
with Q1 as
(
    select REGISTRATIONNUMBER, cast(TRIPSTART as date) d, cast(TRIPEND as date) as TRIPEND
    from @tbl
    union all
    select REGISTRATIONNUMBER, dateadd(day, 1, d), TRIPEND 
    from Q1
    where d < TRIPEND
),
Q2 as 
(
    select distinct REGISTRATIONNUMBER, d
    from Q1
)
select REGISTRATIONNUMBER, count(*) as tripdays 
from Q2
group by REGISTRATIONNUMBER
option (maxrecursion 0)

Answer 2

Maybe like this:

select REGISTRATIONNUMBER, datediff( day, min(tripstart), max(tripend)) + 1 as tripdays
from @tbl
group by REGISTRATIONNUMBER

It will include gaps too.

Answer 3

Hi @jn93

You need to add a CASE WHEN condition inside the SUM function. Please check this:

;with T1 as(
  select REGISTRATIONNUMBER,CONVERT(varchar(100),TRIPSTART,111) as TRIPSTART,
         CONVERT(varchar(100), TRIPEND,111) as TRIPEND 
  from @tbl
  group by REGISTRATIONNUMBER,CONVERT(varchar(100),TRIPSTART,111),CONVERT(varchar(100), TRIPEND,111)
),T2 AS( 
  SELECT *,LAG(TRIPEND,1,TRIPEND)OVER(PARTITION BY REGISTRATIONNUMBER ORDER BY TRIPEND) AS Prev_TRIPEND
  FROM T1
)
,T3 as
(
  select REGISTRATIONNUMBER,
         sum(CASE WHEN TRIPSTART = Prev_TRIPEND
		          THEN DATEDIFF(day,TRIPSTART, TRIPEND) 
		          ELSE DATEDIFF(day,TRIPSTART, TRIPEND) + 1 END )as tripdays 
  from T2 
  group by REGISTRATIONNUMBER
)
SELECT * FROM T3

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.