SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Table Valued Function to List Shifts in Date Range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

therealrobstone
Starting Member

USA
6 Posts

Posted - 10/01/2013 :  11:08:54  Show Profile  Reply with Quote
I just recently got back into working with SQL programming after a 10 year hiatus. SO I am very rusty. I have to create a table valued function that will generate a list of shifts for a given date range.

Shifts at can be determined by the time of day following these rules:
11PM to 7AM = 3rd shift
7AM to 3PM = 1st shift
3PM to 11PM = 2nd shift
3rd shift technically begins the previous day. For instance, 3rd shift Monday actually starts at 11PM Sunday and continues until 7AM Monday.

Parameters:
@p_StartDT DATETIME
@p_EndDT DATETIME

Output columns:
Column / Data type/ Description
StartDT / DATETIME / Start date time of shift (i.e. 2013-01-01 07:00:00.000)
EndDT / DATETIME / End date time of shift (i.e. 2013-01-01 15:00:00.000)
ShiftNum / INT / Shift number (i.e. 1, 2, 3)
ShiftDesc / VARCHAR(10) / String description of shift (i.e. 1st, 2nd, 3rd)

The function should be inclusive. So if I pass a date range that covers several days, it should return all shifts that occurred in that date range.

What I have so far, works if I pass a date range for one day. But it does not work for a date range that includes multiple days:

USE [TraceSystem_DEV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ListShifts] (@p_StartDT DATETIME, @p_EndDT DATETIME)
RETURNS @list TABLE (StartDT DATETIME,
EndDT DATETIME, ShiftNum INT, ShiftDesc VARCHAR(10))
AS BEGIN
IF DATEPART(HOUR, @p_StartDT) >= 7 AND DATEPART(HOUR, @p_StartDT) < 15 OR DATEPART(HOUR, @p_EndDT) > 7 AND DATEPART(HOUR, @p_EndDT) < 15
BEGIN
INSERT INTO @list
(StartDT, EndDT, ShiftNum, ShiftDesc)
VALUES
(DATEADD(HOUR,07,CONVERT(VARCHAR(10),GETDATE(),110)), DATEADD(HOUR,15,CONVERT(VARCHAR(10),GETDATE(),110)), '1', '1st Shift')
END
IF DATEPART(HOUR, @p_StartDT) >= 15 AND DATEPART(HOUR, @p_StartDT) < 23 OR DATEPART(HOUR, @p_EndDT) > 15 AND DATEPART(HOUR, @p_EndDT) < 23

BEGIN

INSERT INTO @list
(StartDT, EndDT, ShiftNum, ShiftDesc)
VALUES
(DATEADD(HOUR,15,CONVERT(VARCHAR(10),GETDATE(),110)), DATEADD(HOUR,23,CONVERT(VARCHAR(10),GETDATE(),110)), '2', '2nd Shift')
END

IF DATEPART(HOUR, @p_StartDT) >= 23 OR DATEPART(HOUR, @p_StartDT) < 7 OR DATEPART(HOUR, @p_EndDT) > 23 OR DATEPART(HOUR, @p_EndDT) <= 7
BEGIN
INSERT INTO @list
(StartDT, EndDT, ShiftNum, ShiftDesc)
VALUES
(DATEADD(HOUR,23,CONVERT(VARCHAR(10),GETDATE(),110)), DATEADD(HOUR,7,CONVERT(VARCHAR(10),GETDATE(),110))+1, '3', '3rd Shift')
END

RETURN;
END
GO

---------------------------------------

Thanks for taking a look.

Regards,

Rob Stone

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 10/01/2013 :  11:26:52  Show Profile  Reply with Quote
For multiple days, you will need to use a numbers table of some sort. For testing, you can use spt_values table from master database as long as the number of days you are interested in is less than 2048.
DECLARE @p_StartDT DATETIME, @p_EndDT DATETIME;
SET @p_StartDT = '20130925';
SET @p_EndDT = '20131002';

SET @p_StartDT = CAST(@p_StartDT AS DATE); -- to remove any time portion
SET @p_EndDT = CAST(@p_EndDT AS DATE);

SELECT
	DATEADD(dd,number,@p_StartDT) AS [Date],
	DATEADD(hh,-1,DATEADD(dd,number,@p_StartDT)) [3S_Start],
	DATEADD(hh,7,DATEADD(dd,number,@p_StartDT)) [3S_End],
	DATEADD(hh,7,DATEADD(dd,number,@p_StartDT)) [1S_Start],
	DATEADD(hh,15,DATEADD(dd,number,@p_StartDT)) [1S_End],
	DATEADD(hh,15,DATEADD(dd,number,@p_StartDT)) [2S_Start],
	DATEADD(hh,23,DATEADD(dd,number,@p_StartDT)) [2S_End]
FROM
	master.dbo.spt_values 
WHERE
	type = 'P' AND number < DATEDIFF(dd,@p_StartDT,@p_EndDT)
ORDER BY
	[Date]
This is not exactly in the function form that you are looking for, but easy enough to convert to a function if you do need to.
Go to Top of Page

therealrobstone
Starting Member

USA
6 Posts

Posted - 10/01/2013 :  13:37:35  Show Profile  Reply with Quote
I took that and created this function:

USE [TraceSystem_DEV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ListShifts] (@p_StartDT DATETIME, @p_EndDT DATETIME)
RETURNS @list TABLE (ShiftsDate DATETIME, ThirdS_Start DATETIME, ThirdS_End DATETIME, FirstS_Start DATETIME, FirstS_End DATETIME, SecondS_Start DATETIME, SecondS_End DATETIME)
AS BEGIN

