CODEWITHSUNDEEP
sqlms-accessms-access-2010
mTorres
mTorres

Reputation: 3590

MsAccess SQL count days in overlaping periods with some excluding periods

The Problem:

Given a table with periods of time for a given person that may (or may not) overlap and given another table with exclusions, I want to count the number of days for each person and period excluding the overlapping days and also the exclusion periods.

As they say an image it's worth 1000 words so:

periods image

Sample scenario:

The periods table:

create table periods (
  `id` COUNTER (1,1) PRIMARY KEY,
  `person_id` text(50),
  `start_date` Date,
  `end_date` Date
)

The excluding periods table:

create table exclusions (
  `start_date` As Date,
  `end_date` As Date
)

And some values:

INSERT INTO `periods`(`person_id`, `start_date`, `end_date`)
VALUES('1', CDate('01/09/2014'), CDate('30/09/2014'));

INSERT INTO `periods`(`person_id`, `start_date`, `end_date`)
VALUES('1', CDate('10/10/2014'), CDate('31/10/2014'));

INSERT INTO `periods`(`person_id`, `start_date`, `end_date`)
VALUES('1', CDate('25/09/2014'), CDate('15/10/2014'));

INSERT INTO `periods`(`person_id`, `start_date`, `end_date`)
VALUES('1', CDate('20/11/2014'), CDate('10/12/2014'));

INSERT INTO `periods`(`person_id`, `start_date`, `end_date`)
VALUES('1', CDate('15/11/2014'), CDate('25/11/2014'));

INSERT INTO `exclusions`(`start_date`, `end_date`)
VALUES(CDate('10/09/2014'), CDate('15/09/2014'));

INSERT INTO `exclusions`(`start_date`, `end_date`)
VALUES(CDate('01/12/2014'), CDate('20/12/2014'));

What I've tried:

So far, I am able to detect the overlapped periods using this query:

SELECT s1.person_id as person_id, 
    iif(s1.start_date <= s2.start_date, s1.start_date, s2.start_date) As start_date,
    iif(s1.end_date >= s2.end_date, s1.end_date, s2.end_date) As end_date
FROM
  periods As S1 INNER JOIN periods As S2 ON
    s1.person_id = s2.person_id And
    s1.id < s2.id And
    s2.start_date <= s1.end_date And s2.end_date >= s1.start_date

But there is a problem as the result is:

person_id   start_date   end_date
 1          01/09/2014  15/10/2014
 1          25/09/2014  31/10/2014
 1          15/11/2014  10/12/2014

Notice that the first and second rows are an overlapped period also. I can mange this executing the same query with its own result, but feels odd.

What I need:

The other problems I have now are that I don't know how to:

Unfortunately I am only able to use MsAccess for this, so I can't use some tricks that I've found googling and thus here I am asking.

Upvotes: 1

Views: 92

Answers (1)

paul
paul

Reputation: 22001

You could solve this with a Calendar table (one row per day for the next 'n' years).

create table calendar (
  `id` COUNTER (1,1) PRIMARY KEY,
  `calendar_date` Date
)

insert into calendar values ('2015-01-01')
insert into calendar values ('2015-01-02')
insert into calendar values ('2015-01-03')
insert into calendar values ('2015-01-04')
insert into calendar values ('2015-01-05') ... et cetera

Then:

select distinct calendar_date
from   periods p join calendar c
where  c.calendar_date between p.start_date and p.end_date and
       not exists(select * from exclusions where c.calendar_date between e.start_date and e.end_date)

Upvotes: 2

Related Questions