Table Valued Function to List Shifts in Date Range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Table Valued Function to List Shifts in Date Range

Author

Topic

therealrobstone
Starting Member

6 Posts

Posted - 2013-10-01 : 11:08:54

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-01 : 11:26:52

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.

therealrobstone
Starting Member

6 Posts

Posted - 2013-10-01 : 13:37:35

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-01 : 13:48:36

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?

therealrobstone
Starting Member

6 Posts

Posted - 2013-10-01 : 14:02:01

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-01 : 14:54:47

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

therealrobstone
Starting Member

6 Posts

Posted - 2013-10-02 : 10:46:12

Works perfectly. Thank you sir!

Regards,

Rob Stone

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-02 : 11:00:46

You are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources