Joe - 1 year ago 112
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`

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download