How to retrieve records for last few hours in SQL Server

Question

theyI had a asp.net (vb) web application to store work overtime (OT) records.

In SQL server, the OT table likes this, e.g.:

ot_key | From Time        | To Time          | total_min
12     | 2011-09-22 10:00 | 2011-09-22 13:00 | 180
13     | 2011-09-24 14:00 | 2011-09-24 15:00 | 60
14     | 2011-09-23 12:00 | 2011-09-23 14:30 | 150
15     | 2011-09-24 18:00 | 2011-09-24 19:30 | 90

As user will input previous date OT records, so the records in db will not be in sequence. the date of records#14 is before records#13.

if user want to know which OT records cover the last 2 hours, the system should retrieve record #15 (90mins) & #13 (30mins) because they covers the final 2 hours.

How to write the SQL statement to retrieve the records ? Thanks

Joe

Your data looks strange : the ToTime for #13 is one day before the FromTime. Please check. — Louis, Sep 23 '11 at 07:41
What do you want exactly ? Just select the records with total_min lower than two hours ? — Louis, Sep 23 '11 at 07:42
Following the TDD mantra, a simple `SELECT * FROM OTTable WHERE total_min <= 120`. — Lieven Keersmaekers, Sep 23 '11 at 07:43
hi, louis, for your question "#13 is one day before the FromTime", it is because user will input previous date OT records, so the records will not be in sequence. — Joe Yan, Sep 23 '11 at 10:05
Sorry for confusing all of you. I had revised my question, hope it will be better. — Joe Yan, Sep 23 '11 at 10:15

score 1 · Answer 1 · answered Sep 23 '11 at 07:47

1

if I understand correctly, all you need to do is this (but its weird the way you phrased your question, so I'm not sure this what you are looking for.

 select * from OT where total_min <=
 [number of hours expressed in minutes]

answered Sep 23 '11 at 07:47

Icarus

63,293
14
100
115

This just gets the entries where the time difference is less than 2 hours, not what have been posted the past 2 hours. – Nicolai Sep 23 '11 at 07:54
@Nicolai, true but OP wants record 15 & 13 returned. Record 13 ends at 15:00 hours, record 15 at 19:30. This can never be returned for *the past two hours*. The requirements given by OP are wrong or at least incomplete. – Lieven Keersmaekers Sep 23 '11 at 07:59
Sorry for confusing all of you. I had revised my question, hope it will be better. – Joe Yan Sep 23 '11 at 10:14

score 1 · Answer 2 · edited May 23 '17 at 12:19

CREATE TABLE OT (
    [ot_key] INT,
    [From Time] DATETIME,
    [To Time] DATETIME,
    [total_min] INT
)

INSERT  OT
VALUES  (12,'2011-09-22 10:00', '2011-09-22 13:00', 180),
        (13, '2011-09-24 14:00', '2011-09-24 15:00', 60),
        (14, '2011-09-23 12:00', '2011-09-23 14:30', 150),
        (15, '2011-09-24 18:00', '2011-09-24 19:30', 90)

Query:

DECLARE @CoverTime INT = 120

;WITH cteOTRN AS (
    SELECT ROW_NUMBER() OVER (ORDER BY [To Time] DESC) AS [ROW_NUMBER], *
    FROM OT
)
, cteOTRT AS (
    SELECT *
    FROM cteOTRN ot
    CROSS APPLY (
        SELECT  SUM([total_min]) AS [RunningTotal]
        FROM cteOTRN
        WHERE [ROW_NUMBER] <= ot.[ROW_NUMBER]
    ) rt
)
SELECT *, [total_min] AS [CoverTime]
FROM cteOTRT ot
WHERE [RunningTotal] <= @CoverTime
UNION 
SELECT TOP 1 *, [RunningTotal] - @CoverTime
FROM cteOTRT ot
WHERE NOT ([RunningTotal] <= @CoverTime)
AND NOT EXISTS (
    SELECT *
    FROM cteOTRT ot
    WHERE [RunningTotal] = @CoverTime
)
ORDER BY [To Time] DESC

See also Calculate a Running Total in SQL Server

score 0 · Answer 3 · answered Sep 23 '11 at 07:51

0

Do a

select * from OT where datediff(hour,ToTime,getdate()) < 2

which will give you the posts where the difference between currenttime and totime is less than 2 hours.

answered Sep 23 '11 at 07:51

Nicolai

2,835
7
42
52

this may well be wat OP *intended* but it will not return records 13 & 15. – Lieven Keersmaekers Sep 23 '11 at 08:00
Sorry for confusing all of you. I had revised my question, hope it will be better. – Joe Yan Sep 23 '11 at 10:14

How to retrieve records for last few hours in SQL Server

3 Answers3