我正在研究以下查询,无法弄清楚它的最后一部分。 我需要我的查询给我一个上一个业务和前一个工作日减去( - )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 SMBLI'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....
更多推荐
发布评论