How to get utilization of days per month in SQL?

Question

Hi All, lets say I have sets of data like below. I have develop the query but I didn't get the expected output for the field No. of day vehicle is used/ No. of day in the month . How can I get the utilization of days per month in SQL as per shown in expected output below? I do have copy the sample data and query that need for modification.

Expected Output:

-- 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
('NDT2877', '2022-10-30 10:59', '2022-10-30 11:08', 0, 0, 1658435825),
('NDT2877', '2022-10-30 11:31', '2022-10-30 11:56', 0, 0, 1658471862),
('NDT2877', '2022-10-30 14:00', '2022-10-30 14:05', 0, 0, 1658616033),
('NDT2877', '2022-10-30 14:07', '2022-10-30 14:23', 63, 0, 1658645830),
('NDT2877', '2022-10-30 15:11', '2022-10-30 15:18', 0, 0, 1658747446),
('NDT2877', '2022-10-30 15:36', '2022-10-30 15:45', 0, 0, 1658803244),
('NDT2877', '2022-10-30 15:48', '2022-10-30 16:00', 0, 0, 1658835422),
('NDT2877', '2022-10-31 11:35', '2022-10-31 11:49', 0, 0, 1660392094),
('NDT2877', '2022-10-31 11:51', '2022-10-31 11:59', 0, 0, 1660403070),
('NDT2877', '2022-10-31 14:48', '2022-10-31 15:00', 0, 0, 1660827024),
('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-01 10:59', '2022-11-02 11:08', 0, 0, 1658435826),
('NDT2877', '2022-11-03 11:31', '2022-11-04 11:56', 0, 0, 1658471861)


SELECT 
    REGISTRATIONNUMBER AS [Row Labels],
    COUNT(TRIP_ID) AS [Count of TRIP_ID],
    MAX(MAXSPEED) AS [Max of MAXSPEED],
	SUM(DATEDIFF(day, TRIPSTART, TRIPEND) + 1) * 100.0 / DAY(EOMONTH(MIN(CAST(TRIPSTART AS date)))) AS 'No. of day vehicle is used/ No. of day in the month'
	--TO RETURN # OF DAYS OF END OF MONTH(EOM)..MAX/MIN TO GET ANY DATE FROM TRIPSTART
	--CAST IS TO CONVERT DATETIME TO A DATE DATATYPE
	
FROM @tbl
GROUP BY REGISTRATIONNUMBER

Answer 1

Hi @jn93

Are these two rows of data reversed?

According to your statement, I thought for a while.

The 'No. of day vehicle is used/ No. of day in the month' column should be the number of days used divided by the total number of days. 'NDS7297' should have been used for a total of one day, while 'NDT2877' should have been used for a total of six days.

So I designed this query, you can try it.

;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
),T3 as(
  select REGISTRATIONNUMBER as [Row Labels],
    COUNT(TRIP_ID) as [Count of TRIP_ID],
    MAX(MAXSPEED) as [Max of MAXSPEED],
	DAY(EOMONTH(MIN(CAST(TRIPSTART AS date)))) as monthdays FROM tbl GROUP BY REGISTRATIONNUMBER)
select [Row Labels],[Count of TRIP_ID],[Max of MAXSPEED],
    cast(A.tripdays as float)/B.monthdays as 'No. of day vehicle is used/ No. of day in the month' 
from T2 as A inner join T3 as B on A.REGISTRATIONNUMBER = B.[Row Labels];

Output:

Best regards,

Percy Tang

---

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