CODEWITHSUNDEEP
sqlsql-server
phicon
phicon

Reputation: 3617

SQL Pivot Time Table Matrix

For insight in vehicle rental versus vehicle occupancy / empty times, i want to create a time matrix (for use in a SSAS Cube).

Below the available data for the rental part:

enter image description here

Now i would like to pivot this data to the following result. Showing the amount of minutes for vehicle rental per hour.

enter image description here

Any help on the correct SQL syntax to achieve this result would be great.

Upvotes: 2

Views: 308

Answers (2)

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

CREATE FUNCTION [dbo].GetTime (@Start TIME, @End TIME)
RETURNS @TableTime TABLE(_HH TIME, _MM INT)
AS
BEGIN
   WHILE DATEPART(HH, @Start) <= DATEPART(HH, @End)
   BEGIN
      INSERT INTO @TableTime
      SELECT DATEPART(HH, @Start), 60
      SET @Start = CAST(DATEADD(HH,1,@Start) AS TIME)
   END
   UPDATE @TableTime SET _MM = 60 - DATEPART(MINUTE, @Start) WHERE _HH = DATEPART(HH, @Start)
   UPDATE @TableTime SET _MM = DATEPART(MINUTE, @End) WHERE _HH = DATEPART(HH, @End)
END


SELECT DATE, VEHICLE CROSS APPLY GetTime(Starttime, Endtime) G

Upvotes: 0

shA.t
shA.t

Reputation: 16958

I think you can use CTE to collect 24 hours and use a CASE to achieve your expected result like this:

;WITH HoursAll(H) AS (
    SELECT 0 
    UNION ALL
    SELECT H + 1
    FROM HoursAll
    WHERE H < 23
)
SELECT t.date, t.vehicle, CAST((CAST(ha.H As varchar(10)) + ':00') As time) as [hour]
    , CASE 
        WHEN ha.H = DATEPART(HOUR,t.starttime) THEN 60 - DATEPART(MINUTE,t.starttime)
        WHEN ha.H = DATEPART(HOUR,t.endtime) THEN DATEPART(MINUTE,t.endtime)
        ELSE 60
    END
FROM HoursAll ha
JOIN yourTable t ON ha.H BETWEEN DATEPART(HOUR,t.starttime) AND DATEPART(HOUR,t.endtime);

Upvotes: 3

Related Questions