| Author |
 Topic  |
|
|
auro88
Starting Member
4 Posts |
 Posted - 04/27/2006 : 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 |
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
|
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
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 04/28/2006 : 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 - 04/28/2006 : 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 - 04/28/2006 : 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.
|
|
|
| |
Topic |
|
|
|
Matching problem
