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

group by help?

I am trying to get the last occurance of a display name in a login user log database. Basically I have a table that looks like this:

id user fname lname
-----------
1 jdoe Jane Doe
2 jdoe John Doe
3 jdoe Fred Flinstone

I run the MySQL query: SELECT name, max(id) as max_id, user FROM `logins` GROUP BY user

I get back:
id user fname lname
-----------
3 jdoe Jane Doe

I actually want:
id user fname lname
-----------
3 jdoe Fred Flinstone

Any that can help it would be greatly Appreciated!!by "last" occurrence you mean the one with the largest id?
select id
, user
, fname
, lname
from logins as ZZ
where id
= ( select max(id)
from logins
where user = ZZ.user )|||I tried this result and am still having difficulties? Do you know if this works with all versions of MySQL? I get the following error from phpmyadmin:

You have an error in your SQL syntax near 'select max(id) from logins where user=ZZ.user

Any other thoughts?|||good guess -- subqueries are not supported prior to version 4.1

how come it took you two and a half weeks to try my solution?|||If a correlated subquery is not supported, let's hope a join (and a group by) is?
Could you try this one: select a.id, a.user, a.fname, a.lname
from logins as a, logins as b
where a.user = b.user
and a.id <= b.id
group by a.id, a.user, a.fname, a.lname
having count(*) = 1

GROUP BY and nText

Hi Everyone,

I was wondering if you could help with the following. Basically I have a database of Blog posts. On particular page say I want to list all the posts, easy. Now, if I want to list the posts but also have a column that also counts the number of comments left for that particular post (Comments stored in another table) then I can get this to work. The problem I am having is that the SQL I am using (below) fails if I include the column PostContent as its nText. I need it included as this contains the posts HTML. If I remove the PostContent from the SELECT and GROUP BY statements it works fine.

Any ideas on how to get this to work with PostContent included. Oh and I cant convert to varchar as it wont hold enough.

Code Snippet

SELECT P.PostId, P.UserId, P.PostDate, P.PostTitle, P.PostContent, Count(CommentId) AS TotalComments
FROM dbo.BlogPosts AS P LEFT OUTER JOIN dbo.BlogComments as C on P.PostId = C.PostId
WHERE P.UserId = 3
GROUP BY P.PostId, P.UserId, P.PostDate, P.PostTitle, P.PostContent
ORDER BY PostDate DESC

Cheers.

Hi,

why do you want to group them - you want to fetch all the blogs, right?

Simply remove grouping and add the count as subselect:

Code Snippet

SELECT P.PostId, P.UserId, P.PostDate, P.PostTitle, P.PostContent, (select Count(CommentId) from dbo.BlogComments as C where P.PostId = C.PostId ) AS TotalComments

FROM dbo.BlogPosts AS P

WHERE P.UserId = 3

ORDER BY PostDate DESC

If you want to show the last post grouped by user then this is another story ;-)

|||

If you use sql server 2005 then use the following query,

Code Snippet

SELECT

P.PostId

, P.UserId

, P.PostDate

, P.PostTitle

, Cast(P.PostContent as Nvarchar(Max)) as PostContent

, Count(CommentId) AS TotalComments

FROM

dbo.BlogPosts AS P

LEFT OUTER JOIN dbo.BlogComments as C on P.PostId = C.PostId

WHERE

P.UserId = 3

GROUP BY

P.PostId, P.UserId, P.PostDate, P.PostTitle, Cast(P.PostContent as Nvarchar(Max))

ORDER BY

PostDate DESC

|||

Thanks guys. Make sense now - I was totally going about it the wrong way. I have gone for Ivan's approach for simplicity.

Cheers