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 2000 Forums
 SQL Server Development (2000)
 Matching problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

auro88
Starting Member

4 Posts

Posted - 04/27/2006 :  16:39:02  Show Profile  Reply with Quote
I have these two tables:

Leads
===============
id date account id
1 1/1/2005 1-abc
2 7/1/2005 1-abc
3 7/15/2005 1-abc
4 8/1/2005 1-xyz
5 8/15/2005 1-xyz

Sales
=================
id date account id
1 8/1/2005 1-abc
2 9/1/2005 1-abc
3 7/1/2005 1-xyz
4 9/1/2005 1-xyz

Result:
======================================
Lead id Sales id account id
2 1 1-abc
3 2 1-abc
4 4 1-xyz

I need a store procedure to match the sales to leads (1-1 match). The matching is done by matching up the account id and the dates. The difference between sales date and the lead date has to be <= 6 months and sales date >= lead date.

For example, Sales id 1 is not matched to Lead id 1 because the dates diff is greater than 6 months. Sales id 2 is matched to lead id 1 because the account ids are the same and Sales 2 is the earliest available. Sales id 3 is matched to lead id 2 because the account ids are the same and since Sales 2 is taken already, Sales id 3 is the earliest available one. In the result, sales id should be distinct and it should contain matched record.

Someone please helps me. I am not sure how to do the "earliest available" matching. Thanks in advance.

auro88

Edited by - auro88 on 04/29/2006 15:09:43

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 04/28/2006 :  04:30:41  Show Profile  Reply with Quote
Hi

I think the below will do it:
SELECT	Lead.ID AS Lead_ID, 
	Sales.ID AS Sales_ID, 
	Sales.Account_ID
FROM	Leads
		INNER JOIN Sales ON
	Sales.Account_ID = Leads.Account_ID
	AND Sales.Date >= Leads.Date


You meniton that there is a one to one match. If this is the case then presumably the sale should never be on a date preceding the lead. As such - perhaps you can test for this with some check constraint. Or perhaps this could all be contained in one table.

HTH
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 04/28/2006 :  06:08:21  Show Profile  Reply with Quote
Hi auro88,

You're effectively asking to number the records for each account in both the sales and leads tables, and then join on that.

I would definitely recommend a bit of a rethink about this table design, but the following will (I think) do what you're asking with this table structure...

--data
set dateformat mdy
declare @leads table (id int, date datetime, account_id varchar(10))
insert @leads (id, date, account_id)
          select 1, '1/1/2005', '1-abc'
union all select 2, '7/1/2005', '1-abc'
union all select 3, '8/1/2005', '1-xyz'
union all select 4, '8/15/2005', '1-xyz'

declare @sales table (id int, date datetime, account_id varchar(10))
insert @sales (id, date, account_id)
          select 1, '8/1/2005', '1-abc'
union all select 2, '9/1/2005', '1-abc'
union all select 3, '9/1/2005', '1-xyz'

--calculation
select * from 
        (select *, account_order = (select count(*) from @sales where
            account_id = s.account_id and date <= s.date) from @sales s) s
    inner join
        (select *, account_order = (select count(*) from @leads where
            account_id = l.account_id and date <= l.date) from @leads l) l 
    on s.account_id = l.account_id and s.account_order = l.account_order


Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

auro88
Starting Member

4 Posts

Posted - 04/28/2006 :  12:36:04  Show Profile  Reply with Quote
Thanks for your reply. The query will not work for me because it returns multiple records for the same sales ids. I need a 1-1 match. Within the date range (6 months), two sales could be a possible match for a lead.

Thanks again :)

quote:
Originally posted by pootle_flump

Hi

I think the below will do it:
SELECT	Lead.ID AS Lead_ID, 
	Sales.ID AS Sales_ID, 
	Sales.Account_ID
FROM	Leads
		INNER JOIN Sales ON
	Sales.Account_ID = Leads.Account_ID
	AND Sales.Date >= Leads.Date


You meniton that there is a one to one match. If this is the case then presumably the sale should never be on a date preceding the lead. As such - perhaps you can test for this with some check constraint. Or perhaps this could all be contained in one table.

HTH

Go to Top of Page

auro88
Starting Member

4 Posts

Posted - 04/28/2006 :  12:38:37  Show Profile  Reply with Quote
Hi Ryan,

Thanks a lot!!! I think your query will work. But a sudden new requirement shows up. The dates between the leads and sales have to be within 6 months. Do you think it's possible to make this change in your solution? I have updated my original post with the new result.

Again, thanks for your time.


quote:
Originally posted by RyanRandall

Hi auro88,

You're effectively asking to number the records for each account in both the sales and leads tables, and then join on that.

I would definitely recommend a bit of a rethink about this table design, but the following will (I think) do what you're asking with this table structure...

--data
set dateformat mdy
declare @leads table (id int, date datetime, account_id varchar(10))
insert @leads (id, date, account_id)
          select 1, '1/1/2005', '1-abc'
union all select 2, '7/1/2005', '1-abc'
union all select 3, '8/1/2005', '1-xyz'
union all select 4, '8/15/2005', '1-xyz'

declare @sales table (id int, date datetime, account_id varchar(10))
insert @sales (id, date, account_id)
          select 1, '8/1/2005', '1-abc'
union all select 2, '9/1/2005', '1-abc'
union all select 3, '9/1/2005', '1-xyz'

--calculation
select * from 
        (select *, account_order = (select count(*) from @sales where
            account_id = s.account_id and date <= s.date) from @sales s) s
    inner join
        (select *, account_order = (select count(*) from @leads where
            account_id = l.account_id and date <= l.date) from @leads l) l 
    on s.account_id = l.account_id and s.account_order = l.account_order


Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

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.11 seconds. Powered By: Snitz Forums 2000