CODEWITHSUNDEEP
sqlitedateweekday
Pal Csanyi
Pal Csanyi

Reputation: 101

SQLite to select dates within a range but without Sundays and some holidays

the following code

WITH RECURSIVE dates(x) AS ( 
            SELECT '2017-11-01'
                UNION ALL 
            SELECT DATE(x, '+1 DAYS') FROM dates WHERE x<'2018-01-31' AND 'weekday'>0
        ) 
        SELECT * FROM dates;

does not exclude Sundays from the dates. Why does not?

Also I would like to exclude some dates from the result set of dates which dates are in another table in the database. Let is call that table Holidays. The schema for that table could be like this:

CREATE TABLE Holidays (
    id              INTEGER PRIMARY KEY
                            UNIQUE,
    NameOfHoliday      TEXT    DEFAULT NULL,
    startDate DATE    DEFAULT NULL,
    endDate DATE    DEFAULT NULL
);

Let insert some data into Holidays table:

INSERT INTO Holidays VALUES(1,'Winter Break','2017-12-23','2018-01-14');

Then how to use the above CTE's with this Holidays table to achieve the result which contains dates but without Sundays and without dates between startdate and enddate in holidays?

Best, Pal

Upvotes: 2

Views: 858

Answers (1)

MikeT
MikeT

Reputation: 56943

Part of your issue is that 'weekday' (a string) will always be greater than 0, so that test will always be true.

The second issue is that recursion halts when an empty result is returned from the right(recursive) SELECT. So if you had the test for a Sunday in this select then no more recursions will be made when the condition is not met. Thus, only data up until the first Sunday (Sunday would result in no row) would be selected (as 2017-09-01 is a Friday, you would get just 2 rows as the 3rd is a Sunday). Therefore the test for Sunday should be after the recursion.

What you want is to get the day of the week as an integer 0-6 (Sun-Sat). You can use the strftime('%w',valid_date) to get the value, but you then need to ensure that is is an integer rather than text/char/string so you could use CAST(strftime('%w',valid_date) As INTEGER).

To fix the rows limited to just up till the first Sunday, the WHERE clause for removing Sundays should be in the final SELECT after the recursion.

As such, the following is a fix for the issues that you could use :-

WITH RECURSIVE dates(x) AS ( 
            SELECT '2017-09-01'
                UNION ALL 
            SELECT DATE(x, '+1 DAYS') FROM dates WHERE x <'2017-09-30'
        ) 
        SELECT x FROM dates WHERE CAST(strftime('%w',x) As INTEGER) > 0;

This would result in 26 rows, instead of the 30 e.g. :-

enter image description here

......

  • i.e. the 3rd and 10th are excluded (and so on).

Additional

re the additional question :-

Also I would like to exclude some dates from the result set of dates which dates are in another table in the database. Let is call that table Holidays. The schema for that table could be like this:

CREATE TABLE Holidays (
    id              INTEGER PRIMARY KEY
                            UNIQUE,
    NameOfHoliday      TEXT    DEFAULT NULL,
    startDate DATE    DEFAULT NULL,
    endDate DATE    DEFAULT NULL
);

Let insert some data into Holidays table:

INSERT INTO Holidays VALUES(1,'Winter Break','2017-12-23','2018-01-14');

This is quite complicated especially as you could potentially have multiple holidays to consider (say you wanted to do a whole year). What I would suggest is rather than have a range of dates in two columns is to have a table with individual dates that are holidays, this is then very simple to cater for.

Instead of the Holidays table lets say there is the simpler AltHolidays table e.g. :-

CREATE TABLE IF NOT EXISTS AltHolidays (NameOfHoliday TEXT, Day_To_Exclude TEXT);
INSERT OR IGNORE INTO AltHolidays VALUES
    ('Winter Break','2017-12-23'),
    ('Winter Break','2017-12-24'),
    ('Winter Break','2017-12-25'),
    ('Winter Break','2017-12-26'),
    ('Winter Break','2017-12-27'),
    ('Winter Break','2017-12-28'),
    ('Winter Break','2017-12-29'),
    ('Winter Break','2017-12-30'),
    ('Winter Break','2017-12-31'),
    ('Winter Break','2018-01-01'),
    ('Winter Break','2018-01-02'),
    ('Winter Break','2018-01-03'),
    ('Winter Break','2018-01-04'),
    ('Winter Break','2018-01-05'),
    ('Winter Break','2018-01-06'),
    ('Winter Break','2018-01-07'),
    ('Winter Break','2018-01-08'),
    ('Winter Break','2018-01-09'),
    ('Winter Break','2018-01-10'),
    ('Winter Break','2018-01-11'),
    ('Winter Break','2018-01-12'),
    ('Winter Break','2018-01-13'),
    ('Winter Break','2018-01-14')
;
  • Ideally descriptions would be held in it's own table and it would be referenced.

Then (assuming you still want to exclude Sundays as well) you could use NOT IN as e.g. :-

WITH RECURSIVE dates(x) AS ( 
            SELECT '2017-11-01'
                UNION ALL 
            SELECT DATE(x, '+1 DAYS') FROM dates WHERE x<'2018-01-31'
        ) 
        SELECT * 
                FROM dates 
                WHERE x NOT IN (SELECT Day_To_Exclude FROM AltHolidays) -- Exclude holidays
                    AND CAST(strftime('%w',x) AS INTEGER) <> 0 -- Exclude Sundays
;

This would result in 60 rows (92 - 23 (holidays) - 9 (remaining Sundays))

Upvotes: 1

Related Questions