0

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.

0
1

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 >= DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7 + 7, 0) 
AND (EndDate <= DATEADD(DAY, (DATEDIFF(DAY, 6, GETDATE()) / 7) * 7 + 14, 6) 
    OR EndDate <= 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<>fiddle here

3
  • Hi, thank you very much for looking into it but the condition hasn’t satisfied as the query didn’t pick up ID: 1, 3, 9. I could get away if the condition will pick up ID: 5, 6 being in the range but I will have to include everything that falls within start date>= Monday AND end date <= Sunday. Apr 29 at 18:28
  • the query make exactly what you said. It takes all between may the 9th and the week after that, but mainly you see how you can search for specific dates. From here you can construct what ever you need
    – nbk
    Apr 29 at 18:50
  • OR StartDate <= CAST(GETDATE()+4 AS date) AND EndDate >= CAST(GETDATE()+4 AS date) gets than to OR StartDate <= DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7 + 7, 0) AND EndDate >= DATEADD(DAY, (DATEDIFF(DAY, 0, GETDATE()) / 7) * 7 + 7, 0)your description is not that clear, but now you have eery thing you need. A minimal reproducible example would also include a wanted result
    – nbk
    Apr 29 at 18:55
1

Test table

CREATE TABLE dbo.Test
(
    ID integer NOT NULL,
    [Start Date] date NOT NULL,
    [End Date] date NOT NULL
);

Sample data

INSERT dbo.Test
    (ID, [Start Date], [End Date])
VALUES
    (1, CONVERT(date, '20/04/2021', 103), CONVERT(date, '07/05/2021', 103)),
    (2, CONVERT(date, '05/06/2021', 103), CONVERT(date, '06/06/2021', 103)),
    (3, CONVERT(date, '04/05/2021', 103), CONVERT(date, '30/06/2021', 103)),
    (4, CONVERT(date, '20/05/2021', 103), CONVERT(date, '30/05/2021', 103)),
    (5, CONVERT(date, '05/04/2019', 103), CONVERT(date, '06/06/2021', 103)),
    (6, CONVERT(date, '24/06/2018', 103), CONVERT(date, '31/10/2023', 103)),
    (7, CONVERT(date, '05/05/2021', 103), CONVERT(date, '06/05/2021', 103)),
    (8, CONVERT(date, '07/05/2021', 103), CONVERT(date, '08/05/2021', 103)),
    (9, CONVERT(date, '27/11/2020', 103), CONVERT(date, '05/05/2021', 103)),
    (10, CONVERT(date, '09/05/2021', 103), CONVERT(date, '10/05/2021', 103));

Solution

SELECT
    T.* 
FROM dbo.Test AS T
CROSS APPLY
(
    SELECT 
        NM.NextMonday,
        SundayAfter = DATEADD(DAY, 6, NM.NextMonday)
    FROM
    (
        -- See https://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server
        -- 1 Jan 1900 was a Monday
        SELECT NextMonday = 
            DATEADD(WEEK, 
                1 + DATEDIFF(WEEK, 
                    {D '1900-01-01'}, CURRENT_TIMESTAMP), 
                {D '1900-01-01'})
    ) AS NM
) AS RF
WHERE
    T.[Start Date] BETWEEN RF.NextMonday AND RF.SundayAfter
    OR T.[End Date] BETWEEN RF.NextMonday AND RF.SundayAfter;

Results

ID Start Date End Date
1 2021-04-20 2021-05-07
3 2021-05-04 2021-06-30
7 2021-05-05 2021-05-06
8 2021-05-07 2021-05-08
9 2020-11-27 2021-05-05
10 2021-05-09 2021-05-10

db<>fiddle demo

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.