Matching problem - 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)

Matching problem

Author

Topic

auro88
Starting Member

4 Posts

Posted - 2006-04-27 : 16:39:02

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

pootle_flump

1064 Posts

Posted - 2006-04-28 : 04:30:41

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

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-28 : 06:08:21

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.

auro88
Starting Member

4 Posts

Posted - 2006-04-28 : 12:36:04

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

auro88
Starting Member

4 Posts

Posted - 2006-04-28 : 12:38:37

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources