calculate the number of days between 2 dates but with an include/exclude day mask

• solus

  Hall of Fame

  Points: 3495

  More actions

  May 19, 2017 at 9:30 am

  #323559

  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 ?

• Bill Talada

  SSChampion

  Points: 11954

  More actions

  May 19, 2017 at 9:54 am

  #1943345

  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.

• Bill Talada

  SSChampion

  Points: 11954

  More actions

  May 19, 2017 at 10:10 am

  #1943349

  
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'))
;

• solus

  Hall of Fame

  Points: 3495

  More actions

  May 23, 2017 at 4:15 am

  #1943730

  I ended up putting the code inside a table valued function then using (select count from functionname) which has done the trick