Adv. SQL Query Help

[gbarcalow]

I know how to get the results I want with two queries, but would like to use only one query. The following is as abbreviated and concise as possible.

My first table [Items] contains two relevant fields:
ItemID (int) and GroupNum (varchar).
The second table [Call] contains three relevent fields:
ItemID (int), Started (smalldatetime) , Voicemail (smalldatetime) .

These are the results I would like:
GroupNum, Count(NOT Voicemail IS NULL), COUNT(DATEDIFF( n, Voicemail, Started) > 60)

I would like the results grouped by by the group number, the total count where the voicemail is not null, and the total count where the date difference in minutes between started and voicemail is greater then 60.

I can join the table, and I can’t get the group number and one OR the other of the two remaining fields, but not both.

Any help would be greatly appreciated

[ck9663]

I know how to get the results I want with two queries, but would like to use only one query. The following is as abbreviated and concise as possible.

My first table [Items] contains two relevant fields:
ItemID (int) and GroupNum (varchar).
The second table [Call] contains three relevent fields:
ItemID (int), Started (smalldatetime) , Voicemail (smalldatetime) .

These are the results I would like:
GroupNum, Count(NOT Voicemail IS NULL), COUNT(DATEDIFF( n, Voicemail, Started) > 60)

I would like the results grouped by by the group number, the total count where the voicemail is not null, and the total count where the date difference in minutes between started and voicemail is greater then 60.

I can join the table, and I can’t get the group number and one OR the other of the two remaining fields, but not both.

Any help would be greatly appreciated

will it be handled by something like...

select call.itemid, items.groupnum, count(*)
from call inner join items on call.itemid = items.itemid
where call.voicemail is not null and
 datediff(n,call.voicemail,callstarted) > 60

[gbarcalow]

will it be handled by something like...

select call.itemid, items.groupnum, count(*)
from call inner join items on call.itemid = items.itemid
where call.voicemail is not null and
 datediff(n,call.voicemail,callstarted) > 60

Here is my working query, Thanks again

select groupNum ,count(a.voicemail) 'voicemailCount', 
	( select count(*) 
	  from ( select a.itemid,a.groupnum,a.created,b.started,b.voicemail,b.department 
		 from csrt_items a inner join call b on a.itemid=b.itemid ) b
	  where b.groupnum=a.groupnum and datediff(n,b.voicemail,b.started)>60 
	  and convert(varchar(10), b.started, 111) = convert(varchar(10), b.voicemail, 111)
	  and b.department = 'SERVICES' and b.created > @Start and b.created < @End) 'countOver60'
from ( 	select a.itemid,a.groupnum,a.created,b.started,b.voicemail,b.department
	from csrt_items a inner join call b on a.itemid=b.itemid
	where a.created > @Start and a.created < @End) a
where a.voicemail is not null
group by a.groupNum
order by a.groupNum