SET @p_StartDT = CAST(@p_StartDT AS DATE); -- to remove any time portion
SET @p_EndDT = CAST(@p_EndDT AS DATE);
INSERT INTO @list
SELECT
DATEADD(dd,number,@p_StartDT) AS [Date],
DATEADD(hh,-1,DATEADD(dd,number,@p_StartDT)) [ThirdS_Start],
DATEADD(hh,7,DATEADD(dd,number,@p_StartDT)) [ThirdS_End],
DATEADD(hh,7,DATEADD(dd,number,@p_StartDT)) [FirstS_Start],
DATEADD(hh,15,DATEADD(dd,number,@p_StartDT)) [FirstS_End],
DATEADD(hh,15,DATEADD(dd,number,@p_StartDT)) [SecondS_Start],
DATEADD(hh,23,DATEADD(dd,number,@p_StartDT)) [SecondS_End]
FROM
master.dbo.spt_values
WHERE
type = 'P' AND number < DATEDIFF(dd,@p_StartDT,@p_EndDT)
ORDER BY
[Date]
RETURN
END




GO

----------------------------------------------------------
Then I use this query:
USE [TraceSystem_DEV]
GO

SELECT * FROM [dbo].[ListShifts]
('2013-01-01 08:00:00.000', '2013-01-01 16:00:00.000')
GO
-------------------------------------------------
But it brings up no data, just the columns.

I left out the:
SET @p_StartDT = '20100925';
SET @p_EndDT = '20131002';

From your code because it returned all shifts in those dates instead of my parameters in the query.

What do I need to do to get this to work?

Thanks again for the help.

Regards,

Rob Stone


















Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 10/01/2013 :  13:48:36  Show Profile  Reply with Quote
Change the < to <= in the where clause
WHERE
type = 'P' AND number <= DATEDIFF(dd,@p_StartDT,@p_EndDT)
ORDER BY
Are you looking only for shifts that fall within the time part of the start and end dates as well, or for all shifts in the date range between start date and end date?
Go to Top of Page

therealrobstone
Starting Member

USA
6 Posts

Posted - 10/01/2013 :  14:02:01  Show Profile  Reply with Quote
quote:
Originally posted by James K

Change the < to <= in the where clause
WHERE
type = 'P' AND number <= DATEDIFF(dd,@p_StartDT,@p_EndDT)
ORDER BY
Are you looking only for shifts that fall within the time part of the start and end dates as well, or for all shifts in the date range between start date and end date?



Looking for shifts that fall within the time part of the start and end dates.

Thanks again!

Regards,

Rob Stone
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 10/01/2013 :  14:54:47  Show Profile  Reply with Quote
In that case, it might be better to have the signature that you originally proposed - with a shift number column, like shown below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ListShifts]
    (
      @p_StartDT DATETIME ,
      @p_EndDT DATETIME
    )
RETURNS @list TABLE
    (
      ShiftsDate DATETIME ,
      StartDT  DATETIME ,
      EndDT  DATETIME ,
      ShiftNum  INT
    )
AS 
    BEGIN 

        INSERT  INTO @list
                SELECT  DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),number) AS [Date] ,
                        DATEADD(hh, -1, DATEADD(dd, number, DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0))) [ThirdS_Start] ,
                        DATEADD(hh, 7, DATEADD(dd, number,DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0))) [ThirdS_End],
                        3
                FROM    master.dbo.spt_values
                WHERE   type = 'P'
                        AND number <= DATEDIFF(dd, @p_StartDT, @p_EndDT)
                        AND @p_EndDT >= DATEADD(hh, -1, DATEADD(dd, number, DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0)))
                        AND @p_StartDT <= DATEADD(hh, 7, DATEADD(dd, number,DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0)))

				UNION ALL
				
                SELECT  DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),number) AS [Date] ,
                        DATEADD(hh, 7, DATEADD(dd, number, DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0))),
                        DATEADD(hh, 15, DATEADD(dd, number,DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0))),
                        1
                FROM    master.dbo.spt_values
                WHERE   type = 'P'
                        AND number <= DATEDIFF(dd, @p_StartDT, @p_EndDT)
                        AND @p_EndDT >= DATEADD(hh, 7, DATEADD(dd, number, DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0)))
                        AND @p_StartDT <= DATEADD(hh, 15, DATEADD(dd, number,DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0)))

				UNION ALL

                SELECT  DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),number) AS [Date] ,
                        DATEADD(hh, 15, DATEADD(dd, number, DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0))),
                        DATEADD(hh, 23, DATEADD(dd, number,DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0))),
                        2
                FROM    master.dbo.spt_values
                WHERE   type = 'P'
                        AND number <= DATEDIFF(dd, @p_StartDT, @p_EndDT)
                        AND @p_EndDT >= DATEADD(hh, 15, DATEADD(dd, number, DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0)))
                        AND @p_StartDT <= DATEADD(hh, 23, DATEADD(dd, number,DATEADD(dd,DATEDIFF(dd,0,@p_StartDT),0)))     
        RETURN
    END
GO
Go to Top of Page

therealrobstone
Starting Member

USA
6 Posts

Posted - 10/02/2013 :  10:46:12  Show Profile  Reply with Quote
Works perfectly. Thank you sir!

Regards,

Rob Stone
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 10/02/2013 :  11:00:46  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000