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
Table1

  • SELECT COUNT(DISTINCT ) from several tables
  • Error accessing Schema from web app over over local network
  • Connecting to Sql Server Database with hyphenated name
  • How get sum of count when using group by
  • Could not load file or assembly Microsoft.SqlServer.SqlClrProvider on production ASP.net site, where is it?
  • Getting Default Constraints Information
  • 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
    

    Table Product

    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

    select *
    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

    select *
    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.