CODEWITHSUNDEEP
asp.netsqlvb.netsql-server-2008
Joe Yan
Joe Yan

Reputation: 2095

How to retrieve records for last few hours in SQL Server

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

Upvotes: 0

Views: 1699

Answers (3)

Tom Hunter
Tom Hunter

Reputation: 5918

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

Upvotes: 1

Nicolai
Nicolai

Reputation: 2915

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.

Upvotes: 0

Icarus
Icarus

Reputation: 63956

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]

Upvotes: 1

Related Questions