时间戳

首页 > 解决方案 > 具有多个日期字段的每个日期的 SQL COUNT

sql - 具有多个日期字段的每个日期的 SQL COUNT

问题描述

我有下表和查询，它没有给出正确的结果。

JOB 表有 Open、Finish 和 Closed 日期。

现在我需要按每个日期和位置在所选日期组之间提取打开、已完成和已关闭作业的计数。

请帮助我得到如下预期结果中的结果

+-------+-----------+------------+-----------+----------+
| JOB_id| DateOpen  | DateFinish | DateClose | Location |
+-------+-----------+------------+-----------+----------+
|   100 | 16-Dec-18 | 18-Dec-18  | 19-Dec-18 | A        |
|   101 | 16-Dec-18 | 18-Dec-18  | 19-Dec-18 | A        |
|   102 | 17-Dec-18 | 19-Dec-18  | 20-Dec-18 | C        |
|   103 | 10-Dec-18 | 11-Dec-18  | 16-Dec-18 | D        |
|   104 | 17-Dec-18 | 19-Dec-18  | 18-Dec-18 | E        |
+-------+-----------+------------+-----------+----------+

询问：

SELECT count(DateOpen) as Opened,
       count(DateFinish) as Finised,
      count(DateClose) as Closed,
      (DateOpen) as Date 
FROM JOBS
WHERE DateOpen BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/17/2018')
group by DateOpen

预期结果：

+-----------+------+----------+--------+----------+
|   Date    | Open | Finished | Closed | Location |
+-----------+------+----------+--------+----------+
| 16-Dec-18 |    2 |        0 |      0 | A        |
| 16-Dec-18 |    0 |        0 |      1 | D        |
| 17-Dec-18 |    1 |        0 |      0 | C        |
| 17-Dec-18 |    1 |        0 |      0 | E        |
+-----------+------+----------+--------+----------+

标签： sqlsql-serverdatetimesql-server-2012sql-server-2014

解决方案

您可以将所有打开、完成和关闭日期放在一个列中，然后将其加入您的工作表：

DECLARE @date1 AS DATE = '2018-12-16';
DECLARE @date2 AS DATE = '2018-12-17';

WITH dates(date) AS (
    SELECT DateOpen FROM jobs
    UNION
    SELECT DateFinish FROM jobs
    UNION
    SELECT DateClose FROM jobs
)
SELECT dates.date
     , Location
     , COUNT(CASE WHEN dates.date = DateOpen THEN 1 END) AS Opened
     , COUNT(CASE WHEN dates.date = DateFinish THEN 1 END) AS Finished
     , COUNT(CASE WHEN dates.date = DateClose THEN 1 END) AS Closed
FROM dates
LEFT JOIN jobs ON dates.date IN (DateOpen, DateFinish, DateClose)
WHERE dates.date BETWEEN @date1 AND @date2
GROUP BY dates.date
       , Location

结果：

| date       | Location | Opened | Finished | Closed |
|------------|----------|--------|----------|--------|
| 16/12/2018 | A        | 2      | 0        | 0      |
| 16/12/2018 | D        | 0      | 0        | 1      |
| 17/12/2018 | C        | 1      | 0        | 0      |
| 17/12/2018 | E        | 1      | 0        | 0      |

DB Fiddle 上的演示

推荐阅读