复杂的日期范围(Complex Date Range)

复杂的日期范围(Complex Date Range)

我正在研究以下查询，无法弄清楚它的最后一部分。 我需要我的查询给我一个上一个业务和前一个工作日减去（ - ）28天之间的结果集。 （例如2015年10月28日到2015年10月28日-28之间的日期范围）到目前为止我写的查询只给了我第-28天（2015年9月30日）而不是之前的业务范围当天和前一个工作日-28。 我的研究显示了几种不同的方法，到目前为止，没有一种方法适合我。

SELECT SMBL, SUM(NET_FLOWS/1000000.00) FROM HISTORY WHERE DATE - 28 = DATE AND DATE = TO_DATE('10282015','MMDDYYYY') AND SYMBOL IN ('AAA','BBB') GROUP BY SMBL

I'm working on the following query and cant figure out the final piece of it. I need my query to give me a result set between the previous business and the previous business day minus (-) 28 days. (e.g. date range between 10/28/2015 and 10/28/2015 -28) The query that I wrote so far is only giving me the -28th day (09/30/2015) and NOT a range in between the previous business day and the previous business day -28. My research shows a couple of different ways of doing it and so far none have worked for me.

SELECT SMBL, SUM(NET_FLOWS/1000000.00) FROM HISTORY WHERE DATE - 28 = DATE AND DATE = TO_DATE('10282015','MMDDYYYY') AND SYMBOL IN ('AAA','BBB') GROUP BY SMBL

最满意答案

首先，使用BETWEEN可以轻松完成日期范围，因此您可以快速解决问题：

WHERE DATE BETWEEN (SYSDATE-28) and (SYSDATE-1)

然后你意识到你的日期有时间组成部分，所以要包括所有昨天和28天所有你需要：

WHERE DATE >= TRUNC(SYSDATE)-28 AND DATE < TRUNC(SYSDATE)

然后我查看您的“前一工作日”规则并询问 - 您的工作日是什么时候？ 在星期一上一个星期五？ 还是周六？ 或者你是一个每周7天的生意？ 法定假日怎么样？ 那是28日历吗？ 或28个工作日？

啊业务规则。 魔鬼总是在那些细节......

First off, date ranges are easy using BETWEEN, so you do the quick solution:

WHERE DATE BETWEEN (SYSDATE-28) and (SYSDATE-1)

Then you realize your dates have time components, so to include all of yesterday and all of day-28 you need to:

WHERE DATE >= TRUNC(SYSDATE)-28 AND DATE < TRUNC(SYSDATE)

Then I look at your rule "previous BUSINESS day" and ask - what are your business days? On a Monday to go up to the previous Friday? Or Saturday? Or are you a 7-day-a-week business? How about statutory holidays? And is it 28 CALENDAR days back? Or 28 BUSINESS days?

Ahh business rules. The devil is always in those details....