Grouping COUNT by two date fields in same table

Hi, I'm trying something which I'm sure should be quite simple but I am having a bit of trouble. Basically I have a call logging table which has a PK of CallID and then a Received Date column (RecvdDate) and Closed Date column (ClosedDate).

I need to return a single set of results showing how many calls were received and closed on a particular date. (N.B all records will have a RecvdDate, but not all will have a ClosedDate (i.e if the job has not been completed)).

Now, I can get the information with two seperate queries no problem:

Query 1

SELECT RecvdDate, COUNT(Callid)

FROM CallLog

GROUP BY RecvdDate

ORDER BY RecvdDate

Query 2

SELECT ClosedDate, COUNT(Callid)

FROM CallLog

WHERE NOT ISNULL(ClosedDate, '') = ''

GROUP BY ClosedDate

ORDER BY ClosedDate

The problem is that I can't work out how to get the two counts to show together, grouped by each date, which I need for displaying on a single chart in SSRS. I'm thinking I might need a variable for the date to group by, but then i get lost

The ideal results set would look like this:

Date Total received Total Closed 28/02/2007 54 43 01/03/2007 22 21 02/03/2007 122 104 03/03/2007 33 41 04/03/2007 44 33 05/03/2007 76 56 06/03/2007 34 40 07/03/2007 87 80 08/03/2007 56 45 09/03/2007 42 31 10/03/2007 72 66

Can anybody help with this?

Thanks

Matt

try this one

select convert(varchar(10),RecvdDate,101) as [Date]
, count(RecvdDate) as TotalRecvd
, count(ClosedDate) as TotalClosed
from CallLog
group by
convert(varchar(10),RecvdDate,101)|||disregard my prev post.. try this one instead

select [Date]
, SUM(CASE WHEN Rem = 'Recieved' THEN Calls ELSE 0 END) AS TotalReceived
, SUM(CASE WHEN Rem = 'Closed' THEN Calls ELSE 0 END) AS TotalClosed
FROM (
select convert(varchar(10),RecvdDate,101) as [Date]
, count(RecvdDate) as Calls
, 'Recieved' as Rem
from CallLog
group by
convert(varchar(10),RecvdDate,101)
union all
select convert(varchar(10),ClosedDate,101) as [Date]
, count(ClosedDate) as Calls
, 'Closed' as Rem
from #temp
group by
convert(varchar(10),ClosedDate,101)
) CallLogs
GROUP BY
[Date]|||

Hi, thanks for the reply.

The problem with this solution (I had already tried something similar) is that it returns identical values for both Received and Closed calls, which I know is not the case. Here's the results I got:

04/01/2007 32 32 09/01/2007 62 62 10/01/2007 37 37 12/01/2007 45 45 16/01/2007 55 55 25/01/2007 77 77 02/02/2007 69 69 08/02/2007 106 106 11/02/2007 19 19 17/02/2007 13 13

For example, from manually searching table I know that for 17/02/2007 there were 16 Received calls and 13 Closed calls. However the SELECT statement we've tried doesn't include the three calls which were logged on 17/02/2007 but not closed.

I think the reason for this is because it is being GROUPED by RecvdDate, which doesn't make logical sense as it is possible that a call can be received one day and closed x number of days later.

Do you have any more ideas?

Thanks

Matt

|||

Okay thanks again, have seen your second post now and this solution has worked perfectly. I must admit I don't fully understand it, but it works!

Thanks a lot for your help.

Matt

|||

Matt:

Maybe a 1-pass select like this will work:

Code Snippet

declare @.mockup table
( CallID integer,
RecvdDate datetime,
ClosedDate datetime
)
insert into @.mockup
select 1, '1/1/7', null union all
select 2, '1/1/7', '1/1/7' union all
select 3, '1/5/7', '1/11/7' union all
select 4, '1/19/7', '1/11/7'

select case when dateType = 1 then RecvdDate
else ClosedDate
end as Date,
sum (dateType) as RecvdCount,
sum (case when dateType=0 then 1 else 0 end)
as ClosedCount
from @.mockup a
join (select 0 as dateType union all select 1) b
on dateType = 1 or ClosedDate is not null
group by case when dateType = 1 then RecvdDate
else ClosedDate
end

/*
Date RecvdCount ClosedCount
-- -- --
2007-01-01 00:00:00.000 2 1
2007-01-05 00:00:00.000 1 0
2007-01-11 00:00:00.000 0 2
2007-01-19 00:00:00.000 1 0
*/

|||

Try joining both results by the date.

Code Snippet

select

coalesce(a.d, b.d) as new_d,

isnull(a.cnt, 0) as received,

isnull(b.cnt, 0) as closed

from

(

SELECT RecvdDate as d, COUNT(Callid) as cnt

FROM CallLog

GROUP BY RecvdDate

) as a

full join

(

SELECT ClosedDate as d, COUNT(Callid) as cnt

FROM CallLog

WHERE NOT ISNULL(ClosedDate, '') = ''

GROUP BY ClosedDate

) as b

on a.d = b.d

order by new_d;

AMB

|||Thanks to both hunchback and Kent: both of these solutions also work