Query Help Please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query Help Please

Author

Topic

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-08-05 : 15:57:16

Hi SQL Gurus,

I am looking for a better way to do write this query to avoid joining to the same table three times. Here is my current T-SQL select

select [Total Leads]=count(distinct l.lead_id),
[Total Sales]=count(distinct l2.lead_id),
[Total NoSales]=count(distinct l3.lead_id),
f.franchise_nm
from cobra.dbo.leads l
left outer join cobra.dbo.leads l2 on l.lead_id=l2.lead_id and l2.status=8
left outer join cobra.dbo.leads l3 on l.lead_id=l3.lead_id and l3.status=9
join cobra.dbo.franchise f on f.prov_id=p.prov_id
group by f.franchise_nm
having sum(case when l.creation_date between '7/1/2002' and '7/31/2002' then 1 else 0)>1

Thanks Gurus. Sorry for not posting more detail on the tables but its really not that hard to figure out what I am trying to do. Basically current_step=8 is a sale, current_step=9 is a no_sale.

Thanks,
Anthony

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-08-05 : 16:04:45

I meant status=8 is a sale and status=9 is a no sale. Is Monday over yet? :)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-05 : 16:09:51


select
    count(distinct l.lead_id) as [Total Leads],
    sum(case when l.status = 8 then 1 else 0 end) as [Total Sales],
    sum(case when l.status = 9 then 1 else 0 end) as [Total NoSales],
    f.franchise_nm
from
    ....

This may not do what you need. If you really do need a distint on lead_id, I think your query is the only way to do it. Also, I didn't see a table aliased to 'p', so I don't know what p.prov_id is....

Jay White
{0}

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-08-05 : 16:15:47

Sorry Jay about not including the p in there but it doesn't matter anyway. I tried what you posted before with the CASE statements and I got weird numbers and think its due to them counting all the lead_id's and not just the distinct ones.

"DBAs are overpaid and their jobs are easy...heck, I could do it."

-Quote from a former boss who calls often and speaks to my voice mail :)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-05 : 16:20:31

quote:

I got weird numbers and think its due to them counting all the lead_id's and not just the distinct ones.

Then use a derived table in your from clause ...


from
   (select lead_id, min(status)
    from cobra.dbo.leads
    group by lead_id) l
   inner join .....

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources