Working days from a given date

0
votes

User will select a date in frontend and flexibledays, say for example if they have selected '2014-07-17' as date and flexibledays as 2, then we need to display both 2 previous and next 2 working days as like below,

  1. 2014-07-15
  2. 2014-07-16
  3. 2014-07-17
  4. 2014-07-20
  5. 2014-07-21

excluding weekends (friday and saturday), for use weekends is friday and saturday.

I have used the below query

DECLARE @MinDate DATE, @MaxDate DATE;
    SELECT @MinDate = DATEADD(Day, -@inyDays ,@dtDate), @MaxDate = DATEADD(Day,@inyDays ,@dtDate)

    DECLARE @DayExclusionValue VARCHAR(20)
    SELECT @DayExclusionValue = dbo.UDF_GetConfigSettingValue('DaysToExclude')

    DECLARE @NumOfWeekends INT
    SELECT @NumOfWeekends= (DATEDIFF(wk, @MinDate, @MaxDate) * 2) +(CASE WHEN DATENAME(dw, @MinDate) = 'Friday' THEN 1 ELSE 0 END)      +(CASE WHEN DATENAME(dw, @MaxDate) = 'Saturday' THEN 1 ELSE 0 END)

    SET @MaxDate = DATEADD(Day,@inyDays + @NumOfWeekends ,@dtDate)

    ;WITH CalculatedDates AS
    (
        SELECT dates = @MinDate
        UNION ALL
        SELECT DATEADD(day, 1, dates)
        FROM CalculatedDates
        WHERE DATEADD(day, 1, dates)  <= @MaxDate

    )
    SELECT dates FROM CalculatedDates 
    WHERE dates >= CAST(GETDATE() AS DATE)
    AND DATENAME(DW, dates) NOT IN (SELECT Value FROM UDF_GetTableFromString(@DayExclusionValue))
    OPTION (MAXRECURSION 0);

but the above query is not working properly.

Can you pls suggest me any other solution.

2
sqlsql-servertsql
Added sql-server tag based on provided code – a_horse_with_no_name
I'd highly recommend a calendar table - you can populate it with 100 years worth of data and it's still less than 40000 rows. You can then have a simple working_day/bit column that's populated based on whatever rules make sense for you - i.e. if you also have rules about public holidays. – Damien_The_Unbeliever

2 Answers

0
votes

This example will work for Oracle, you did not say what DB you were using. If you have a list of vacations you need to join that in as indicated. It would need to be a outerjoin, and you need to add a case or something so that the vacation tables 'exclude' days override the generated days.

Also I chose a multiplier on random. When only dealing with weekend 8 was more than enough, but if your vacation table includes a lot of consecutive vacation days it might no longer be. 

select d from(
select rownum nn, d, sysdate - d, first_value (rownum) over (order by abs(sysdate-d)) zero_valu
from (
select sysdate+n d, to_char(sysdate+n,'DAY'), CASE to_char(sysdate+n,'D') WHEN '6' THEN 'exclude' WHEN '7' THEN 'exclude' ELSE 'include' END e_or_i  from
(SELECT ROWNUM-9 n -- 9=flexibleday*8/2 +1
FROM   ( SELECT 1 just_a_column
         FROM   dual
         CONNECT BY LEVEL <= 16 -- 8=flexibleday * 8
       ) 
)
) where e_or_i = 'include'  -- in this step you need to join in a table of holidays or such if you need that.
) where abs(nn-7) <= 2 -- 2=flexiday
order by d
0
votes
DECLARE @StartDate DATE = '2014-07-17';

SELECT *
FROM
(
    --Show Closest Previous 2 Days Not In Friday or Saturday
    SELECT TOP 2
        DATEADD(DAY, -nr, @StartDate) CheckDate, 
        DATENAME(DW, DATEADD(DAY, -nr, @StartDate)) CheckName, 
        -nr CheckCount
    FROM (VALUES(1),(2),(3),(4)) AS Numbers(nr)
    WHERE DATENAME(DW, DATEADD(DAY, -nr, @StartDate)) NOT IN ('Friday','Saturday')

    UNION ALL

    --Show Todays Date If Not Friday or Saturday
    SELECT TOP 1
        DATEADD(DAY, +nr, @StartDate) CheckDate, 
        DATENAME(DW, DATEADD(DAY, +nr, @StartDate)) CheckName, 
        nr CheckCount
    FROM (VALUES(0)) AS Numbers(nr)
    WHERE DATENAME(DW, DATEADD(DAY, +nr, @StartDate)) NOT IN ('Friday','Saturday')

    UNION ALL

    --Show Closest Next 2 Days Not In Friday or Saturday
    SELECT TOP 2
        DATEADD(DAY, +nr, @StartDate) CheckDate, 
        DATENAME(DW, DATEADD(DAY, +nr, @StartDate)) CheckName, 
        nr CheckCount
    FROM (VALUES(1),(2),(3),(4)) AS Numbers(nr)
    WHERE DATENAME(DW, DATEADD(DAY, +nr, @StartDate)) NOT IN ('Friday','Saturday')
) d
ORDER BY d.CheckDate

