Calculate date base on another table active days values

Question

I want to get next three days based on table value. Table one contains the zip code and table 2 contains the orderid with zip postal code. Suppose if i get the order that zip postal code is 27520 on 14-06-2021 then expected date of delivery will be Wednesday date, Friday date and Monday date . The date will be calculate based on days available for Zip in below table routes.

CREATE TABLE [dbo].[ROUTES ](
    [zip] [varchar](255) NULL,  
    [Monday] [varchar](255) NULL,
    [Tuesday] [varchar](255) NULL,
    [Wednesday] [varchar](255) NULL,
    [Thursday] [varchar](255) NULL,
    [Friday] [varchar](255) NULL,
    [Saturday] [varchar](255) NULL,
    [Sunday] [varchar](255) NULL,
    [id] [varchar](255) NULL 
)

The data in this table is like below. 1 indicate for active date for that routes.

zip     Monday  Tuesday Wednesday Thursday Friday Saturday Sunday 
27520   1       0       1         0        1      0         0

Result expected for the date of 14-06-2021 will be below

ExpectedDate1 = 16-06-2021
ExpectedDate2 = 18-06-2021
ExpectedDate3 = 21-06-2021

Thanks for your help.

Answer 1

Here's one possible solution using your current setup...

SQL Fiddle Example

declare @orderDate datetime = '2021-06-14'
, @orderZipCode nvarchar(10) = '27520'

declare @start datetime = dateadd(day, 1, @orderDate)
       ,@end  datetime =  dateadd(day, 22, @orderDate)

select top 3 CAST(s.n as datetime)
from dbo.generate_series(cast(@start as bigint), cast(@end as bigint), default, default) s
where DateName(dw, CAST(s.n as datetime)) in (
    select dw
    from ( select * from ROUTES where zip = @orderZipCode ) r
    UNPIVOT (include for dw in (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)) upvt
    where upvt.include = 1
 )
 order by s.n

This uses the generate series function I describe here.

