两个日期之间每天未结订单的 SQL 计数 [英] SQL Count Of Open Orders Each Day Between Two Dates
问题描述
我尝试过搜索,但很可能我使用了错误的关键字,因为我找不到答案.
I've tried searching but it's likely I'm using the wrong keywords as I can't find an answer.
我正在尝试查找两个日期和员工之间未结订单的数量.我有一个显示员工列表的表格,另一个显示包含打开和关闭日期的订单列表以及日期表(如果有帮助).
I'm trying to find the number of orders that are open between two dates and by employee. I have one table that shows a list of employees, another that shows a list of orders that contains an open and close date and also a dates table if that helps.
加入的员工表和订单表将返回如下内容:
The employee and order tables joined will return something like:
employee order ref opened closed
a 123 01/01/2012 04/01/2012
b 124 02/01/2012 03/01/2012
a 125 02/01/2012 03/01/2012
我需要将这些数据转换为:
And I need to transform this data into:
Date employee Count
01/01/2012 a 1
02/01/2012 a 2
02/01/2012 b 1
03/01/2012 a 2
03/01/2012 b 1
04/01/2012 a 1
我正在从 SQL 服务器中提取数据.
I'm pulling the data from SQL server.
有什么想法吗?
谢谢
尼克
推荐答案
Join Dates
到 Employees
和 Orders
之间的连接结果,然后按日期和员工分组以获取计数,如下所示:
Join Dates
to the result of the join between Employees
and Orders
, then group by dates and employees to obtain the counts, something like this:
SELECT
d.Date,
o.Employee,
COUNT(*) AS count
FROM Employees e
INNER JOIN Orders o ON e.ID = o.Employee
INNER JOIN Dates d ON d.Date BETWEEN o.Opened AND o.Closed
GROUP BY
d.Date,
o.Employee
这篇关于两个日期之间每天未结订单的 SQL 计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!