how to set a specific time interval for different work shifts to retrieve data
I have two working shifts: 8:00:00 to 16:30:00 and 20:00:00 to 06:00:00.
I want to create a stored procedure that will retrieve data from an SQL table when I pass the date
this is my tables
ID DateTime EmpID 47 2014-12-05 08:00:00 1111 47 2014-12-05 08:25:00 1235 47 2014-12-05 23:55:00 4569 47 2014-12-06 00:00:00 4563 47 2014-12-06 02:00:00 7412 59 2014-12-06 04:00:00 8523 59 2014-12-05 10:30:00 5632
ID DateTime ProductMade 47 2014-12-05 11:00:00 Milk 47 2014-12-05 08:00:00 Juice 47 2014-12-06 00:00:00 Bread 47 2014-12-06 06:00:00 Cakes
query for shift 2 18:00 to 06:00
from Table 1 as T1 inner join Table_Product as Prod on t1.ID=Prod.ID
where T1.DateTime BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-8), 0) + ’18:00′
and DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-7), 0) + ’06:00′
so this will get all the records that has the same ID matching
then i have to do another query for the first shift.
between 08:00 to 16:30
from Table 1 as T1 inner join
Table_Product as Prod on t1.ID=Prod.ID
where DATEDIFF(day, CONVERT(VARCHAR(10), GETDATE(),110), CONVERT(VARCHAR(10), T1.DateTime,110))=-1
and DATEPART(HOUR,T1.DateTime) BETWEEN ’07’ AND ’16’
How do i make this into one stored procdure and elminate having two queries.
2 Solutions collect form web for “how to set a specific time interval for different work shifts to retrieve data”
Try this if you want it for a specific shift. Then you have to specify @Shift
Declare @Shift char(1), @days int Set @Shift = 'A' -- will only get information for SHIFT A. Change to B if you want the rest Set @days = 1 Select * from Table1 t where t.DateTime between case when @Shift = 'A' then DateAdd(hour, 8, Convert(date, GetDate() - @days)) else DateAdd(hour, 20, Convert(date, GetDate() - @days)) end and case when @Shift = 'A' then DateAdd(hour, 16, Convert(date, GetDate() - @days)) else DateAdd(hour, 30, Convert(date, GetDate() - @days)) end
Specify the Shift and a Date, and it should work.
You can always do something like this as well. This you only have to specify the number of days in the past, and it will retrieve the information and specify the Shift in the first Column
DECLARE @days int SET @days = 1 Select case when DATEPART(hour, t.DateTime) between 8 and 16 then 'A' else 'B' end AS Shift, * from Table1 t where t.DateTime between DateAdd(hour, 8, Convert(date, GetDate() - @days)) and DateAdd(hour, 30, Convert(date, GetDate() - @days)) ORDER BY 1, t.DateTime
It seems that you have two shifts per day and the day shift begins before the night shift. So, let’s enumerate the shifts and let you choose the one(s) you want that way:
select t.* from (select t.*, row_number() over (partition by cast(sp.datetime as date) order by sp.datetime ) as shiftnumber from table t ) t where DATEDIFF(day, CAST(GETDATE() as DATE), CAST(SP.DateTime as DATE)) = -1 and shiftnumber = 1;
Note that I also changed the date arithmetic. The conversion to dates uses the built-in
DATE type. Converting a date to a string and back to a date is inelegant.