两个日期之间每天未结订单的 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屋！