CODEWITHSUNDEEP
sql-serveraggregate-functions
Tayir
Tayir

Reputation: 15

sql conditional aggregate function with date type

I have table like:

    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');

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)

the result in sqlfiddle is:

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

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?

thanks!

Upvotes: 1

Views: 332

Answers (1)

Spock
Spock

Reputation: 4900

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

Upvotes: 1

Related Questions