How to get utilization of days per month in SQL?

jn93 651 Reputation points
2023-02-28T07:13:13.95+00:00

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:

User's image

-- 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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,126 questions
0 comments No comments
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-02-28T08:38:16.66+00:00

    Hi @jn93

    User's image

    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:

    User's image

    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".

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful