SQL Server Performance Forum – Threads Archive
Plz help me for this solution.
Hi,<br /><br />I have total three tables.<br /><br />1. Master_Disposition<br />2. Call_Transaction<br />3. Master_Agent<br /><br />Master_Disposition<br />Dep_ID DepName<br />1 A<br />2 B<br />3 C<br />4 D<br />5 E <br /><br />Agent_Master<br />Ag_id AgName<br />1. Raju<br />2. Shyam<br /><br />Call_Transaction<br />Call_ID Dep_ID Ag_ID Call_Date<br />1 1 1 10-02-04 4:55<br />2 1 1 10-02-04 4:57 <br />3 3 1 10-02-04 4:58<br />4 1 2 10-02-04 4:60<br />5 2 2 10-02-04 5:02<br /><br />I need output like that I have to apply the two filters for this result <br />ag_id & calldate between ” and ”<br /><br />DepName TotalDep <br />A 2<br />B 0 <br />C 1<br />D 0<br />E 0<br /><br />Filter desription for above output <br />Ag_id=1 and call_date between ’10-02-04 4:55′ and ’10-02-04 4:59′<br /><br />Note: I don’t like to use the CASE for this result.<br />I need dynamic result but DepName value should not change.<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />] I hope anyone will provide to me a good solution.<br /><br /><br /><br /><br /><br /><br /><br />Amit Kumar<br />Mob.: 9873812005
Look up JOIN syntax in BOL.
Hi, I used this method to resolve the problem. select di.dispname,count(*) as DispTotal from translog tr join dispmast di
on tr.dispid = di.dispid
where agid=1 and calldate between ‘2007-05-06 00:00:00’ and
‘2007-05-06 23:00:00’
group by di.dispname
union all
select dispname,0 as DispTotal from dispmast
where dispname not in (
select di.dispname from translog tr join dispmast di
on tr.dispid = di.dispid
where agid=1 and calldate between ‘2007-05-06 00:00:00’ and
‘2007-05-06 23:00:00’
group by di.dispname) Amit Kumar
Mob.: 9873812005
Sorry for the blunt reply earlier, but your question was not quite clear about which part was causing you a problem.[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />Another approach is to use an <b>outer</b> join, and count the rows from the outer table:<br /><br />select di.dispname, count(<b>tr.dispid</b>) as DispTotal<br />from dispmast di <b>left join</b> translog tr on di.dispid = tr.dispid<br />where agid=1 and calldate between ‘2007-05-06 00:00:00’ and ‘2007-05-06 23:00:00′<br />group by di.dispname<br /><br />Note that the outer join alone is not enough. You must count a column from the table "on the right" in order to see the 0 results.<br /><br />I prefer to put the "parent" table before the "child" table, always going left to right to expand the family tree, so its easier to get the overview.
Thank you very much for suggestion. Amit Kumar
Mob.: 9873812005
]]>