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?

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

Source (Stackoverflow)