Geek asks and answers

Time Range - Sql

please help me with my problem. So, I have a table called "RATES" that contains these columns:

id (int) 
rate (money) 
start_time (datetime) 
end_time(datetime)

data examples:

1 150 8:00am 6:00pm 
2 200 6:00pm 4:00am
3 250 8:00am 4:00am (the next day)

What I need to do is select all the id (s) at which a certain time will drop out.

for example, the set time: 9:00 pm, the output should be 2.3

The problem is that the next day I get this interval between 8am and 4am, and I don't know what to do. Please help me! thanks in advance: D

+3
source share
3 answers

Assuming @Andriy M is correct:

  • Data never spans more than 24 hours.
  • if end_time<= start_time, then end_timebelongs to the next day
what you are looking for is:
Declare @GivenTime DateTime
Set @GivenTime = '9:00 PM'
Select ID
  From Rates
 Where (Start_Time<End_Time And Start_Time<=@GivenTime And End_Time>=@GivenTime)
    Or (Start_Time=End_Time And Start_Time=@GivenTime)
    Or (Start_Time>End_Time And (Start_Time>=@GivenTime Or End_Time<=@GivenTime))
+3
source

MS SQL, , , .

- , , , , .

SELECT id FROM RATES WHERE datepart(hh, start_time) <= 9 AND datepart(hh, end_time) >= 9;

, , .

SELECT id FROM RATES WHERE start_time <= '2011-1-1 9:00' AND end_time >= '2011-1-1 9:00';

, .

0

, @gbn . .

- , timedata, ,

WITH normalized AS
(
   SELECT * 
   FROM timedata
   WHERE datepart(day,start_time) = datepart(day,endtime)
   UNION ALL
   SELECT id, rate, start_time, dateadd(second,dateadd(day,datediff(day,0,end_time),0),-1) as end_time 
   FROM timedata
   WHERE not (datepart(day,start_time) = datepart(day,endtime))
   UNION ALL
   SELECT id, rate,dateadd(day,datediff(day,0,end_time),0) as start_time, end_time
   FROM timedata
   WHERE not (datepart(day,start_time) = datepart(day,endtime))
)
SELECT * 
FROM normalized
WHERE datepart(hour,start_time) < @inhour
  AND datepart(hour,end_time) > @inhour

CTE . , : SQL-

, :

,

  • , .

,

  • Selects a start time and one second until the next day as the end time for this entire range.

and

  • Select the date 12 days at the endpoint as start time and end_time.

Finally, you make the selection using the hour indicator in this normalized table.

If your ranges go beyond more than one day, you will need to use a recursive CTE to get the same normalized table.

0
source

More articles:


All Articles