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
67  7474 
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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net... 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
"PC Datasheet" <fa***@email.com> wrote in
news:ju******************@newsread1.news.atl.earth link.net: 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.
--
Bob Quintal
PA is y I've altered my email address.
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
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135... "PC Datasheet" <fa***@email.com> wrote in
news:ju******************@newsread1.news.atl.earth link.net:
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.
--
Bob Quintal
PA is y I've altered my email address.
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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl... 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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net... 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
* 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl... 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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net... 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
Maybe Tom can explain it to you. I hear his rates are very reasonable.
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
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
"PC Datasheet" <fa***@email.com> wrote in message
news:4f******************@newsread1.news.atl.earth link.net... 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl... 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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net... 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
Dear Randy:
I resent that. My rates are not at all reasonable! I can barely afford
groceries! : )
Tom Ellison
"Randy Harris" <pl****@send.no.spam> wrote in message
news:kk*******************@newssvr11.news.prodigy. com... * 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl... 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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net...
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
Maybe Tom can explain it to you. I hear his rates are very reasonable.
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Tom,
See responses below ---
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eb**************@TK2MSFTNGP05.phx.gbl... 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.
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
"PC Datasheet" <fa***@email.com> wrote in message
news:4f******************@newsread1.news.atl.earth link.net... 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl... 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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net...
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
"PC Datasheet" <fa***@email.com> wrote in
news:Be******************@newsread1.news.atl.earth link.net: 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 over. Done!
Steve
--
Bob Quintal
PA is y I've altered my email address.
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" <fa***@email.com> wrote in message
news:M_******************@newsread1.news.atl.earth link.net... Tom,
See responses below ---
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eb**************@TK2MSFTNGP05.phx.gbl... 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.
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
"PC Datasheet" <fa***@email.com> wrote in message
news:4f******************@newsread1.news.atl.earth link.net... 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl...
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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net...
> 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
>
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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:Op**************@TK2MSFTNGP05.phx.gbl... 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" <fa***@email.com> wrote in message
news:M_******************@newsread1.news.atl.earth link.net... Tom,
See responses below ---
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eb**************@TK2MSFTNGP05.phx.gbl... 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.
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
"PC Datasheet" <fa***@email.com> wrote in message
news:4f******************@newsread1.news.atl.earth link.net...
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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl...
> 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" <fa***@email.com> wrote in message
> news:ju******************@newsread1.news.atl.earth link.net...
>> 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
>>
>
>
<< group's charter >>
Look closely at what it says ---
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn*********************@207.35.177.135... "PC Datasheet" <fa***@email.com> wrote in
news:Be******************@newsread1.news.atl.earth link.net:
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 over. Done!
Steve
--
Bob Quintal
PA is y I've altered my email address.
"PC Datasheet" <fa***@email.com> wrote << group's charter >>
Look closely at what it says ---
It has been explained to you many times, with references, that basic USENET
rules prohibit advertising in any newsgroup, unless the charter of that
newsgroup specifically _permits_ it. The charter of
comp.databases.ms-access does not specifically permit advertising, as it
would have to for your interpretation to be correct.
Does it not seem odd to you that you are "out of step" with virtually all
who participate here when you try to stretch the rules, with the exception
of a very few who are more interested in creating disruption rather than
keeping the newsgroup useful? Are you _so_ hard up for business that you
have to violate the rules of the venue where you are trying to seek it,
misrepresent what others have done and written, and mislead people about how
many paying jobs you have gotten through the newsgroup? If so, you are
really pitiable, but being pitiable still does not give anyone carte blanche
to violate USENET rules and the charter.
Larry Linson
How pitiable was it when you came to me a few years back and asked me to
subcontract to you some of the work I was getting from newsgroup readers?
PC Datasheet
"Larry Linson" <bo*****@localhost.not> wrote in message
news:5rX2g.1017$0z.226@trnddc01... "PC Datasheet" <fa***@email.com> wrote
<< group's charter >>
Look closely at what it says ---
It has been explained to you many times, with references, that basic
USENET rules prohibit advertising in any newsgroup, unless the charter of
that newsgroup specifically _permits_ it. The charter of
comp.databases.ms-access does not specifically permit advertising, as it
would have to for your interpretation to be correct.
Does it not seem odd to you that you are "out of step" with virtually all
who participate here when you try to stretch the rules, with the exception
of a very few who are more interested in creating disruption rather than
keeping the newsgroup useful? Are you _so_ hard up for business that you
have to violate the rules of the venue where you are trying to seek it,
misrepresent what others have done and written, and mislead people about
how many paying jobs you have gotten through the newsgroup? If so, you are
really pitiable, but being pitiable still does not give anyone carte
blanche to violate USENET rules and the charter.
Larry Linson
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
"PC Datasheet" <fa***@email.com> wrote in message
news:6w****************@newsread2.news.atl.earthli nk.net... 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:Op**************@TK2MSFTNGP05.phx.gbl... 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" <fa***@email.com> wrote in message
news:M_******************@newsread1.news.atl.earth link.net... Tom,
See responses below ---
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eb**************@TK2MSFTNGP05.phx.gbl...
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.
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
"PC Datasheet" <fa***@email.com> wrote in message
news:4f******************@newsread1.news.atl.earth link.net...
> 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
>
>
>
> "Tom Ellison" <te******@jcdoyle.com> wrote in message
> news:eg*************@TK2MSFTNGP02.phx.gbl...
>> 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" <fa***@email.com> wrote in message
>> news:ju******************@newsread1.news.atl.earth link.net...
>>> 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
>>>
>>
>>
>
>
>
"PC Datasheet" <fa***@email.com> wrote in news:YyW2g.8416$sq5.1279
@newsread2.news.atl.earthlink.net: << 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.
--
Bob Quintal
PA is y I've altered my email address.
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn*********************@207.35.177.135...
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" <fa***@email.com> wrote How pitiable was it when you came to me a few years back and asked me to
subcontract to you some of the work I was getting from newsgroup readers?
You are despicable, not pitable, for continuing to try to misrepresent what
I and others have done, in this manner. Constant repetition of an untruth
will not make it so.
It's been thoroughly established that when you first came into this
newsgroup, you were seeking confirmation that what you had done to stiff a
subcontractor was OK.
I've never, ever been so hard up for work that I'd _take_ a subcontract from
someone who is known for stiffing subcontractors, much less, as you claim,
_seek_ one.
Your actions have made you a pariah here. Your false claims make you more
so.
I suppose you could claim that I never offer any help here, as you have
claimed about others who point out your violations, but even you know how
ludicrous you'd appear if you did that.
Larry Linson
"PC Datasheet" <fa***@email.com> wrote in
news:RQ*****************@newsread2.news.atl.earthl ink.net: How pitiable was it when you came to me a few years back and asked me
to subcontract to you some of the work I was getting from newsgroup
readers?
PC Datasheet
Steve
I don't believe this. If you think about my interaction with Larry over the
years, you will realize that if I don't believe it, it's unlikely anyone
will believe it. So just drop it, please; it's not winning friends and it's
not influencing anyone.
--
Lyle Fairfield
Liars are despicable and that makes you despicable. No matter how many times
you try and deny it, you did come to me a few years back and asked me to
subcontract to you some of the work I was getting from newsgroup readers.
That's a fact!!!
"Larry Linson" <bo*****@localhost.not> wrote in message
news:bp43g.1287$yI1.966@trnddc04... "PC Datasheet" <fa***@email.com> wrote
How pitiable was it when you came to me a few years back and asked me to
subcontract to you some of the work I was getting from newsgroup readers?
You are despicable, not pitable, for continuing to try to misrepresent
what I and others have done, in this manner. Constant repetition of an
untruth will not make it so.
It's been thoroughly established that when you first came into this
newsgroup, you were seeking confirmation that what you had done to stiff a
subcontractor was OK.
I've never, ever been so hard up for work that I'd _take_ a subcontract
from someone who is known for stiffing subcontractors, much less, as you
claim, _seek_ one.
Your actions have made you a pariah here. Your false claims make you more
so.
I suppose you could claim that I never offer any help here, as you have
claimed about others who point out your violations, but even you know how
ludicrous you'd appear if you did that.
Larry Linson
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" <la******@stonehenge.ca> wrote in message
news:OB**************@TK2MSFTNGP04.phx.gbl... "Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn*********************@207.35.177.135...
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
.
Lyle,
That's your perogative and that's fine. However, it is true, trust me! My
hand on a stack of bibles. Just think about Larry's egostistical, boisterous
replies over the years and it's easy to then believe. Then look at the
nature of his denials and it's even easier to believe. Liars are despicable
and that makes Larry despicable!!!
Steve
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119... "PC Datasheet" <fa***@email.com> wrote in
news:RQ*****************@newsread2.news.atl.earthl ink.net:
How pitiable was it when you came to me a few years back and asked me
to subcontract to you some of the work I was getting from newsgroup
readers?
PC Datasheet
Steve
I don't believe this. If you think about my interaction with Larry over
the
years, you will realize that if I don't believe it, it's unlikely anyone
will believe it. So just drop it, please; it's not winning friends and
it's
not influencing anyone.
--
Lyle Fairfield
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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl... 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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net... 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
PC Datasheet wrote: Just think about Larry's egostistical, boisterous
replies over the years
You must be talking about another poster. I was here a considerable
amount of time before you began posting and then and now, Larry's been
nothing but polite to all save for a few people who occasionally or
continually violate the group charter.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
"PC Datasheet" <fa***@email.com> schreef in bericht news:uk*****************@newsread2.news.atl.earthl ink.net... That's your perogative and that's fine. However, it is true, trust me! My
hand on a stack of bibles.
<sigh>
Trusting you, even with your 'hand-on-bible' (sic) would be very unwise.
You are such a filthy liar and a scum, that it's almost unbelievable!!
**************************************************
Not only these and other false accusations to Larry and others, but also this last week ...
-- you have been complaining again that I sent emails to your office...
-- you have been posting messages in these newsgroups, saying that I love (young!) boys...
-- you revealed my real emailaddress in the newsgroups here in a thread where at that time I not even participated...
You invited people to 'hook up' with me.
I consider this particularly *unpleasant* (sic) behaviour.
This afternoon I got the first email in response of this from an unknown person in France.
You are a resource indeed, a 'resource in lowlife' that is! and you almost make me puke.
Please, please take your pills and get lost!!
Arno R
You don't know anything at all about what Larry does, scumbag!! You weren't
there!! You don't know anything at all about anything but how to post filth
like your post here. All anyone has to do is look at the language you use.
You make absolutely no contribution to this newsgroup but pollute it with
your filthy garbage.
You are nothing but an obsessed A$$!! Go back down the hole you came out
of!!!!!!!!!!!!
"Arno R" <ar***********@tiscali.nl> wrote in message
news:44**********************@text.nova.planet.nl. ..
"PC Datasheet" <fa***@email.com> schreef in bericht
news:uk*****************@newsread2.news.atl.earthl ink.net... That's your perogative and that's fine. However, it is true, trust me! My
hand on a stack of bibles.
<sigh>
Trusting you, even with your 'hand-on-bible' (sic) would be very unwise.
You are such a filthy liar and a scum, that it's almost unbelievable!!
**************************************************
Not only these and other false accusations to Larry and others, but also
this last week ...
-- you have been complaining again that I sent emails to your office...
-- you have been posting messages in these newsgroups, saying that I love
(young!) boys...
-- you revealed my real emailaddress in the newsgroups here in a thread
where at that time I not even participated...
You invited people to 'hook up' with me.
I consider this particularly *unpleasant* (sic) behaviour.
This afternoon I got the first email in response of this from an unknown
person in France.
You are a resource indeed, a 'resource in lowlife' that is! and you almost
make me puke.
Please, please take your pills and get lost!!
Arno R
More lies. With a unique name like Arno, it is simple to do a search, just
on this newsgroup, and find several useful posts.
As to pollution, you are the only one who is spewing pseudo profanity and
insults.
John... Visio MVP
"PC Datasheet" <fa***@email.com> wrote in message
news:Mu****************@newsread3.news.atl.earthli nk.net... You don't know anything at all about what Larry does, scumbag!! You
weren't there!! You don't know anything at all about anything but how to
post filth like your post here. All anyone has to do is look at the
language you use. You make absolutely no contribution to this newsgroup
but pollute it with your filthy garbage.
You are nothing but an obsessed A$$!! Go back down the hole you came out
of!!!!!!!!!!!!
"Arno R" <ar***********@tiscali.nl> wrote in message
news:44**********************@text.nova.planet.nl. ..
"PC Datasheet" <fa***@email.com> schreef in bericht
news:uk*****************@newsread2.news.atl.earthl ink.net...
That's your perogative and that's fine. However, it is true, trust me! My
hand on a stack of bibles.
<sigh>
Trusting you, even with your 'hand-on-bible' (sic) would be very unwise.
You are such a filthy liar and a scum, that it's almost unbelievable!!
**************************************************
Not only these and other false accusations to Larry and others, but also
this last week ...
-- you have been complaining again that I sent emails to your office...
-- you have been posting messages in these newsgroups, saying that I love
(young!) boys...
-- you revealed my real emailaddress in the newsgroups here in a thread
where at that time I not even participated...
You invited people to 'hook up' with me.
I consider this particularly *unpleasant* (sic) behaviour.
This afternoon I got the first email in response of this from an unknown
person in France.
You are a resource indeed, a 'resource in lowlife' that is! and you
almost make me puke.
Please, please take your pills and get lost!!
Arno R
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
"PC Datasheet" <fa***@email.com> wrote in message
news:4A***************@newsread2.news.atl.earthlin k.net... 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl... 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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net... 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
OK, do a search for the last three months and see how many you come up with.
Then go down the same hole with Arno and kiss his A$$ as you go!!!
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:GZ********************@magma.ca... More lies. With a unique name like Arno, it is simple to do a search, just
on this newsgroup, and find several useful posts.
As to pollution, you are the only one who is spewing pseudo profanity and
insults.
John... Visio MVP
"PC Datasheet" <fa***@email.com> wrote in message
news:Mu****************@newsread3.news.atl.earthli nk.net... You don't know anything at all about what Larry does, scumbag!! You
weren't there!! You don't know anything at all about anything but how to
post filth like your post here. All anyone has to do is look at the
language you use. You make absolutely no contribution to this newsgroup
but pollute it with your filthy garbage.
You are nothing but an obsessed A$$!! Go back down the hole you came out
of!!!!!!!!!!!!
"Arno R" <ar***********@tiscali.nl> wrote in message
news:44**********************@text.nova.planet.nl. ..
"PC Datasheet" <fa***@email.com> schreef in bericht
news:uk*****************@newsread2.news.atl.earthl ink.net...
That's your perogative and that's fine. However, it is true, trust me!
My
hand on a stack of bibles.
<sigh>
Trusting you, even with your 'hand-on-bible' (sic) would be very unwise.
You are such a filthy liar and a scum, that it's almost unbelievable!!
**************************************************
Not only these and other false accusations to Larry and others, but also
this last week ...
-- you have been complaining again that I sent emails to your office...
-- you have been posting messages in these newsgroups, saying that I love
(young!) boys...
-- you revealed my real emailaddress in the newsgroups here in a thread
where at that time I not even participated...
You invited people to 'hook up' with me.
I consider this particularly *unpleasant* (sic) behaviour.
This afternoon I got the first email in response of this from an unknown
person in France.
You are a resource indeed, a 'resource in lowlife' that is! and you
almost make me puke.
Please, please take your pills and get lost!!
Arno R
So if there is at least one contribution by Arno in the last three months
then you will concede that you lied?
John... Visio MVP
"PC Datasheet" <fa***@email.com> wrote in message
news:8S*****************@newsread2.news.atl.earthl ink.net... OK, do a search for the last three months and see how many you come up
with.
--- More of stevies childish rant clipped ---
Tom,
Once again it works great! Yes, just what I needed.
Thank you for your help again.
Steve
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:OO****************@TK2MSFTNGP02.phx.gbl... 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
"PC Datasheet" <fa***@email.com> wrote in message
news:4A***************@newsread2.news.atl.earthlin k.net... 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl... 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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net...
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
NO!!! Do the search and show all the newsgroup readers how many
contributions the A$$ Arno R made in the last six months then show how many
posts of his filthy garbage he made.
When you're done, follow Arno R down the hole and kiss his A$$ as you go.
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:Z8******************************@magma.ca... So if there is at least one contribution by Arno in the last three months
then you will concede that you lied?
John... Visio MVP
"PC Datasheet" <fa***@email.com> wrote in message
news:8S*****************@newsread2.news.atl.earthl ink.net... OK, do a search for the last three months and see how many you come up
with.
--- More of stevies childish rant clipped ---
And by the way, Don Mellon is a saint next to Larry!!!!
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119... "PC Datasheet" <fa***@email.com> wrote in
news:RQ*****************@newsread2.news.atl.earthl ink.net:
How pitiable was it when you came to me a few years back and asked me
to subcontract to you some of the work I was getting from newsgroup
readers?
PC Datasheet
Steve
I don't believe this. If you think about my interaction with Larry over
the
years, you will realize that if I don't believe it, it's unlikely anyone
will believe it. So just drop it, please; it's not winning friends and
it's
not influencing anyone.
--
Lyle Fairfield
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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:OO****************@TK2MSFTNGP02.phx.gbl... 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
"PC Datasheet" <fa***@email.com> wrote in message
news:4A***************@newsread2.news.atl.earthlin k.net... 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl... 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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net...
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
xxx posts as StopThisAdvertising + a website 'devoted' to you is a huge contribution to the newsgroups IMO.
Filthy garbage is something we see mainly in your posts.
Arno R
"PC Datasheet" <fa***@email.com> schreef in bericht news:gX*****************@newsread2.news.atl.earthl ink.net... NO!!! Do the search and show all the newsgroup readers how many
contributions the A$$ Arno R made in the last six months then show how many
posts of his filthy garbage he made.
When you're done, follow Arno R down the hole and kiss his A$$ as you go.
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:Z8******************************@magma.ca... So if there is at least one contribution by Arno in the last three months
then you will concede that you lied?
John... Visio MVP
"PC Datasheet" <fa***@email.com> wrote in message
news:8S*****************@newsread2.news.atl.earthl ink.net... OK, do a search for the last three months and see how many you come up
with.
--- More of stevies childish rant clipped ---
"PC Datasheet" <fa***@email.com> wrote in message
news:YM****************@newsread2.news.atl.earthli nk.net... 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!!!
Bread and butter stuff for Oracle developers ... http://www.databasejournal.com/featu...le.php/3485291
Keith.
Keith,
Thank you for responding!
I appreciate the help and as important I appreciate the politeness.
Steve
"Keith Wilby" <he**@there.com> wrote in message
news:44**********@glkas0286.greenlnk.net... "PC Datasheet" <fa***@email.com> wrote in message
news:YM****************@newsread2.news.atl.earthli nk.net... 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!!!
Bread and butter stuff for Oracle developers ...
http://www.databasejournal.com/featu...le.php/3485291
Keith.
Your opinion is as worthless as the filthy garbage you pollute this
newsgroup with.
All you are doing is demonstrating to all newsgroup readers what an obsessed
A$$ you are. Your filthy garbage makes no contribution to this newsgroup.
"StopThisAdvertising" <StopThisAdvertising@DataShit> wrote in message
news:44**********************@text.nova.planet.nl. ..
xxx posts as StopThisAdvertising + a website 'devoted' to you is a huge
contribution to the newsgroups IMO.
Filthy garbage is something we see mainly in your posts.
Arno R
"PC Datasheet" <fa***@email.com> schreef in bericht
news:gX*****************@newsread2.news.atl.earthl ink.net... NO!!! Do the search and show all the newsgroup readers how many
contributions the A$$ Arno R made in the last six months then show how
many
posts of his filthy garbage he made.
When you're done, follow Arno R down the hole and kiss his A$$ as you go.
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:Z8******************************@magma.ca... So if there is at least one contribution by Arno in the last three months
then you will concede that you lied?
John... Visio MVP
"PC Datasheet" <fa***@email.com> wrote in message
news:8S*****************@newsread2.news.atl.earthl ink.net... OK, do a search for the last three months and see how many you come up
with.
--- More of stevies childish rant clipped ---
OK, where are the search results for contributions to this newsgroup by your
A$$ pal Arno R in the last three months?
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:Z8******************************@magma.ca... So if there is at least one contribution by Arno in the last three months
then you will concede that you lied?
John... Visio MVP
"PC Datasheet" <fa***@email.com> wrote in message
news:8S*****************@newsread2.news.atl.earthl ink.net... OK, do a search for the last three months and see how many you come up
with.
--- More of stevies childish rant clipped ---
"PC Datasheet" <fa***@email.com> wrote in message
news:YP*****************@newsread1.news.atl.earthl ink.net... Keith,
I appreciate the help and as important I appreciate the politeness.
That's nicely hypocritical of you. You hold the record for being impolite in
these newsgroups.
John... Visio MVP
"PC Datasheet" <fa***@email.com> wrote in message
news:aU******************@newsread1.news.atl.earth link.net... Your opinion is as worthless as the filthy garbage you pollute this
newsgroup with.
All you are doing is demonstrating to all newsgroup readers what an
obsessed A$$ you are. Your filthy garbage makes no contribution to this
newsgroup.
Great example of polite behaviour.
John... Visio MVP
I appreciate your politeness
Arno R
"PC Datasheet" <fa***@email.com> schreef in bericht news:aU******************@newsread1.news.atl.earth link.net... Your opinion is as worthless as the filthy garbage you pollute this
newsgroup with.
All you are doing is demonstrating to all newsgroup readers what an obsessed
A$$ you are. Your filthy garbage makes no contribution to this newsgroup.
"StopThisAdvertising" <StopThisAdvertising@DataShit> wrote in message
news:44**********************@text.nova.planet.nl. ..
xxx posts as StopThisAdvertising + a website 'devoted' to you is a huge
contribution to the newsgroups IMO.
Filthy garbage is something we see mainly in your posts.
Arno R
"PC Datasheet" <fa***@email.com> schreef in bericht
news:gX*****************@newsread2.news.atl.earthl ink.net... NO!!! Do the search and show all the newsgroup readers how many
contributions the A$$ Arno R made in the last six months then show how
many
posts of his filthy garbage he made.
When you're done, follow Arno R down the hole and kiss his A$$ as you go.
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:Z8******************************@magma.ca... So if there is at least one contribution by Arno in the last three months
then you will concede that you lied?
John... Visio MVP
"PC Datasheet" <fa***@email.com> wrote in message
news:8S*****************@newsread2.news.atl.earthl ink.net...
OK, do a search for the last three months and see how many you come up
with.
--- More of stevies childish rant clipped ---
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.
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:uK*************@TK2MSFTNGP03.phx.gbl... "PC Datasheet" <fa***@email.com> wrote in message
news:YP*****************@newsread1.news.atl.earthl ink.net... Keith,
I appreciate the help and as important I appreciate the politeness.
That's nicely hypocritical of you. You hold the record for being impolite
in these newsgroups.
John... Visio MVP
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:uK*************@TK2MSFTNGP03.phx.gbl... "PC Datasheet" <fa***@email.com> wrote in message
news:YP*****************@newsread1.news.atl.earthl ink.net... Keith,
I appreciate the help and as important I appreciate the politeness.
That's nicely hypocritical of you. You hold the record for being impolite
in these newsgroups.
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.
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
"PC Datasheet" <fa***@email.com> wrote in message
news:YM****************@newsread2.news.atl.earthli nk.net... 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:OO****************@TK2MSFTNGP02.phx.gbl... 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
"PC Datasheet" <fa***@email.com> wrote in message
news:4A***************@newsread2.news.atl.earthlin k.net... 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl...
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" <fa***@email.com> wrote in message
news:ju******************@newsread1.news.atl.earth link.net...
> 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
>
On Mon, 24 Apr 2006 02:17:28 GMT, "PC Datasheet" <fa***@email.com>
wrote:
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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:u4**************@TK2MSFTNGP04.phx.gbl... 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
"PC Datasheet" <fa***@email.com> wrote in message
news:YM****************@newsread2.news.atl.earthli nk.net... 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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:OO****************@TK2MSFTNGP02.phx.gbl... 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
"PC Datasheet" <fa***@email.com> wrote in message
news:4A***************@newsread2.news.atl.earthlin k.net...
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
"Tom Ellison" <te******@jcdoyle.com> wrote in message
news:eg*************@TK2MSFTNGP02.phx.gbl...
> 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" <fa***@email.com> wrote in message
> news:ju******************@newsread1.news.atl.earth link.net...
>> 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
>>
>
>
PC Datasheet wrote: You have shown subqueries to be a powerful tool! I see why you use them
daily.
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
OK, where are the search results for contributions to this newsgroup by your
A$$ pal Arno R in the last three months?
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:xc******************************@magma.ca... "PC Datasheet" <fa***@email.com> wrote in message
news:aU******************@newsread1.news.atl.earth link.net... Your opinion is as worthless as the filthy garbage you pollute this
newsgroup with.
All you are doing is demonstrating to all newsgroup readers what an
obsessed A$$ you are. Your filthy garbage makes no contribution to this
newsgroup.
Great example of polite behaviour.
John... Visio MVP
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: bobb |
last post by:
Is there a way to select count(*) by grouping by date, and having multiple
date ranges?
combining...
select field,count(*) from table where datefield > 2004/1/1 and datefield <
2004/1/31
and...
|
by: Steve Elliott |
last post by:
I have a query set up to gather together data between two specified dates.
Shown in the query column as:
Between #24/09/2004# And #01/10/2004#
Is it possible to enter several different date...
|
by: Justin Emlay |
last post by:
I'm hopping someone can help me out on a payroll project I need to
implement.
To start we are dealing with payroll periods. So we are dealing with an
exact 10 days (Monday - Friday, 2 weeks).
...
|
by: dfetrow410 |
last post by:
Anyone have some code that will do this?
Dave
|
by: norma.j.hildebrand |
last post by:
I have a database that has a field (performance standard), every
year the standard changed which was not a problem since we start out
each year with a blank database and just change the standards...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: nia12 |
last post by:
Hi there,
I am very new to Access so apologies if any of this is obvious/not clear.
I am creating a data collection tool for health care employees to complete. It consists of a number of...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
| |
