SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
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  

bkmmba
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

TG
Flowing Fount of Yak Knowledge

USA
5467 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
       ,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
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.05 seconds. Powered By: Snitz Forums 2000