Find Missing Date Ranges

  • Thread starter Thread starter PC Datasheet
  • Start date Start date
Tom,

Would you help me again, please ---

Beginning End TransactionType
4/1/06 4/9/06 A
4/7/06 4/11/06 A
4/14/06 4/17/06 A
4/18/06 4/21/06 A
426/06 4/30/06 A
4/1/06 4/5/06 B
4/7/06 4/13/06 B
4/16/06 4/17/06 B
4/18/06 4/23/06 B
426/06 4/30/06 B

I added additional data to your test data. I also added a TransactionType
field with the original test data having a value A and the new test data
having a value B. How would you modify your solution to determine the
missing date ranges for each TransactionType? The results would be:

BeginRange EndRange TransactionType
4/12/2006 4/13/2006 A
4/22/2006 4/25/2006 A
4/6/2006 4/6/2006 B
4/14/2006 4/15/2006 B
4/24/2006 4/25/2006 B

Thank you!

Steve
 
Dear Steve:

Pretty simple really:

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

First, I added the TransactionType to the select list. Next, I correlated
on it in both subqueries. Is this what you needed?

Tom Ellison
 
Tom,

Once again it works great! Yes, just what I needed.

Thank you for your help again.

Steve
 
Tom,

I am fascinated by what you have shown me!!!

You used the concept "correlated subqueries".
Would you please briefly explain what that means and when do you use it.

Thank you very much!!!

Steve
 
Keith,

Thank you for responding!

I appreciate the help and as important I appreciate the politeness.

Steve
 
PC Datasheet said:
Keith,

I appreciate the help and as important I appreciate the politeness.
Click to expand...

That's nicely hypocritical of you. You hold the record for being impolite in
these newsgroups.

John... Visio MVP
 
You are an A$$!! How's that for being polite? You don't deserve any
courtesy!! You are an embarassment to what MVP stands for.
 
Dear Steve:

A subquery is a query embedded within another query, set of by a set of
parens. References in the WHERE clause that filter it using values obtained
from the outer query are the means of correlation.

I use them every day. : )

Seriously, a correlated subquery is a substitute for domain function
(DSum(), DCount(), etc.) which is more portable. Much of the SQL I write
would work under SQL Server and other database engines. If you use the
domain functions, you will have greater portabilitiy issues. But you can to
much more complex things with subqueries than you can with domain functions.

Subqueries are useful in EXISTS clauses and in the SELECT clause when you
want a value not in the current row. In the query I provided you, beginning
at line 2, a subquery is used to find the minimum value of all those rows
whose Beginning value is greater than the current End value + 1, that is:

look at all the rows whose Beginning is larger than the current End value,
and choose the smallest

Actually, I believe the WHERE clause has already determined that the "gap"
is such that the current row's End value and the nearest larger Beginning
value is more than 1 day, so the + 1 is probably unnecessary. However, I
find this reads better anyway, as it emphasized the 2 day minimum
difference. That's a documentation kind of choice, not a functional
necessity. Adding 1 is so very quick that I don't expect there would be any
noticable change in performance.

May I point out the final subquery:

End < (SELECT MAX(End) FROM DateRange)

This is an uncorrelated subquery. It could easily have been done with
DMax(), right?

I think the whole thing could have been written with domain functions
instead of subqueries. However, there are cases when this cannot be done.

If you needed to include a value from the next "range" of dates, coming from
some other column than its Beginning, say its ending value, then a subquery
would have been unavoidable. Add this column:

(SELECT DR2.End
FROM DateRange DR2
WHERE DR2.Beginning =
(SELECT MIN(DR1.Beginning)
FROM DateRange DR1
WHERE DR1.TransactionType = DR.TransactionType
AND DR1.Beginning > DR.End + 1)
AND DR2.TransactionType = DR1.TransactionType)
AS NextEnd

The above nests two subqueries. The inner one returns the Beginning value
of the immediately following date range. The outer one returns the End
value of that following range.

A subquery returning a value in the SELECT clause can only return one value,
that is, one column and one row. If there were two ranges with the same
Beginning date, and that were the following range in the above, then the
subquery would return two rows, and you'd get a runtime error for this
query. If you put MIN(DR2.End) in the first line above, that would
eliminate this potential problem, although it is not clear whether this
would be the right answer.

Jet has an historical problem with the above construction on occasion. This
is a bug, or at least a limitation on its capacity. If I didn't screw it
up, it would work very well indeed in MSDE, but with the same limitation on
returning just one row.

Within each of the sets of date ranges for various TransactionType(s) there
are likely constraints that could be applied so no two ranges within a set
(based on TransactionType) would overlap. If built in MSDE, such
constraints can be programmed. In Jet, I doubt it. You should perhaps
consider a query that detects such overlaps and report them. That would be
a good exercise in the use of subqueries! Go for it!

Tom Ellison
 
John Marshall said:
That's nicely hypocritical of you. You hold the record for being impolite
in these newsgroups.
Click to expand...

Indeed ... and it seems he also missed the subtlety of my put-down. Just in
case anyone else did, the phrase "I thought every experienced developer
worth his reasonable fee knew that" springs to mind.

