Get data between date range I have a range of tabular data with two columns for dates Start Date and End Date and I want to pick up everything either Starts/Ends on coming Monday 03/05/21 or Starts/Ends on Sunday 09/05/21 and everything in between so typically a week of workload.
e.g.
Derived Monday Start Date:  03/05/2021
Derived Sunday End Date:    09/05/2021
Derived Monday End Date:    10/05/2021
I initially wrote below syntax to work around to choose every Monday since Report will run on every Thursday but the query not producing correct results, I understand it might be due to excessive use of ANDs &amp; ORs -- I am pretty much new in SQL world so any help on this would be really appreciated.
WHERE   
   StartDate &gt;= CAST(GETDATE()+4 AS date) AND EndDate &lt;= CAST(GETDATE()+14 AS date)
OR StartDate &lt;= CAST(GETDATE()+4 AS date) AND EndDate &gt;= CAST(GETDATE()+4 AS date)
OR StartDate &gt;= CAST(GETDATE()+14 AS date) AND EndDate &lt;= CAST(GETDATE()+14 AS date)

I also tried below but this formula doesn't up pick on-going work:
StartDate BETWEEN CAST(GETDATE()+4 AS Date) AND DATEADD(week, 2, CAST(GETDATE() AS Date))

ID  Start Date  End Date
1   20/04/2021  07/05/2021
2   05/06/2021  06/06/2021
3   04/05/2021  30/06/2021
4   20/05/2021  30/05/2021
5   05/04/2019  06/06/2021
6   24/06/2018  31/10/2023
7   05/05/2021  06/05/2021
8   07/05/2021  08/05/2021
9   27/11/2020  05/05/2021
10  09/05/2021  10/05/2021

I want to pick everything that is either starts or End on Monday OR  starts or End on Sunday and everything in between a week of range for example.
I want data for next week between 03/05/21 - 10/05/21 so the query should pick ID: 1, 3, 7, 8, 9, 10. I sadly can't use any fixed date as the data I want, is between Every Monday and Sunday.
The default date format of sql server is yyyymmdd as you can see in the example
What you need to do is find a algorithm that gets the wanted date


CREATE TABLE table1 (
  ID INTEGER,
  StartDate DATE,
  EndDate DATE
);

INSERT INTO table1
  (ID, StartDate, EndDate)
VALUES
  ('1', '20210420', '2021-05-07'),
  ('2', '20210605', '20210606'),
  ('3', '20210501', '20210630'),
  ('4', '20210520', '20210530'),
  ('5', '20210429', '20210430'),
  ('6', '20210429', '20210429'),
  ('7', '20210505', '20210506'),
  ('8', '20210507', '20210508'),
  ('9', '20210510', '20210512'),
  ('10', '20210509', '20210510');
GO




SELECT DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7 + 14, 0) AS NextDay



GO


| NextDay                 |
| :---------------------- |
| 2021-05-10 00:00:00.000 |




SELECT DATEADD(DAY, (DATEDIFF(DAY, 6, GETDATE()) / 7) * 7 + 14, 6) AS NextDay
GO


| NextDay                 |
| :---------------------- |
| 2021-05-09 00:00:00.000 |




SELECT ID, StartDate, EndDate FROM table1 
WHERE StartDate &gt;= DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7 + 7, 0) 
AND (EndDate &lt;= DATEADD(DAY, (DATEDIFF(DAY, 6, GETDATE()) / 7) * 7 + 14, 6) 
    OR EndDate &lt;= DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7 + 14, 0))
GO


ID | StartDate  | EndDate   
-: | :--------- | :---------
 7 | 2021-05-05 | 2021-05-06
 8 | 2021-05-07 | 2021-05-08
10 | 2021-05-09 | 2021-05-10


db&lt;&gt;fiddle here