SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
 Apr 11 2022 16:24:07 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



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 
)
INSERT INTO routes
values (27520,1,0,1,0,1,0,0,'')
1 rows affected

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())
)
SELECT 
dt,
    weekday
FROM 
    next_7_days;


dt
weekday


2024-02-12
Monday

2024-02-13
Tuesday

2024-02-14
Wednesday

2024-02-15
Thursday

2024-02-16
Friday

2024-02-17
Saturday

2024-02-18
Sunday

2024-02-19
Monday


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


dt


2024-02-12

2024-02-14

2024-02-16

2024-02-19

dt	weekday
2024-02-12	Monday
2024-02-13	Tuesday
2024-02-14	Wednesday
2024-02-15	Thursday
2024-02-16	Friday
2024-02-17	Saturday
2024-02-18	Sunday
2024-02-19	Monday