Selecting Records When Certain Conditions Exists

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 2008 Forums

Transact-SQL (2008)

Selecting Records When Certain Conditions Exists

Author

Topic

bkmmba
Starting Member

1 Post

Posted - 2013-02-08 : 15:07:45

I've hit a wall selecting records when certain conditions are true. Here's the scenario.

I have multiple leads being entered into what we'll call the 'Leads' table. A person can submit multiple leads on the same day from different sources. Take this into consideration:

Ex. John Doe inquires about our business on the same day multiple times - once through a phone and once through the web.

Each instance would count as a lead and as separate records in the Leads table. The following is what I want to do:

When there are multiple leads per day, select any lead record that is NOT a web lead record and ignore the phone lead. I would only select the phone lead if it is the ONLY record for that day.

Basically, the phone record should only be counted if it's the only record on file. I think what has me stumped here is the fact that the records can come in on the same day. I'm thinking I need to do some sort of counting of leads and group by date to determine if it's the only one. Then select form there... Thanks guys.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-08 : 15:47:42

Perhaps something like this. This will return one lead per person with preference for WEB:


select d.Name
       ,d.leadSource
from   (
       select name
              ,leadSource
              ,rn = row_number() over (
                     partition by name 
                     order by case when LeadSource = 'WEB' then 0 else 1 end, LeadSource
                     )
       from   leads
       ) as d
where  d.rn = 1

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources