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 & 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.
Best Answer
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
db<>fiddle here