Stored Procedure that returns productivity numbers over two shifts with the second shift that spans midnight, need to implement date range

• RickDowdall

  SSC Rookie

  Points: 32

  More actions

  February 8, 2016 at 2:05 pm

  #307852

  Hello Folks - I've been working on a stored procedure that will return productivity numbers by shift and by production line. Second shift was giving me a challange because it spans two days. 4:30pm to 1:00am, but I have that worked out. First shift is 7:30 to 4:00pm, Now I need to implement a date range. Due to the shift criteria being based on date I am not sure exactly how to implement the date range. I would appreciate some ideas in how I can accomplish this.

  USE [NomBaseMLT]

  GO

  /****** Object: StoredProcedure [dbo].[GetCustLoadByShiftandLine] Script Date: 2/8/2016 7:53:58 AM ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  --

  ALTER PROCEDURE [dbo].[GetCustLoadByShiftandLine]

  @shift integer,

  @line char,

  @beginDate datetime,

  @endDate datetime

  AS

  declare @ShiftStart datetime

  declare @ShiftEnd datetime

  declare @Shift2Start datetime

  declare @Shift2End datetime

  BEGIN

  set @ShiftStart = Convert(datetime,dateadd(day, datediff(day,0,convert(date,GetDate())),0) + '07:30.00')

  set @ShiftEnd = Convert(datetime,dateadd(day, datediff(day,0,convert(date,GetDate())),0) + '16:00.00')

  --set @ShiftEnd = dateadd(day, datediff(day,0,getDate()),0) + '16:00.00'

  set @Shift2Start = Convert(datetime,dateadd(day, datediff(day,0,convert(date,GetDate())),0) + '16:30.00')

  set @Shift2End = Convert(datetime,dateadd(day, datediff(day,0,getDate()+1),0) + '01:00.00')

  SET NOCOUNT ON;

  END

  BEGIN

  if @shift = 1

  select count(id) as records

  from NomBaseMLT.dbo.tableresmlt where C_Status in (0,72)

  and C_Site Like ('%SHMLT' + @line + '%')

  and DateEntered >= @ShiftStart and DateEntered <= @ShiftEnd

  ELSE IF @shift = 2

  select count(id) as records

  from NomBaseMLT.dbo.tableresmlt where C_Status in (0,72)

  and C_Site Like ('%SHMLT' + @line + '%')

  and DateEntered >= @Shift2Start and convert(date,DateEntered) <= @Shift2End

  END

  Thanks in Advance.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  February 8, 2016 at 2:19 pm

  #1857245

  Hi and welcome to SSC. Thanks for sharing the code you have that you have so far. What would be even more helpful is if you can share the ddl and sample data so we can recreate the problem on our end. Take a look at the first link in my signature for best practices when posting questions.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/