SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Selecting Records When Certain Conditions Exists
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 02/08/2013 :  15:07:45  Show Profile  Reply with Quote
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.

Edited by - bkmmba on 02/08/2013 15:09:04

Flowing Fount of Yak Knowledge

6062 Posts

Posted - 02/08/2013 :  15:47:42  Show Profile  Reply with Quote
Perhaps something like this. This will return one lead per person with preference for WEB:

select d.Name
from   (
       select name
              ,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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000