How cen i get a time between two dates when they are not in the same day?

Question

Hello, i was searching in the past questians and couldn't find what i was looking for, In my web app i need to get all the records in order table where the orders where orders in a certian shift:

1. A shift has an openning date only and a record id in shiftTypes.

2. shiftTypes holds the time of start and ending of a shift(implicitly)

Now, people are working with the system all the time and they could enter an order yestorday morning and today morning.

Some shift are over night, so some orders in that shift are in one day and some in the other.

My problem is that when i am trying to get only the orders in a shift, i'm getting beck all the records in both days in the time frame of a shift(by shift type) but for the wrong shift also (The one that was yestorday night for example)...ofcourse that happen, and can onlyu happen for shifts that are over night and extends over two diferent days, becouse both days has the same hours in them....

How can i get only the records that are on my shift? p.s. by shiftId is not working....

----DEMO: spShiftCloseZ @Date='2010-10-11'
alter procedure spShiftCloseZ
@Date   date
as
declare @ShiftID smallint
declare @ShiftDate date
declare @StartTime time(7)
declare @EndTime time(7)
set @ShiftID = (select top 1 ShiftID from dbo.Shifts order by ShiftID desc)
set @ShiftDate = (select ShiftDate from dbo.Shifts where ShiftID = @ShiftID)
set @StartTime = (select StartTime from dbo.Shifts s,dbo.ShiftTypes st 
                where s.ShiftTypeID=st.ShiftTypeID and ShiftID = @ShiftID)
set @EndTime = (select EndTime from dbo.Shifts s,dbo.ShiftTypes st 
                where s.ShiftTypeID=st.ShiftTypeID and ShiftID = @ShiftID)

select  OrderID,    
        Total       

from    dbo.Orders
where   OrderDate between @ShiftDate and @Date
        --and
        --OpenTime between @StartTime and @EndTime


select      SUM(NumOfDiners) as NumOfDiners,
            SUM(Total) as TotalAmount,
            OrderDate

from    dbo.Orders
where   OrderDate between @ShiftDate and @Date
        and
        OpenTime between @StartTime and @EndTime
group by    OrderDate

10 :-)

Answer 1

OK, Got it!!!

alter procedure spShiftCloseZ
@Date   date
as
declare @ShiftID smallint
declare @ShiftDate date
declare @StartTime time(7)
declare @EndTime time(7)
set @ShiftID = (select top 1 ShiftID from dbo.Shifts order by ShiftID desc)
set @ShiftDate = (select ShiftDate from dbo.Shifts where ShiftID = @ShiftID)
set @StartTime = (select StartTime from dbo.Shifts s,dbo.ShiftTypes st 
                where s.ShiftTypeID=st.ShiftTypeID and ShiftID = @ShiftID)
set @EndTime = (select EndTime from dbo.Shifts s,dbo.ShiftTypes st 
                where s.ShiftTypeID=st.ShiftTypeID and ShiftID = @ShiftID)

select  OrderID,    
        Total       

from    dbo.Orders
where   OrderDate =  @Date
        and
        OpenTime < @StartTime
        or
        OrderDate = @ShiftDate
        and
        OpenTime > @StartTime


select      SUM(NumOfDiners) as NumOfDiners,
            SUM(Total) as TotalAmount,
            OrderDate

from    dbo.Orders
where   OrderDate between @ShiftDate and @Date
        and
        OpenTime between @StartTime and @EndTime
group by    OrderDate

This is working the way i want it.... 10x :-)

Answer 2

You should create starting and ending datetime values, if you have the date and time separate you have to do much more complicated comparisons to make it work.

If a shift starts at 22:00 one day and runs to 06:00 the next day, you want all the records for the first date between 22:00 and 24:00, and for the second date between 00:00 and 06:00. It's easier if you combine the date and time so that you have one range that applies to all records instead of two ranges that applies differently depending on the date.

I'm not sure exactly how you calculate and use the @ShiftDate and @Date values (perhaps you should compare the @StartTime and @EndTime to determine if the shift passes midnight), but the principle would be:

where OrderDate + OpenTime between @ShiftDate + @StartTime and @Date + @EndTime

Tip: You can use @var = field in a query, so you don't need a separate query for each variable that you want to get:

select
  @ShiftDate = s.ShiftDate,
  @StartTime = st.StartTime,
  @EndTime = st.EndTime
from dbo.Shifts s
inner join dbo.ShiftTypes st on s.ShiftTypeID = st.ShiftTypeID
where s.ShiftID = @ShiftID