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.
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 & ORs -- I am pretty much new in SQL world so any help on this would be really appreciated.
WHERE StartDate >= CAST(GETDATE()+4 AS date) AND EndDate <= CAST(GETDATE()+14 AS date) OR StartDate <= CAST(GETDATE()+4 AS date) AND EndDate >= CAST(GETDATE()+4 AS date) OR StartDate >= CAST(GETDATE()+14 AS date) AND EndDate <= 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.