create function dbo.generate_series
(
      @start bigint
    , @stop bigint
    , @step bigint = 1
    , @maxResults bigint = 0 --0 = unlimited
)
returns @results table(n bigint)
as
begin

    --avoid infinite loop (i.e. where we're stepping away from stop instead of towards it)
    if @step = 0 return
    if @start > @stop and @step > 0 return
    if @start < @stop and @step < 0 return
    
    --ensure we don't overshoot
    set @stop = @stop - @step

    --treat negatives as unlimited
    set @maxResults = case when @maxResults < 0 then 0 else @maxResults end

    --generate output
    ;with myCTE (n,i) as 
    (
        --start at the beginning
        select @start
        , 1
        union all
        --increment in steps
        select n + @step
        , i + 1
        from myCTE 
        --ensure we've not overshot (accounting for direction of step)
        where (@maxResults=0 or i<@maxResults)
        and 
        (
               (@step > 0 and n <= @stop)
            or (@step < 0 and n >= @stop)
        )  
    )
    insert @results
    select n 
    from myCTE
    option (maxrecursion 0) --sadly we can't use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this 

    --all good  
    return
    
end

Note: This is not the cleanest approach (e.g. this uses an unpivot statement which wouldn't be needed if the data were held in a different format to begin with, and relies on the server language being set to English for datename to give the expected values); rather it's the approach which sticks closely to what you've given us to begin with.

Explanation

Regarding how this works:

• @orderDate and @orderZipCode are variables to hold your input data. In the real world you'd likely wrap this code in a function and these would be the parameters you pass to it.

• @start and @end are dates based on the day after your order date to 3 weeks and a day after your order date. These are the outer bounds for the list of dates in which you may have order dates. I went for 3 weeks since if only 1 day (e.g. Monday) were ticked, you'd get 1 date per week. If more days are ticked you don't need this full (e.g. if you always have 3 days ticked you only need 1 week). If no days are ticked you'll never have any results regardless of how many weeks we took.

• generate_series gives us a list of dates between (and including) start and end. See this post for info on how that works.

• unpviot converts your single row per postcode into 7 rows with a dw column taking the source row's column's name, and include flag based on the source row's column's value.

• We filter on dates where include = 1 to capture only those days that we deliver on; then the list of dates over the next 3 weeks is filtered for those falling on these days. We order by n (so earlier dates come first) then return the first 3 dates; giving us the next 3 delivery dates as our result.

---

Update

To return the 3 results as 3 named columns instead of 3 rows, we can use the PIVOT statement. Per discussion in the comments, this version uses your original table definition, with true and blank representing truthy and falsey values.

declare @orderDate datetime = '2021-06-14'
, @orderZipCode nvarchar(10) = '27520'

declare @start datetime = dateadd(day, 1, @orderDate)
       ,@end  datetime =  dateadd(day, 22, @orderDate)

select ExpectedDate1, ExpectedDate2, ExpectedDate3
from (
  select top 3 'ExpectedDate' + cast(row_number() over (order by n) as nvarchar(12)) r
  , CAST(s.n as datetime) ExpectedDate 
  from dbo.generate_series(cast(@start as bigint), cast(@end as bigint), default, default) s
  where DateName(dw, CAST(s.n as datetime)) in (
      select dw
      from ( select * from ROUTES where zip = @orderZipCode ) r
      UNPIVOT (include for dw in (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday)) upvt
      where upvt.include = 'true'
   )
   order by s.n
 ) x
 pivot (
   max (ExpectedDate)
   for r in (ExpectedDate1,ExpectedDate2,ExpectedDate3)
 ) pvt

Answer 2

I would approach this in 2 steps

1. Generate a list of the next 7 dates using a CTE
2. Unpivot the data about delivery days to get rows and join this to 1) above

---

WITH next_7_days(dt, weekday) 
AS (
    SELECT 
        cast(GETDATE() as date), 
        DATENAME(DW,GetDate())
    UNION ALL
    SELECT    
        CAST(CAST(dt AS DATETIME)+1 AS DATE), 
        DATENAME(DW, CAST(dt AS DATETIME) + 1)
    FROM    
        next_7_days
    WHERE dt < DATEADD(day,6,GetDate())
), routes_upvt 
AS
(
    SELECT Zip, Day, IsDeliveryDay  
    FROM   
       (SELECT zip, Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday  
      FROM routes) p  
    UNPIVOT  
       (IsDeliveryDay FOR Day IN   
          (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday )  
    )AS unpvt
)
SELECT nsd.dt 
FROM routes_upvt r
INNER JOIN next_7_days nsd
 ON r.Day = nsd.weekday
WHERE zip=27520
AND IsDeliveryDay=1

Live example: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c5b4f08ef5bf53b6d1854b1f795344de&hide=8

Answer 3

I'd handle it like this:

declare @test date = '2021-06-14';
declare @zip varchar(7) = '27520';

declare @routes table (zip varchar(255), monday varchar(255), tuesday varchar(255), wednesday varchar(255), thursday varchar(255), friday varchar(255), saturday varchar(255), sunday varchar(255));

INSERT INTO @routes VALUES 
('27520', '1', '0', '1', '0', '1', '0', '0'),
('27523', '1', '1', '0', '1', '1', '0', '0');

declare @nextdays table(vdate date, dow int);

INSERT INTO @nextdays (vdate) VALUES
(DATEADD(dd, 1, @test)),
(DATEADD(dd, 2, @test)),
(DATEADD(dd, 3, @test)),
(DATEADD(dd, 4, @test)),
(DATEADD(dd, 5, @test)),
(DATEADD(dd, 6, @test)),
(DATEADD(dd, 7, @test)),
(DATEADD(dd, 8, @test)),
(DATEADD(dd, 9, @test)),
(DATEADD(dd, 10, @test)),
(DATEADD(dd, 11, @test)),
(DATEADD(dd, 12, @test)),
(DATEADD(dd, 13, @test)),
(DATEADD(dd, 14, @test)),
(DATEADD(dd, 15, @test)),
(DATEADD(dd, 16, @test)),
(DATEADD(dd, 17, @test)),
(DATEADD(dd, 18, @test)),
(DATEADD(dd, 19, @test)),
(DATEADD(dd, 20, @test)),
(DATEADD(dd, 21, @test));

UPDATE @nextdays SET dow = DATEPART(dw, vdate);


WITH cte AS
(SELECT 1 as dow, sunday from @routes WHERE zip = @zip
UNION 
SELECT 2 as dow, monday from @routes WHERE zip = @zip 
UNION 
SELECT 3 as dow, tuesday from @routes WHERE zip = @zip 
UNION 
SELECT 4 as dow, wednesday from @routes WHERE zip = @zip 
UNION 
SELECT 5 as dow, thursday from @routes WHERE zip = @zip 
UNION 
SELECT 6 as dow, friday from @routes WHERE zip = @zip 
UNION 
SELECT 7 as dow, saturday from @routes WHERE zip = @zip)
SELECT TOP 3 vdate
FROM @nextdays n
INNER JOIN cte c ON n.dow = c.dow AND c.sunday = '1'
ORDER BY n.vdate;

I am inserting 21 dates into the table variable, in case there is only 1 delivery date for a given zip. Obviously if all zips have a minimum of 2 or 3 delivery days you can reduce the amount inserted.

I then do a UNION instead of an UNPIVOT, simply because with a limited number of requirements it is a bit easier to understand. Note that the UNION restricts the SELECT to the required zip.

One point to note, when doing a UNION in this fashion, if the column names are different, the name of the resulting column is the name of the column in the first SELECT in the UNION. Hence the JOIN is on c.sunday = '1'.