日期类型的SQL条件聚合函数 [英] sql conditional aggregate function with date type

问题描述

我有这样的表：

    CREATE TABLE myissues 
        (
         id int IDENTITY(1,1) primary key, 
         title varchar(20), 
         status varchar(30),
         submitdate datetime,
         updatedate datetime
        );

    INSERT INTO myissues
    (title, status,submitdate,updatedate)
    VALUES
    ('issue1', 'closed','2014-01-01 07:59:59.000','2014-01-02 10:59:59.000'),
    ('issue2', 'closed','2014-01-01 08:59:59.000','2014-01-02 12:59:59.000'),
    ('issue3', 'closed','2014-01-01 09:59:59.000','2014-01-02 10:59:59.000'),
    ('issue4', 'closed','2014-01-02 07:59:59.000','2014-01-03 10:59:59.000'),
    ('issue5', 'closed','2014-01-02 08:59:59.000','2014-01-03 11:59:59.000'),
    ('issue6', 'closed','2014-01-03 08:59:59.000','2014-01-03 12:59:59.000');

我想获取每天的问题计数，计数应该分为两个不同的类别：开放已提交并已关闭的状态为status =已关闭的问题和更新日期。
这是我的SQL脚本：

I want to get counts of the issues for each day and counts should be in two different categories: Open issue which is submitted and closed which is status='closed' and update date. here is my sql script:

SELECT 
    convert(nvarchar(10),submitdate,112) as Dates, 
    COUNTS_OPEN   = SUM(case when (submitdate > CONVERT(datetime, '2014-01-01 00:00:00.000') and submitdate < CONVERT(datetime, '2014-01-05 00:00:00.000') ) then 1 else 0 end), 
    COUNTS_CLOSED = SUM(case when (status='closed' and (updatedate > CONVERT(datetime, '2014-01-01 00:00:00.000') and updatedate < CONVERT(datetime, '2014-01-05 00:00:00.000')) ) then 1 else 0 end) 
FROM myissues
GROUP BY convert(nvarchar(10),submitdate,112)
order by convert(nvarchar(10),submitdate,112)

sqlfiddle中的结果是：

the result in sqlfiddle is:

DATES   COUNTS_OPEN COUNTS_CLOSED
20140101    3            3
20140102    2            2
20140103    1            1

如您所见，COUNTS_CLOSED的结果是错误的。上面列出的日期的正确结果应为0,3,3。
我想我没有将其正确分组。有人可以帮忙吗？

As you can see, the result is wrong for COUNTS_CLOSED. Correct result should be 0,3,3 for the listed dates above. I think I'm not grouping it correctly. Can anyone help?

谢谢！

推荐答案

您需要将关闭计数与主查询分开。尝试这个。 ..

You need to separate the closed count from the main query. Try this. ..

SELECT convert(nvarchar(10),submitdate,112) as Dates, 
COUNTS_OPEN   = COUNT(1), 
COUNTS_CLOSED = (SELECT COUNT(1) FROM myissues f WHERE f.status = 'closed' AND  convert(nvarchar(10),updatedate,112) = convert(nvarchar(10), m.submitdate,112))
FROM myissues m
GROUP BY convert(nvarchar(10),submitdate,112)
order by convert(nvarchar(10),submitdate,112)

请注意，如果在未提交问题的当天关闭问题，则此查询将不起作用。为此，您需要获取详尽的日期列表。

Be warned that this query will not work if the issue was closed on a day that there was no issue submitted. To do this properly you need to get a exhaustive list of dates.

尝试类似这样的事情...

Try something like this...

SELECT m.Dates, 
COUNTS_OPEN = (SELECT COUNT(1) FROM myissues f WHERE convert(nvarchar(10),submitdate,112) = m.Dates),
COUNTS_CLOSED = (SELECT COUNT(1) FROM myissues f WHERE f.status = 'closed' AND  convert(nvarchar(10),updatedate,112) = m.Dates)
FROM (
    SELECT convert(nvarchar(10),submitdate,112) as Dates
    FROM myissues
    UNION
    SELECT convert(nvarchar(10),updatedate,112)
    FROM myissues
) m
order by m.Dates

这篇关于日期类型的SQL条件聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！