date - 有没有办法计算日期范围内的天数?
问题描述
我是 VB/SQL 的初学者,几个月前我才开始学习,但我可以理解算法的逻辑,就像我以前做一些 Excel VBA 一样。
实际上,我正在设计一个数据库,我可以(希望)在其中跟踪每一位同事一年中的活动。
目标是(每月)比率 => 可计费天数 /(可计费 + 不可计费 - 缺席)
上下文:一个人可以是:在内部工作(不可计费),或在外部工作(可计费),或在假期(缺席)。
- 我有一个 [Planning] 表,其中存储以下数据:[Consultant_ID](链接到另一个表 [Consultant]、[Activity](具有上述三个选项的列表)、[Beginning_Date]、[End_Date]。
示例:顾问 1:从 01/01/2019 到 01/06/2019 在外部工作,从 02/06/2019 到 31/12/2019 在内部工作,从 02/03/2019 到 15/03/2019 假期
例如,有没有办法获得三月份的可计费比率?
我创建了 4 个查询(可能太多了?) 3 个查询:[Consultant_ID] [Activity] [Beginning_Date] [End_Date] [Ratio : Datediff("d";[Beginning_Date];[End_Date])。
对于每个查询: [活动标准]:一个在内部工作,一个在外部工作,一个不在。
对于 [Beginning_Date] 和 [End_Date] 条件:<=[输入开始日期],>=[输入结束日期]
第 4 次查询 [Consultant ID] [Billable] [Non billable] [Absent](并计划添加 [RATIO])。
问题是:Datediff 计算它发现的整个活动的日期,而不仅仅是我希望的 01/03/2019 和 31/03/2019 之间的日期。
我期望比率的输出为:所需期间的可计费天数/(可计费 + 不可计费 - 缺席)。
实际输出显示输入日期之间整个期间的可计费、不可计费和缺勤天数
因此,而不是 31 可计费,0 不可计费,15 缺席 它显示 180 可计费,0 不可计费,32 缺席
很抱歉这么长的帖子,这实际上是我的第一个,非常感谢你!我已经为此苦苦挣扎了整整一个星期
解决方案
我们首先需要计算出每一行的 maxBegin 和 minEnd 日期
SELECT
*,
(IIF (Beginning_Date > #3/1/2019#, Beginning_Date, #3/1/2019#) ) as maxBegin,
(IIF (End_Date < #3/31/2019#, End_Date, #3/31/2019#) ) as minEnd,
Datediff("d", maxBegin, minEnd) + 1 as theDiff
FROM Planning
Where Beginning_Date <= #3/31/2019# AND End_Date >= #3/1/2019#
然后用它来计算持续时间。注意:DateDiff 不计算两端,所以我们需要加上+1。
SELECT
Consultant_ID,
SUM(IIF (Activity = "Working Internally", Datediff("d", maxBegin, minEnd) +1, 0) ) as NonBillable,
SUM(IIF (Activity = "Working Externally", Datediff("d", maxBegin, minEnd) +1, 0) ) as Billable,
SUM(IIF (Activity = "Holidays", Datediff("d", maxBegin, minEnd) +1, 0) ) as Absent
FROM
(
SELECT
*,
(IIF (Beginning_Date > #3/1/2019#, Beginning_Date, #3/1/2019#) ) as maxBegin,
(IIF (End_Date < #3/31/2019#, End_Date, #3/31/2019#) ) as minEnd
FROM Planning
Where Beginning_Date <= #3/31/2019# AND End_Date >= #3/1/2019#
) as z
GROUP BY Planning.Consultant_ID;
最后,您需要通过参数将实际的开始/结束日期替换为 sql 以运行您的查询。另请注意,假期只有 14 天,而不是 15 天。
此外,您可以将比率计算直接添加到此 sql 中,并且只有一个查询。
推荐阅读
- reactjs - 无法在 Mac os 中使用 nvm 安装 nodejs 版本
- node.js - 如何在 POST 请求中发送 GET 请求的结果?
- scala - spark scala 条件连接替换空值
- forms - 当目标页面被重定向到另一个页面时,有没有办法通过表单传递数据?
- c# - Android 上的 Xamarin Profiler 错误(运行时太旧)
- jsf - 如何在执行ajax更新时将参数从primefaces传递给java bean
- javascript - Chrome/Edge 中的 document.title 更改延迟
- tensorflow - Keras 如何在自动编码器中计算 Model.fit() 的准确性?
- c++ - 在 Windows 中截屏会输出黑屏图像
- microsoft-edge - 浏览器启动时未加载解压扩展