Unfinished query - 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

Transact-SQL (2000)

Unfinished query

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-21 : 10:21:29

I have a kind of pyramid structure in my tables. Each customer has a sponsor for each product. But sometimes a customer do not have a sponsor, so I need to find him one. First we have to look if, for an other product he has a sponsor. If yes, it will be his sponsor. The thing that is complicated( for me ) is if the customer has 2 different sponsor for two other products, I need to get the first who sponsored the customer and the sponsor need to be in the table for that product.

Table LCustomerT
Customer_key, CustomerSponsor_key,product_key,datestamp(dd/mm/yy)
1 1 2
5 3 3 7/2/2001
5 3 4 5/2/2001
6 1 2 5/8/2001
6 5 3 5/2/2002
6 0 4 5/8/2002

here is what I started
SELECT dbo.LSponsorT.Customer_key,
dbo.LSponsorT.Product_key,
dbo.LSponsorT.Sent_key,
a.CustomerSponsor_key
FROM
dbo.LSponsorT LEFT OUTER JOIN
(SELECT customer_key,
CustomerSponsor_key
FROM LSponsorT

WHERE CustomerSponsor_key <> 0 ) a
ON dbo.LSponsorT.Customer_key = a.customer_key

WHERE (dbo.LSponsorT.CustomerSponsor_key = 0)

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-21 : 12:17:29

something like

update tbl
set CustomerSponsor_key
= (select min(CustomerSponsor_key) from tbl t1
where t1.Customer_key = tbl.Customer_key
and t1.datestamp = (select min(datestamp) from tbl t2 where t2.Customer_key = tbl.Customer_key and CustomerSponsor_key is not null)
)
where CustomerSponsor_key is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-21 : 14:08:28

Your query always return customer_key 1. I'll continue working on it, you provide me some good stuff.

Thanks

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-08-22 : 08:09:10

1fred,

Try the below and let me know if it works correctly.

UPDATE cust_test
Set CustomerSponsor_Key = bb.CustomerSponsor_Key
FROM cust_Test aa, (
Select a.customer_Key, a.CustomerSponsor_Key
FROM cust_test a INNER JOIN
(Select customer_Key, Min(IsNull(datestamp, getdate())) datestamp
FROM cust_test
Where customer_key in (Select Distinct Customer_key
FROM cust_test
WHERE CustomerSponsor_Key = 0)
AND CustomerSponsor_Key <> 0
Group by customer_Key) b
ON a.customer_Key = b.customer_Key
WHERE a.datestamp = b.datestamp) bb
WHERE aa.customer_key = bb.Customer_Key
AND aa.customerSponsor_Key = 0

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources