Find Missing Date Ranges

[PC Datasheet]

Transaction data is given with date ranges:
Beginning End
4/1/06 4/4/06
4/7/06 4/11/06
4/14/06 4/17/06
4/18/06 4/21/06
426/06 4/30/06

I am looking for suggestions on how to find the date ranges where there were
no transactions.
4/12/06 - 4/13/06
4/22/06 - 4/25/06

Thanks!

Steve

 

[Guest]

Try this
Function LookForMissingDates()
Dim MyDB As DAO.Database, MyRec As DAO.Recordset, EndDate As Date
EndDate = #1/1/2500#
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset("SELECT TableName.StartDate,
TableName.EndDate FROM TableName ORDER BY TableName.StartDate,
TableName.EndDate;")
While Not MyRec.EOF
If DateDiff("d", EndDate, MyRec!StartDate) > 1 Then
Debug.Print EndDate + 1 & " " & MyRec!StartDate - 1
End If
EndDate = MyRec!EndDate
MyRec.MoveNext
Wend
End Function

 

[Bob Quintal]

Transaction data is given with date ranges:
Beginning End
4/1/06 4/4/06
4/7/06 4/11/06
4/14/06 4/17/06
4/18/06 4/21/06
426/06 4/30/06

I am looking for suggestions on how to find the date ranges
where there were no transactions.
4/12/06 - 4/13/06
4/22/06 - 4/25/06

Thanks!

Steve

My approach is as follows, given that you have date ranges, not
exact dates.
Build a calendar table. Left join to your transaction beginning
date. Loop through the dataset setting a flag in the calendar
table for each date to the end date of that record. Any dates
without the flag after you have walked the recordset have no
transactions.

 

[Tom Ellison]

Dear Steve:

I suggest a query. It would use a certain methodology which I will
describe.

Now, the data you give has gaps on the dates 4-5, 12-13, and 22-25. I'd
like to change the data slightly to test the condition where two ranges
overlap, as well as the one case you have where they are adjacent (14-17 and
18-21).

So, I propose this test data:

Beginning End
4/1/06 4/9/06
4/7/06 4/11/06
4/14/06 4/17/06
4/18/06 4/21/06
426/06 4/30/06

The query would be:

SELECT DR.End + 1 AS BeginRange,
(SELECT MIN(DR1.Beginning) - 1
FROM DateRange DR1
WHERE DR1.Beginning > DR.End + 1)
AS EndRange
FROM DateRange DR
WHERE NOT EXISTS (
SELECT * FROM DateRange DR1
WHERE DR.End + 1 BETWEEN DR1.Beginning AND DR1.End
AND NOT DR.Beginning BETWEEN DR1.Beginning AND DR1.End)
AND End < (SELECT MAX(End) FROM DateRange)

I get the result:

BeginRange EndRange
4/12/2006 4/13/2006
4/22/2006 4/25/2006


You would need to change the table name where I have DateRange. Initially,
please try this with no other changes.

Do you need some explanation of how this works?

Tom Ellison

 

[PC Datasheet]

Bob,

Thank you for responding!!

Since you responded politely, I want to offer you my apology for my previous
jabs at you. I just wanted you to know what it feels like to be ridiculed at
every post you make to the newsgroup. I know you are not like Arno R, John
Marshall, Randy Harris and Keith Wilby who do not make any contributions to
this newsgroup but nevertheless feel warranted in posting their slanderous
garbage here about me. My jabs at you are over. Done!

Steve

 

[PC Datasheet]

Tom,

Thank you very much for responding (and politely)!!

I tried your solution. I built a table named DateRange with two fields
Beginning and End and entered your test data in the table. I copied your SQL
from your response and pasted it into the SQL of a new query. When I ran the
query I got an error message that said:
Enter Pasrameter Value DateRange.End.

I pressed Enter and the query result had BeginRange and EndRange. There was
no data under BeginRange and the two dates you show under EndRange appeared
under EndRange. It looks like what you suggested will work as soon as we
determine why I got the parameter message.

I have a question about your SQL. In the third line you have:
From DateRange DR1
Could you explain that syntax please. Is that the same as
From DateRange As DR1

That syntax appears in more than one place in your SQL and I don't
understand it.

Finally, Yes, I would appreciate your explanation on how the SQL works.

Thank you again!!

Steve

 

[Randy Harris]

* PC Datasheet:

Tom,

Thank you very much for responding (and politely)!!

I tried your solution. I built a table named DateRange with two fields
Beginning and End and entered your test data in the table. I copied your SQL
from your response and pasted it into the SQL of a new query. When I ran the
query I got an error message that said:
Enter Pasrameter Value DateRange.End.

I pressed Enter and the query result had BeginRange and EndRange. There was
no data under BeginRange and the two dates you show under EndRange appeared
under EndRange. It looks like what you suggested will work as soon as we
determine why I got the parameter message.

I have a question about your SQL. In the third line you have:
From DateRange DR1
Could you explain that syntax please. Is that the same as
From DateRange As DR1

That syntax appears in more than one place in your SQL and I don't
understand it.

Finally, Yes, I would appreciate your explanation on how the SQL works.

Thank you again!!

Steve

Maybe Tom can explain it to you. I hear his rates are very reasonable.

 

[Tom Ellison]

Dear Steve:

1. If I did not feel I should respond to you politely, I would not respond
at all. I am aware of certain controversies, but choose not to participate.

2. Your sampe showed a column named End. I do not believe you would get
Enter Parameter Value message unless somehow you do not have a column named
as indicated.

3. The AS is optional for table aliases. I tend to omit it. After we get
this working, feel free to insert AS where you like.

I will provide a brief explanation after it is working. Assuming you have a
situation where you need this, why not provide me the table and column names
used, and I'll alter it myself.

I took the time to create a table with the column names I used and have
tested this, as I indicated. You should be able to move with confidence
that, except for column and table names, this is a working solution.

Tom Ellison

 

[Tom Ellison]

Dear Randy:

I resent that. My rates are not at all reasonable! I can barely afford
groceries! : )

Tom Ellison

 

[PC Datasheet]

Tom,

See responses below ---

Dear Steve:

1. If I did not feel I should respond to you politely, I would not
respond at all. I am aware of certain controversies, but choose not to
participate.

Thanks again for your politeness and for choosing not to participate!!

2. Your sampe showed a column named End. I do not believe you would get
Enter Parameter Value message unless somehow you do not have a column
named as indicated.

My table had a field named End. I deleted my query, created a new query and
pasted your SQL in and this time it worked just as you said!!

3. The AS is optional for table aliases. I tend to omit it. After we
get this working, feel free to insert AS where you like.

I learned something new!!

I will provide a brief explanation after it is working. Assuming you have
a situation where you need this, why not provide me the table and column
names used, and I'll alter it myself.

Yes it works fine and will work in my application. I am researching methods
for a reservations project. I need to be able to determine date ranges in a
time period where there are no reservations. Your solution does this nicely.
I can take it from here but would appreciate your explanation if you have
time.

 

[Bob Quintal]

Bob,

Thank you for responding!!

Since you responded politely, I want to offer you my apology
for my previous jabs at you.

Your apology is sincerely accepted.


I just wanted you to know what it

feels like to be ridiculed at every post you make to the
newsgroup.

I've known the feeling of rejection from long before you tried
to teach it to me. I've learned to live with it. Have you ever
stopped to think about why people get upset about your
misleading answers and that overblown .sig? Steve, you need to
follow the counsel of others if you want to avoid being
ridiculed.

If you were to follow the group's charter and stop advertising,
I'm sure things would run a lot smoother for you.

My jabs at you are

 

[PC Datasheet]

Thanks Ofer!!

Steve

Try this
Function LookForMissingDates()
Dim MyDB As DAO.Database, MyRec As DAO.Recordset, EndDate As Date
EndDate = #1/1/2500#
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset("SELECT TableName.StartDate,
TableName.EndDate FROM TableName ORDER BY TableName.StartDate,
TableName.EndDate;")
While Not MyRec.EOF
If DateDiff("d", EndDate, MyRec!StartDate) > 1 Then
Debug.Print EndDate + 1 & " " & MyRec!StartDate - 1
End If
EndDate = MyRec!EndDate
MyRec.MoveNext
Wend
End Function

 

[Tom Ellison]

Dear Steve:

I'll start simple and build up the query.

SELECT DR.End + 1 AS BeginRange
FROM DateRange DR
WHERE End < (SELECT MAX(End) FROM DateRange)

Pretty simple start. The beginning of each "free" range will be the day
after the end of a "committed" range. However, I do not want the LAST
ending date included, as there is no range for that.

SELECT DR.End + 1 AS BeginRange
FROM DateRange DR
WHERE NOT EXISTS (
SELECT * FROM DateRange DR1
WHERE DR.End + 1 BETWEEN DR1.Beginning AND DR1.End
AND NOT DR.Beginning BETWEEN DR1.Beginning AND DR1.End)
AND End < (SELECT MAX(End) FROM DateRange)

Here's the first leap. Filter out the committed ranges that do not have a
gap before the next committed range. I am looking to see whether there is
an adjacent committed range.

1. The day after the range (DR.End + 1) could fall within ANOTHER committed
range, hence:

WHERE DR.End + 1 BETWEEN DR1.Beginning AND DR1.End

2. But it must be ANOTHER range, not the same one:

AND NOT DR.Beginning BETWEEN DR1.Beginning AND DR1.End

Now this makes an assumption that you won't have two identical ranges.
Actually that may not be much of an assumption, as that wouldn't create any
new gaps anyway.

After giving this a bit of additional thought, I'm dropping that test. As I
think it through carefully, I see it should not be necessary. This may help
with performance:

SELECT End + 1 AS BeginRange,
(SELECT MIN(DR1.Beginning) - 1
FROM DateRange DR1
WHERE DR1.Beginning > DR.End + 1)
AS EndRange
FROM DateRange AS DR
WHERE NOT EXISTS (
SELECT * FROM DateRange DR1
WHERE DR.End + 1 BETWEEN DR1.Beginning AND DR1.End)
AND End < (SELECT MAX(End) FROM DateRange);

Here, I have dropped one of the criterion in the second subquery. There is
another subquery to find the end of the "free" range.

Are you familiar with subqueries and aliases? You might want to review
these in the online help and in any reference works you have. I find them
to be the core of advanced query work.

Tom Ellison

 

[PC Datasheet]

Tom,

Thank you very much for your help on this!! I looked through your
explanation and think I see your logic. I will now go back and study your
SQL to thoroughly understand how you implemented the logic.

<<Are you familiar with subqueries and aliases>>
I have used them at times but primarily to determine the first or last of
something. Do you have any recommendations as to references on subqueries?

Thanks!

Steve

 

[PC Datasheet]

<< group's charter >>

Look closely at what it says ---

 

[Tom Ellison]

Dear Steve:

I prefer a trip to a bookstore with a good technical section. Find books
with good information on specific areas where you want to build expertise,
and which are written to your level of expertise. You'll build capabilities
quickly that way. I have a few hundred books now myself. Come by and take
a look!

Tom Ellison

 

[Bob Quintal]

<< group's charter >>

Look closely at what it says ---

I have, several times. It doesn't authorize advertising.
Since usenet general charter forbids advertising except where
authorized in a group charter....

Thou shalt not advertise.

say Amen.

 

[John Marshall, MVP]

If you were to follow the group's charter and stop advertising,
I'm sure things would run a lot smoother for you.

Bob Quintal

Why? He does not follow the guidelines for abusive behaviour.

John... Visio MVP
..

 

[PC Datasheet]

You are nothing but a low life A$$!! You speak of abusive behaviour but you
absolutely make no contributions to this newsgroup but you feel free to
pollute this newsgroup with your worthless garbage!!!!!!!! You are a
disgrace to what MVP stands for!!!

 

[John Marshall, MVP]

Thank you for proving my point.

John... Visio MVP