May 19, 2017 at 9:30 am
I need to be able to calculate the total number of days between 2 dates, but there is an added bit of complexity. There is a corresponding day mask that controls what days should be included in the calculation (eg MTWTFSSH, MT-TF--H)
H being 'include holidays'
Ive created some sql which does just what I want but im unable to build it into a function, only a stored procedure, but I cant execute this stored procedure inline with some sql. Heres my SP
[SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CalculateDayDifferenceWithDayMask]
@StartDate datetime,
@EndDate datetime,
@DayMask varchar(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- day mask includes/excludes the specifed days from the total caluculations
-- for example if Thursday isnt specifed, we dont include them in the total
--@DayMask example MTWTFSSH
-- MT--FSSH
-- MTWTF--H
SET NOCOUNT ON;
DROP TABLE #LocalTempTable
SET DATEFIRST 1
CREATE TABLE #LocalTempTable(DateOf DateTime,WeekDayName varchar(10),WeekDayNumber int)
;with AllDates AS
(
SELECT @StartDate AS DateOf, datename(weekday,@StartDate) AS WeekDayName, datepart(weekday,@StartDate) AS WeekDayNumber
UNION ALL
SELECT DateOf+1, datename(weekday,DateOf+1), datepart(weekday,DateOf+1)
FROM AllDates
WHERE DateOf<@EndDate
)
INSERT INTO #LocalTempTable (DateOf,WeekDayName,WeekDayNumber)
SELECT * FROM AllDates
--monday mask
IF SUBSTRING(@DayMask, 1, 1) = '-' DELETE FROM #LocalTempTable where WeekDayNumber = 1
--tuesday mask
IF SUBSTRING(@DayMask, 2, 1) = '-' DELETE FROM #LocalTempTable where WeekDayNumber = 2
--wednesday mask
IF SUBSTRING(@DayMask, 3, 1) = '-' DELETE FROM #LocalTempTable where WeekDayNumber = 3
--thursday mask
IF SUBSTRING(@DayMask, 4, 1) = '-' DELETE FROM #LocalTempTable where WeekDayNumber = 4
--friday mask
IF SUBSTRING(@DayMask, 5, 1) = '-' DELETE FROM #LocalTempTable where WeekDayNumber = 5
--saturday mask
IF SUBSTRING(@DayMask, 6, 1) = '-' DELETE FROM #LocalTempTable where WeekDayNumber = 6
--sunday mask
IF SUBSTRING(@DayMask, 7, 1) = '-' DELETE FROM #LocalTempTable where WeekDayNumber = 7
--holiday mask (if not H delete where exists in ukHoliday table
--this table MUST be kept up to date, as of today (19-may-2017 ive added holidays up to 26/12/2030)
IF SUBSTRING(@DayMask, 7, 1) = '-' DELETE FROM #LocalTempTable where DateOf IN (SELECT HolidayDate FROM dbo.UKPublicHolidays)
---SELECT COUNT(*) CountOf,WeekDayName FROM #LocalTempTable GROUP BY WeekDayName,WeekDayNumber ORDER BY WeekDayNumber
SELECT COUNT(*) FROM #LocalTempTable
END]
Ideally Id like call this as a function but I get errors when I try to create this code inside one (invalid use of side-effecting operator delete within a function)
so i created it as a stored procedure, how do i call a stored procedure from within a sql statement, something like
select locks.BlockStart,
locks.BlockEndDate,
exec DateCalculateDayDifferenceWithDayMask(locks.BlockStartDate,locks.BlockEndDate,locks.DayMask) as days from tablename
this is part of a larger sql statement so ive simplified it to make it easier to read. am I going about this the wrong way ? this is a pretty complex requirement and I cant really see any other way around it, is this even possible ?
May 19, 2017 at 9:54 am
Maybe create a function that parses the mask and returns a table of day numbers. That table can be joined to any query to return just the days you want.
Handling the holidays is a special case and will always need to be joined if the H is included in the mask.
May 19, 2017 at 10:10 am
CREATE function dbo.MaskWeekdays
(
@Mask varchar(8)
)
returns
@DayNumbers TABLE (DayNumber int not null)
as
BEGIN
if SUBSTRING(@Mask,1,1) = 'M' INSERT INTO @DayNumbers (DayNumber) VALUES(2);
if SUBSTRING(@Mask,2,1) = 'T' INSERT INTO @DayNumbers VALUES(3);
if SUBSTRING(@Mask,3,1) = 'W' INSERT INTO @DayNumbers VALUES(4);
if SUBSTRING(@Mask,4,1) = 'T' INSERT INTO @DayNumbers VALUES(5);
if SUBSTRING(@Mask,5,1) = 'F' INSERT INTO @DayNumbers VALUES(6);
if SUBSTRING(@Mask,6,1) = 'S' INSERT INTO @DayNumbers VALUES(7);
if SUBSTRING(@Mask,7,1) = 'S' INSERT INTO @DayNumbers VALUES(1);
RETURN
END
GO
SELECT *
FROM sys.objects
WHERE DATEPART(WEEKDAY, Create_date)
IN (select DayNumber FROM dbo.MaskWeekdays('MTW--SSH'))
;
May 23, 2017 at 4:15 am
I ended up putting the code inside a table valued function then using (select count from functionname) which has done the trick
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply