DateDiff Query

  • Thread starter Thread starter robboll
  • Start date Start date
R

robboll

Is there a query solution for the following?

Table Name: Table1

Each row of data consists of Events with Start and End dates: a, c, e,
g, i, and k
for period July 1, 2008 thru Aug 5, 2008

a_s: 5-Jul
a_e: 21-Jul
c_s: 5-Jul
c_e: 21-Jul
e_s: 6-Jul
e_e: 24-Jul
g_s: 4-Jul
g_e: 20-Jul
i_s: 6-Jul
i_e: 20-Jul
k_s: 26-Jul
k_e: 4-Aug

Some event start and end at the same time (e.g., a, c) Others
overlap.

Is there a query method that will account for all days that are NOT
event days for the period?

Doing this manually I come up with: 6 Days

Any help greatly appreciated!!!

RBollinger
 
Is there a query solution for the following?

Table Name: Table1

Each row of data consists of Events with Start and End dates: a, c, e,
g, i, and k
for period July 1, 2008 thru Aug 5, 2008

a_s: 5-Jul
a_e: 21-Jul
c_s: 5-Jul
c_e: 21-Jul
e_s: 6-Jul
e_e: 24-Jul
g_s: 4-Jul
g_e: 20-Jul
i_s: 6-Jul
i_e: 20-Jul
k_s: 26-Jul
k_e: 4-Aug

Some event start and end at the same time (e.g., a, c)  Others
overlap.

Is there a query method that will account for all days that are NOT
event days for the period?

Doing this manually I come up with:  6 Days

Any help greatly appreciated!!!

RBollinger
Click to expand...

Try this line of logic.

Define a table as PossibleDays

create table PossibleDays
(
PossibleDay datetime
)

Manually insert into PossibleDays the initial date

INSERT into PossibleDays ( PossibleDay ) values ( #07/01/2008# );

( Excuse me for showing my North American bias in date formatting. )

Next write an insert query for the other days.

INSERT into PossibleDays( PossibleDay )
SELECT PossibleDay + 1
from PossibleDays
where PossibleDay = ( SELECT Max( PossibleDay ) from PossibleDays )
and PossibleDay < #08/05/2008#

Run this query repeatedly until it stops inserting rows.


Write a query that combines the start and end pair into a single row.

SELECT A.EventDate as startDate , B.EventDate as EndDate
from Table1 as A inner join Table1 as B
on left( A.EventDate,1 ) = left( B.EventDate,1 )
where Right( A.EventDate,1 ) = 's'
and Right( B.EventDate,1 ) = 'e'

Save this query as EventPeriods

Use the PossibleDays table to find those days missing in the in the
periods specified by EventPeriods:

SELECT PossibleDay
from PossibleDays
where not exists
( select 'true'
from EventPeriods
where EventStart >= PossibleDays.PossibleDay
and EventEnd <= PossibleDays.PossibleDay )

Then count those days:

SELECT Count(*)
from
(
SELECT PossibleDay
from PossibleDays
where not exists
( select 'true'
from EventPeriods
where EventStart >= PossibleDays.PossibleDay
and EventEnd <= PossibleDays.PossibleDay )
)
 
Back
Top