Hi,

I'm trying to figure out the best way to perform a check to make sure
that all dates are accounted for (used up) for the following situation.

I have a Vehicles table. This table contains information stating when a
vehicle was purchased, and when the vehicle was sold. This table
includes the following fields:

REGO
PURCHDATE
SALEDATE


I have a second table called Usages, which contains a list of who used
the vehicle during the duration. The table contains the following fields:

PERSON
REGO
STARTDATE
ENDDATE


We may have information such as:

VEHICLES
~~~~~~~~
ABC123   2010-01-01  2010-01-10
ZZZ000   2010-01-11  2010-01-15


USAGES
~~~~~~
TIM     ABC123   2010-01-01   2010-01-04
SAM     ABC123   2010-01-06   2010-01-08
ROY     ABC123   2010-01-09   2010-01-10
STEVE   ZZZ000   2010-01-11   2010-01-12
ADAM    ZZZ000   2010-01-13   2010-01-15


What I would like to do is check to make sure that all days are
accounted for.

In the above example, vehicle ZZZ000 is OK, because there are usage
records that cover the entire time of the vehicle, but vehicle ABC123
needs to be raised as it doesn't cover all days. (There is a gap / no
allocation for the date of 2010-01-05).

My first thought was to execute a query such like:

Select V.REGO, V.PURCHDATE, V.SALEDATE, V.SALEDATE - V.PURCHDATE AS
VEHICLEDAYS SUM(U.ENDDATE - U.STARTDATE) AS USAGEDAYS

and compare the VEHICLEDAYS result with the USAGEDAYS result.

But in the data I have this returns various results depending on how
long the vehicle was obtaied (we're talking years in this instance) and
when the days start and end. (ie, if they start and end on the first and
last days of the month respectively).


Another idea I had was to populate a memory table with a list of dates
from the start of the vehicle, to the end of the vehicle, and then
delete where the dates don't exist. Something like:

Drop table if exists "memory\DateList";
CREATE TABLE "memory\DateList"("Date" DATE, PRIMARY KEY ("Date")
COMPRESS NONE);
insert into Memory\DateList values ('2009-01-01');
insert into Memory\DateList values ('2009-01-02');
insert into Memory\DateList values ('2011-01-03'); (etc....)

delete from Memory\DateList M
inner join Usages U on (U.Start_Date <= M.Date) and (U.End_Date > M.Date)
where (U.Vehicle = 'ASD');

Select * from Memory\DateList

But this seems rather complex.

I was wondering is this the best way to obtain this, or has anyone some
other advice?

Cheers

Adam.

Adam
> I'm trying to figure out the best way to perform a check to make sure
> that all dates are accounted for (used up) for the following situation.
>
> I have a Vehicles table

> REGO
> PURCHDATE
> SALEDATE
> I have a second table called Usages,

> PERSON
> REGO
> STARTDATE
> ENDDATE
>

This is actually very similar to the problem of tracking missing numbers (It
think!).  The following query should show all the gaps in usage

/* find holes in usages */
select usages.regno,usages.enddate+1 as start,min(t3.startdate)- 1 as stop
from usages
left outer join usages t2 on usages.enddate=t2.startdate-1 and
usages.regno=t2.regno
left outer join usages t3 on usages.enddate < t3.startdate-1 and
usages.regno=t3.regno
where t2.startdate is null and t3.startdate is not null
group by regno,start,stop
union
/* find those unused at start */
select vehicles.regno,purchdate as start,min(usages.startdate)-1 as stop
from vehicles
join usages on vehicles.regno=usages.regno
group by regno,purchdate
having purchdate < min(usages.startdate)
union
/* find those unused at end */
select vehicles.regno,max(usages.enddate)+1 as start,saledate as stop from
vehicles
join usages on vehicles.regno=usages.regno
group by regno,saledate
having saledate > max(usages.enddate)

John

Hi John,

Fantastic - that's exactly what I was chasing!

Thanks very much and have a great day!

Adam