find records from current shift 1

[sedgely]

i have a query that returns records with a callTime in the last 24 hours. what i am trying to do is determine which of these records has a calltime within the current shift (shift start times are 06:00 and 18:00)

so...
if the current time is 11:10 all those records with a calltime greater than today @ 06:00
if the current time is 05:30 all those records with a callTime greater than yesterday @ 18:00

hope that makes sense

any ideas?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[simian336]

Try this out.... You will need to change the fields and tables. I used one I had.


DECLARE @first datetime;
declare @second datetime;
declare @Yday datetime;
SET @first = dateadd(dd,0, datediff(dd,0,GETDATE()))
SET @second = dateadd(dd,0, datediff(dd,0,GETDATE()))
SET @Yday = dateadd(dd,-1, datediff(dd,0,GETDATE()))
SET @first = dateadd(hh,6, @first)
SET @second = dateadd(hh,18, @second)
SET @Yday = dateadd(hh,18, @Yday)
SELECT @first, @second, @Yday as yday, getdate()

if Getdate() > @first
begin
select *
from reportperiod
where savedon between @first and @second
order by savedon
print 'first'
end
else
begin
select *
from reportperiod
where savedon between @Yday and @first
order by savedon
print 'first'
end


Simi

 

[sedgely]

Thanks Simi
That is exactly what I needed

Cheers, Craig
Si fractum non sit, noli id reficere