If you had a table like this, your job would be easy.
-- I'm going to drop this table later.
create table calendar (
cal_date date primary key
);
insert into calendar values ('2015-09-18');
insert into calendar values ('2015-09-19');
insert into calendar values ('2015-09-20');
insert into calendar values ('2015-09-21');
insert into calendar values ('2015-09-22');
insert into calendar values ('2015-09-23');
-- Weekdays
select cal_date, strftime('%w', cal_date) day_of_week
from calendar
where day_of_week between 1 and 5;
cal_date day_of_week
---------- -----------
2015-09-18 5
2015-09-21 1
2015-09-22 2
2015-09-23 3
-- Weekdays between two dates
select cal_date, strftime('%w', cal_date) day_of_week
from calendar
where day_of_week between 1 and 5
and cal_date between '2015-09-19' and '2015-09-22';
cal_date day_of_week
---------- -----------
2015-09-21 1
2015-09-22 2
-- Count them
select count(*) num_days
from (select cal_date, strftime('%w', cal_date) day_of_week
from calendar
where day_of_week between 1 and 5
and cal_date between '2015-09-19' and '2015-09-22') x;
num_days
----------
2
So let's manufacture a table like that from start and end dates. To do that, we need a table of integers.
create table integers (
n integer primary key
);
insert into integers values (0);
insert into integers values (1);
insert into integers values (2);
insert into integers values (3);
insert into integers values (4);
insert into integers values (5);
The number of rows you insert is important. The lowest start date plus the largest integer usually needs to return a result later than the latest end date. Typically, you need a lot more than five rows. You've been warned.
create table test (start date, end date);
insert into test values('2015-09-19','2015-09-22');
insert into test values('2015-09-19','2015-09-19');
-- Calendar from date range. Look at your query plan.
-- There might be a better way to do this. I think you're
-- going to have to use a cross join with SQLite, though.
select distinct date(start, '+' || n || ' days') as cal_date
from test, integers
order by cal_date;
cal_date
----------
2015-09-19
2015-09-20
2015-09-21
2015-09-22
2015-09-23
2015-09-24
2015-09-25
Let's expand that a little to include the day of the week, and make it a view.
-- Calendar view from date range
drop table calendar;
create view calendar as
select distinct date(start, '+' || n || ' days') as cal_date,
case strftime('%w', date(start, '+' || n || ' days'))
when 0 then 'Sun'
when 1 then 'Mon'
when 2 then 'Tue'
when 3 then 'Wed'
when 4 then 'Thu'
when 5 then 'Fri'
when 6 then 'Sat'
end as day_of_week
from test, integers;
Now we can count the number of weekdays using a join and an aggregate function.
select start, end, count(cal_date) as num_weekdays
from test
left join calendar on cal_date between start and end
and day_of_week in ('Mon', 'Tue', 'Wed', 'Thu', 'Fri')
group by start, end
order by start, end;
start end num_weekdays
---------- ---------- ------------
2015-09-19 2015-09-19 0
2015-09-19 2015-09-22 2
