Joe Joe - 21 days ago 7
SQL Question

SQL calculate points between a time range

I’ve a table which contains different time ranges:

Id Start Time End Time Points
1 0:00 3:00 10
2 3:01 6:00 20
3 6:01 23:59 30


Now I need to calculate the points achieved between two date ranges with respect to time specified.

Start date = 11/9/2016 18:17:00 and
End date = 11/10/2016 01:20:00


I need to calculate the sum of points gained between these two dates.
The time of start date that is 18:17 falls under Id 3, whose point is 30. So the calculation will be,

18:17 to 23:59 -> 6 hrs -> 6 * 30 = 180 points


The end time 01:20 falls under Id 1

0:00 to 1:20 -> 2 hrs
(if minute is greater than zero, it is rounded to next hour, ie; 2) -> 2 * 10 = 20 points


So the total points gained will be 200 points.
Taking the time difference, does not help me, if the start and end date difference is greater than one day.

Table Structure:
Id - int,
StartTime - time(7),
EndTime - time(7),
Points - int

How to write a query for this using SQL?

Answer

This question was good.

You can as the below:

DECLARE @Tbl TABLE (Id INT, StartTime TIME, EndTime TIME, Points INT)
INSERT INTO @Tbl
VALUES                 
(1,   '0:00',     '3:00' ,       10),
(2,   '3:01',     '6:00' ,       20),
(3,   '6:01',     '23:59',       30)


DECLARE @StartDate DATETIME = '2016.11.09 18:17:00'
DECLARE @EndDate DATETIME = '2016.11.10 01:20:00'


;WITH CTE
AS
(
    SELECT 1 AS RowId, @StartDate CurrentDate, 0 Point, @StartDate DateVal UNION ALL
    SELECT
        A.RowId ,
        IIF((A.CurrentDate + A.EndTime) > @EndDate, @EndDate, DATEADD(MINUTE, 1, (A.CurrentDate + A.EndTime))) AS CurrentDate,
        A.Points,
        IIF((A.CurrentDate + A.EndTime) > @EndDate, @EndDate, (A.CurrentDate + A.EndTime)) DateVal
    FROM
    (
        SELECT 
            C.RowId + 1 AS RowId,  
            CAST(CAST(CurrentDate AS DATE) AS DATETIME) CurrentDate,
            CAST((SELECT T.EndTime FROM @Tbl T WHERE CAST(CurrentDate AS TIME) BETWEEN T.StartTime AND T.EndTime) AS DATETIME) AS EndTime,
                (SELECT T.Points FROM @Tbl T WHERE CAST(CurrentDate AS TIME) BETWEEN T.StartTime AND T.EndTime) AS Points,
            C.CurrentDate AS TempDate
         FROM CTE C         
    ) A
    WHERE           
        A.TempDate <> IIF((A.CurrentDate + A.EndTime) > @EndDate, @EndDate, DATEADD(MINUTE, 1, (A.CurrentDate + A.EndTime)))
), CTE2
AS
(
    SELECT
        C.RowId ,
        C.CurrentDate ,
        C.Point ,
        C.DateVal,
        DATEDIFF(MINUTE, LAG(C.DateVal) OVER (ORDER BY C.RowId), C.DateVal) MinuteOfDateDiff
    FROM
        CTE C
)


 SELECT 
    SUM(CEILING(C.MinuteOfDateDiff * 1.0 / 60.0) * C.Point)
 FROM 
    CTE2 C

Result: 200

Comments