I break it into 3 parts, previous 2 days, today (if applicable) and next 2 days Here is the output:

CheckDate   CheckName  CheckCount
2014-07-15  Tuesday    -2
2014-07-16  Wednesday  -1
2014-07-17  Thursday   0
2014-07-20  Sunday     3
2014-07-21  Monday     4

I use the datename since not sure what @@datefirst your server is set to. the values() section is just a numbers table (you should create a numbers table as big as the amount of records you want to return plus any weekends you are crossing over) and then the TOP 2 in the first and last sections would be replaced with the number of days you wanted to return before and after.

**** Update with generic numbers table functionality added:

Here we declare the starting date and the number of previous and next days we would like to pull:

DECLARE @StartDate DATE = '2014-07-20';
DECLARE @MaxBusDays INT = 5

This next section creates a numbers table (can be easily found via google)

DECLARE @number_of_numbers INT = 100000;
;WITH
    a AS (SELECT 1 AS i UNION ALL SELECT 1),
    b AS (SELECT 1 AS i FROM a AS x, a AS y),
    c AS (SELECT 1 AS i FROM b AS x, b AS y),
    d AS (SELECT 1 AS i FROM c AS x, c AS y),
    e AS (SELECT 1 AS i FROM d AS x, d AS y),
    f AS (SELECT 1 AS i FROM e AS x, e AS y),
    numbers AS 
(
    SELECT TOP(@number_of_numbers)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
    FROM f
)

Now we use the numbers table and a row_number setting to pull only the number of rows before and after (plus the date of, if it's not fri/sat as wanted) that are working days (not fri/sat)

SELECT *
FROM
(
    --Show Closest Previous x Working Days (Not Friday or Saturday)
    SELECT * FROM
    (
        SELECT
            DATEADD(DAY, -number, @StartDate) CheckDate, 
            DATENAME(DW, DATEADD(DAY, -number, @StartDate)) CheckName, 
            -number CheckCount,
          ROW_NUMBER() OVER (ORDER BY number ASC) AS RowCounter
        FROM Numbers
        WHERE DATENAME(DW, DATEADD(DAY, -number, @StartDate)) NOT IN ('Friday','Saturday')
    ) a
    WHERE a.RowCounter <= @MaxBusDays

    UNION ALL

    --Show Todays Date If Working Day (Not Friday or Saturday)
    SELECT TOP 1
        @StartDate CheckDate, 
        DATENAME(DW, @StartDate) CheckName, 
        0 CheckCount,
      0 RowCounter
    WHERE DATENAME(DW, @StartDate) NOT IN ('Friday','Saturday')

    UNION ALL

    --Show Closest Next x Working Days (Not Friday or Saturday)
    SELECT * FROM
    (
        SELECT
            DATEADD(DAY, +number, @StartDate) CheckDate, 
            DATENAME(DW, DATEADD(DAY, +number, @StartDate)) CheckName, 
            number CheckCount,
          ROW_NUMBER() OVER (ORDER BY number ASC) AS RowCounter
        FROM Numbers
        WHERE DATENAME(DW, DATEADD(DAY, +number, @StartDate)) NOT IN ('Friday','Saturday')
    ) b
    WHERE b.RowCounter <= @MaxBusDays

) c
ORDER BY c.CheckDate

Here is the output: (2014-07-20 is the middle row)

CheckDate     CheckName   CheckCount  RowCounter
2014-07-13    Sunday      -7          5
2014-07-14    Monday      -6          4
2014-07-15    Tuesday     -5          3
2014-07-16    Wednesday   -4          2
2014-07-17    Thursday    -3          1
2014-07-20    Sunday      0           0
2014-07-21    Monday      1           1
2014-07-22    Tuesday     2           2
2014-07-23    Wednesday   3           3
2014-07-24    Thursday    4           4
2014-07-27    Sunday      7           5