查找缺少日期范围 [英] Find Missing Date Ranges
问题描述
交易数据包含日期范围：
开始结束
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
我正在寻找有关如何找到没有交易的日期范围的建议。
无交易。
4/12/06 - 4/13/06
4/22/06 - 4/25/06
谢谢！
史蒂夫
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
推荐答案
亲爱的史蒂夫：
我建议一个问题。它将使用某种方法，我将用b $ b来描述。
现在，您提供的数据在4-5,12-13日期间存在差距，和22-25。我希望
喜欢稍微更改数据以测试两个范围重叠的情况，以及它们相邻的一个情况（14- 17和
18-21）。
所以，我建议这个测试数据：
开始结束
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
查询将是：
SELECT DR.End + 1 AS BeginRange，
（SELECT MIN（DR1.Beginning） - 1
FROM DateRange DR1
WHERE DR1.Beginning> DR.End + 1）
AS EndRange
来自DateRange DR
什么时候不存在（
SELECT *来自DateRange DR1
WHERE DR.End + 1在DR1.Beginning和DR1.End之间
而不是DR.Beginning在DR1.Beginning和DR1.End之间开始）
AND End< （SELECT MAX（结束）FROM DateRange）
我得到的结果：
BeginRange EndRange
4 / 2006年4月13日
4/22/2006 4/25/2006
您需要更改我拥有DateRange的表名。最初，
请试试这个，没有其他变化。
你需要解释一下这是如何工作的吗？
Tom Ellison
" PC数据表" < FA *** @ email.com>在留言中写道
news：ju ****************** @ newsread1.news.atl.earth link.net ...
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...
交易数据以日期范围给出：
开始结束
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
我在寻找建议如何找到没有交易的日期范围。
4/12/06 - 4/13/06
4/22/06 - 4/25/06
谢谢！
Steve
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数据表" < FA *** @ email.com>写在
新闻：ju ****************** @ newsread1.news.atl.earth link.net：
"PC Datasheet" <fa***@email.com> wrote in
news:ju******************@newsread1.news.atl.earth link.net:
交易数据以日期范围给出：
开始结束
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
我在寻找建议关于如何找到日期范围
没有交易的地方。
4/12/06 - 4/13/06
4/22/06 - 4/25/06
谢谢！
Steve
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
我的方法如下，假设您有日期范围，而不是
确切日期。
建立一个日历表。离开加入你的交易开始
date。循环遍历数据集，在日历
表中设置一个标志，表示每个日期到该记录的结束日期。任何日期
没有国旗后你走了记录集没有
交易。
-
Bob Quintal
PA是我改变了我的电子邮件地址。
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，
感谢您的回复!!
既然您礼貌地回应，我想向您提供我之前的道歉
jabs在你身边我只是想让你知道在你对新闻组发表的每一篇文章中被嘲笑的感觉是什么？我知道你不喜欢Arno R，John
Marshall，Randy Harris和Keith Wilby，他们没有对这个新闻组做出任何贡献，但是在发布他们的诽谤时却感到有理由
垃圾在这里关于我。我对你的刺戳已经结束了。完成！
史蒂夫
" Bob Quintal" < RQ ****** @ sympatico.ca>在消息中写道
news：Xn ********************** @ 207.35.177.135 ...
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数据表 < FA *** @ email.com>在
新闻中写道：ju ****************** @ newsread1.news.atl.earth link.net：
"PC Datasheet" <fa***@email.com> wrote in
news:ju******************@newsread1.news.atl.earth link.net:
交易数据以日期范围给出：
开始结束
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
我正在寻找有关如何寻找的建议日期范围
没有交易的地方。
4/12/06 - 4/13/06
4/22/06 - 4/25/06
>谢谢！
史蒂夫
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
我的方法如下，因为你有日期范围，而不是确切的日期。
构建日历表。离开加入您的交易开始
日期。循环遍历数据集，在日历
表中设置一个标志，用于每个日期到该记录的结束日期。任何日期
没有旗帜，你走过记录集后没有
交易。
-
Bob Quintal
PA是y我修改了我的电子邮件地址。
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.
这篇关于查找缺少日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