Keith.
 
I have not been a party to all these arguments but please stop
advertising or go away.

This thread started out as a reasonable question with a reasonable
answer. When I looked at it though, I saw your first response and
then a big tree of back and forth that has zero to do with the topic.

Please cut it out or get lost.

Thank you.

Bookreader
 
Thanks again for your help, Tom!

You have shown subqueries to be a powerful tool! I see why you use them
daily.

Steve
 
PC said:
You have shown subqueries to be a powerful tool! I see why you use them
daily.
Click to expand...

Hi Steve - they are a pretty common and useful thing for something like
a schema where you have Work Tickets (say for a repair shop or home
heating/air conditioning service) that have separate tables for:

Labour,
Parts,
Other Charges

For totals on a work ticket is a simple matter of

SELECT

wt_number,
wt_pk,
(SELECT sum(Labour_Hours) from labour where labour_wt_fk = wt_pk) as Hours,
(SELECT sum(Labour_Cost) from labour where labour_wt_fk = wt_pk) as
[Labour Cost],
(SELECT sum(Parts_Cost) from parts where parts_wt_fk = wt_pk) as [Parts
Cost],
(SELECT sum(Other_Cost) from Labour where other_wt_fk = wt_pk) as [Other
Cost]

FROM

work_tickets

WHERE

<whatever - if you need transaction based date ranges they need to be
introduced in the above subqueries, not here>

I don't know about the godawful help in later versions, but A97's great
help file had a very good section subqueries.
 
Hi Tim,

Thanks for the example! It leads to two questions ---
1. Could your example be turned into a crosstab query to show Labor, Parts
and Othercharges in separate columns and then for the rows show different
jobs?

2. << if you need transaction based date ranges they need to be introduced
in the above subqueries>>
Can a subquery be embedded in another subquery? How about a correlated
subquery?

Yes, I looked at the Access97 Help file on subqueries. It introduces and
explains the predicates which I found very helpful.

Steve



Tim Marshall said:
PC said:
You have shown subqueries to be a powerful tool! I see why you use them
daily.
Click to expand...

Hi Steve - they are a pretty common and useful thing for something like a
schema where you have Work Tickets (say for a repair shop or home
heating/air conditioning service) that have separate tables for:

Labour,
Parts,
Other Charges

For totals on a work ticket is a simple matter of

SELECT

wt_number,
wt_pk,
(SELECT sum(Labour_Hours) from labour where labour_wt_fk = wt_pk) as
Hours,
(SELECT sum(Labour_Cost) from labour where labour_wt_fk = wt_pk) as
[Labour Cost],
(SELECT sum(Parts_Cost) from parts where parts_wt_fk = wt_pk) as [Parts
Cost],
(SELECT sum(Other_Cost) from Labour where other_wt_fk = wt_pk) as [Other
Cost]

FROM

work_tickets

WHERE

<whatever - if you need transaction based date ranges they need to be
introduced in the above subqueries, not here>

I don't know about the godawful help in later versions, but A97's great
help file had a very good section subqueries.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Click to expand...
 
PC said:
Thanks for the example! It leads to two questions ---
1. Could your example be turned into a crosstab query to show Labor, Parts
and Othercharges in separate columns and then for the rows show different
jobs?
Click to expand...

Not sure - I do mostly Oracle and am currently using Jet for hobby
related apps. Oracle doesn't have cross tab queries, so I'm not sure.
You'd have to have a go at it.

However, to do what you're talking about, just the sql I gave would give
you precisely what you wanted unless there were some other tables and
joins in the main select that would cause jobs (work tickets was what i
called them) that caused a job to appear multiple times.
Can a subquery be embedded in another subquery?
Click to expand...

Yes, but, in Oracle at least, any alias or identifier in the main select
seems to only be identified the next level down. Can't really think of
any examples now...
How about a correlated
subquery?
Click to expand...

I don't use correlated sub queries (for Oracle, anyway, and probably the
same definition in Jet, these use the exists/not exists operator in the
where clause) and prefer to use multiple row sub queries in the where
clause.
 
Kiss my A$$!!!

It wasn't until the scum of this newsgroup, Arno R, John Marshall and Larry
Linson interjected their venom that this thread degraded.
 
the sequence of these two posts is MOST interesting. You start by
biting the very hand that feeds you, and then you turn around and ask
for more food...

Is there something I'm missing here? For a minute you sounded like a
new Steve. Well, for a minute anyway. Shame. It was nice while it
lasted.
 
the sequence of these two posts is MOST interesting. You start by
biting the very hand that feeds you, and then you turn around and ask
for more food...

Is there something I'm missing here? For a minute you sounded like a
new Steve. Well, for a minute anyway. Shame. It was nice while it
lasted.
Click to expand...

Tom Ellison has proven here my previous impression of him - that he has the
patience and forbearance of a saint, as well as a truly impressive knowledge
of databases, especially SQL.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Time Logging Routine? 1
Suming fields in queries 4
Amazon are rubbish 14
Daily Clock Rings 5
Dates 4
How to validate data for new and past records being entered 2
Find then highlight in yellow 6
Help! 1

Back
Top