April 16, 2015 at 7:46 am
Hi guys,
I have a somewhat simple task but I’m not sure how to do it. I have 3 tables “Customer”, “Sales” and “Location Lookup”. Tables Customer and Sales can be linked directly by CustID and LocID when sales done in the store, but when sales done outside of the store I need to use LocationLu table to link sales to the right store by SiteID filed. My problem is how to eliminate duplicates when sales for same customer done inside and outside of the store
Thank you for your help
Tables are:
SET NOCOUNT ON
IF (SELECT OBJECT_ID('tempdb..#Customer'))is not null
DROP TABLE #Customer
SELECT PK, CustID, LocID, Amt
INTO #Customer
FROM (
SELECT 1, 554, 'abc', 100 UNION ALL
SELECT 2, 555, 'zyz', 200 UNION ALL
SELECT 3, 556, 'wdw', 250 UNION ALL
SELECT 4, 557, 'abc', 323 UNION ALL
SELECT 5, 558, 'trt', 234 UNION ALL
SELECT 6, 558, 'trt', 872
) c (PK, CustID, LocID, Amt);
IF (SELECT OBJECT_ID('tempdb..#Sales'))is not null
DROP TABLE #Sales
SELECT PK, CustID, LocID, Amt, SiteID
INTO #Sales
FROM (
SELECT 10, 554, 'abc', 100, 1 UNION ALL
SELECT 11, 555, 'zyz', 200, 66 UNION ALL
SELECT 12, 556, 'wdw', 250, 76 UNION ALL
SELECT 13, 557, 'FFF', 323, 1 UNION ALL
SELECT 14, 558, 'trt', 234, 3 UNION ALL
SELECT 15, 558, '2000', 872, 3
) s (PK, CustID, LocID, Amt, SiteID);
IF (SELECT OBJECT_ID('tempdb..#LocationLu '))is not null
DROP TABLE #LocationLu
SELECT PK, SiteID, CustLocID, SalesLocID
INTO #LocationLu
FROM (
SELECT 88, 1, 'abc', 'abc' UNION ALL
SELECT 89, 1, 'abc', 'FFF' UNION ALL
SELECT 90, 2, 'uuu', '5000' UNION ALL
SELECT 91, 3, 'trt', 'trt' UNION ALL
SELECT 92, 3, 'trt', '2000' UNION ALL
SELECT 93, 4, '22', '7777'
) l (PK, SiteID, CustLocID, SalesLocID);
SET NOCOUNT OFF
select * from #Customer
SELECT * FROM #Sales
select * from #LocationLu
April 16, 2015 at 8:20 am
I have to give you a thumbs up for providing the sample data and code.
The bad news is your table structure is what's killing you. I'll admit I obviously don't know the overall design and business case but there appears to be a few obvious problems. I'm assuming from your description that customers can have a "home" location but could end up purchasing elsewhere.
1. Your CustID should be unique in the Customer table as the primary keys. No matter how many times I buy something there is still only one me.
2. You are duplicating transaction data "Amt". This should only be in the Sales table not the Customer table. Otherwise you end up with multiples of the same person. (see #1)
3. Lastly rather than having a location lookup table you should just have a "Location" table that contains all of your unique locations.
Then it's become more obvious how to join your tables.
April 16, 2015 at 8:25 am
I agree with your assessment but data coming from different vendors and my task to reconcile it. I can’t change table structure
April 16, 2015 at 8:38 am
legeboka (4/16/2015)
I agree with your assessment but data coming from different vendors and my task to reconcile it. I can’t change table structure
I rigged up an example for you before I saw the response so I might as well post it. If at least to show you how much more simple your life could be. Unfortunately if that is what you have to work with you will end up with difficult and bad data.
SET NOCOUNT ON
IF (SELECT OBJECT_ID('tempdb..#Customer'))is not null
DROP TABLE #Customer
SELECT CustID, LocID
INTO #Customer
FROM (
SELECT 554, 1 UNION ALL
SELECT 555, 2 UNION ALL
SELECT 556, 3 UNION ALL
SELECT 557, 4 UNION ALL
SELECT 558, 3
) c (CustID, LocID);
IF (SELECT OBJECT_ID('tempdb..#Sales'))is not null
DROP TABLE #Sales
SELECT PK, CustID, LocID, Amt
INTO #Sales
FROM (
SELECT 10, 554, 1, 100 UNION ALL
SELECT 11, 555, 2, 200 UNION ALL
SELECT 12, 556, 3, 250 UNION ALL
SELECT 13, 557, 4, 323 UNION ALL
SELECT 14, 558, 3, 234 UNION ALL
SELECT 15, 558, 4, 872
) s (PK, CustID, LocID, Amt);
IF (SELECT OBJECT_ID('tempdb..#Location '))is not null
DROP TABLE #Location
SELECT LocID, SiteDesc
INTO #Location
FROM (
SELECT 1, 'abc' UNION ALL
SELECT 2, 'uuu' UNION ALL
SELECT 3, 'trt' UNION ALL
SELECT 4, '22'
) l (LocID, SiteDesc);
SET NOCOUNT OFF
--select * from #Customer
--SELECT * FROM #Sales
--select * from #Location
SELECT
c.CustID,
l.SiteDesc AS HomeLocation,
s.Amt,
l2.SiteDesc AS SalesLocation
FROM
#Customer c
JOIN #Location l ON l.LocID = c.LocID
JOIN #Sales s ON s.CustID = c.CustID
JOIN #Location l2 ON l2.LocID = s.LocID
WHERE
c.CustID = 558
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply