Matching three tables in SQL Server 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have three tables Despatch, Activation and Replaced as below:
Despatch table:
ProductNo VendorName DispatchDate LotQty
Product1 Vendor1 7/8/2013 20
Product2 Vendor2 7/8/2013 20
Product3 Vendor3 7/8/2013 20
Product4 Vendor4 7/8/2013 20
Product5 Vendor5 7/8/2013 20
Product6 Vendor6 7/8/2013 20

Activation table:
ProductNo LotQty ActivationDate
Product11 55 13/11/2013
Product12 55 13/11/2013
Product3 55 13/11/2013
Product4 55 13/11/2013
Product5 55 13/11/2013
Product6 55 13/11/2013

Replaced table
ProductNo RecordDate
Product1 7/12/2013
Product7 7/12/2013
Product8 7/12/2013
Product9 7/12/2013
Product10 7/12/2013
Product11 7/12/2013

I want to compare data from all the three tables with Despatch table being base.

For example, if I enter LOTQty as 20 I should receive result based on following calculations:

1.> It should match all the ProductNo under Lot 20 from Despatch table with ProductNo in Activation table. If match found(in this case Product3,4,5 & 6) then next it should check dates.

2.> If Dispatch Date of matched ProductNo's are less than Activation Date only then it should consider.(Product3's Dispatch Date is less than Activation Date. So we count it as 1 and similarly for all others). Hence we find 4 matches.

3.> Product1 and Product2 did not found any match in Activation table. For such Product numbers we refer Replaced table. If match found (in this case Product1) it should compare Dispatch Date and Record Date. If Dispatch Date is lesser than Record Date only then we should consider the data(Product1's Dispatch date is less than RecordDate. So we count it as 1).

So now total quantity should be 5 i.e 4 by comparing Activation table and 1 by comparing with Replaced table.

The above are just sample table entries. My real tables contains many more columns which are trimmed to keep it simple. I tried preparing the query but I'm unable to find logic to cater my own requirement. Query I tried so far is as below:

SQL

select 
    d.LotQty, 
    ApprovedQty = count(d.ProductNo),
    d.DispatchDate,
    Installed = count(a.ProductNo) + count(r.ProductNo)
from 
    Despatch d 
left join 
    Activation a 
     on d.ProductNo = a.ProductNo 
    and d.DispatchDate < a.ActivationDate 
    and d.LotQty = a.LotQty
left join 
    Replaced r 
      on d.ProductNo = r.ProductNo 
     and d.DispatchDate < r.RecordDate
where 
    d.LotQty = 20
group by 
    d.LotQty, d.DispatchDate

This yields result as below:
LotQty ApprovedQty DispatchDate Installed
20 6 2013-8-7 5

But when I try for my actual table it gives me unexpected result for two columns i.e "ApprovedQty" and "Installed". My approved Qty and Installed for Lot '1007' should be 984 and 869 respectively. but query returned me 990 and 1633, approx twice the expected Installed data.

While comparing Despatch table's ProductNo with Activation and Replaced, there may be possibility that there might be entries for same ProductNo in both Activation and Replaced tables. Hence the query should first check with Activation table. If it finds a match for ProductNo there, it shuould not search for same ProductNo in Replaced table. Only those ProductNo which found no match in Activation table or whose DispatchDate is greater than ActivationDate should be matched with Replaced table.

Help is deeply appreciated. Thanks in advance

Posted 4-Mar-15 18:58pm

Sujeet KC

Updated 6-Mar-15 2:24am

v4

Add a Solution

Comments

coded007 5-Mar-15 2:12am

try with this

select
d.LotQty,
ApprovedQty = count(d.ProductNo),
d.DispatchDate,
Installed = count(a.ProductNo) + count(r.ProductNo)
from
Despatch d
left join
Activation a
on d.ProductNo = a.ProductNo
and d.DispatchDate < a.ActivationDate
and d.LotQty = a.LotQty
left join
Replaced r
on d.ProductNo = a.ProductNo
and d.DispatchDate < r.RecordDate
where
d.LotQty = 20
group by
d.LotQty, d.DispatchDate

Sujeet KC 5-Mar-15 2:25am

No it's giving me way more unexpected result. In both the left join you are matching d.ProductNo with a.ProductNo, where as I want to check first with Activation table i.e I want to match d.ProductNo with a.ProductNo. If the match is found then fine else it should match remaining d.ProductNo in r.ProductNo. and return the result combining both

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-03-05T02:36:00

I've used recursive CTEs[^] (Common Table Expressions) in the following sql - see CodeProject Article How to use recursive CTE calls in T-SQL[^]

My thinking went as follows ... filter out what I need from the Activation table first (ignoring the LotQty for now) ...

SQL

With T1(ProductNo, DispatchDate, LotQty)
AS
(
    SELECT D.ProductNo, D.DispatchDate, D.LotQty
    from Despatch D
    INNER JOIN Activation A ON  D.ProductNo = A.ProductNo AND D.DispatchDate < A.ActivationDate
)

Then do the same with what I might want from the Replaced Table

SQL

With T2(ProductNo, DispatchDate, LotQty)
AS
(
    SELECT D.ProductNo, D.DispatchDate, D.LotQty
    FROM Despatch D
    INNER JOIN Replaced R ON D.ProductNo = R.ProductNo AND D.DispatchDate < R.RecordDate
)

I want to get everything from T1 and only the stuff from T2 that I don't already have and put it into T3. I added the [Source] column just to show where the data was really coming from

SQL

T3(Source, ProductNo, DispatchDate, LotQty)
AS
(
    SELECT 'T1', T1.* FROM T1
    UNION SELECT 'T2', * FROM T2 where T2.ProductNo NOT IN (SELECT T1.ProductNo from T1)
)

A quick query of the results shows that Products 3,4,5,6 are getting the data from T1, Product1 is getting it from T2 and Product2 has no other data.

Finally do the grouping/totals etc using Despatch as the base so the whole thing together becomes

SQL

With T1(ProductNo, DispatchDate, LotQty)
AS
(
    SELECT D.ProductNo, D.DispatchDate, D.LotQty
    from Despatch D
    INNER JOIN Activation A ON  D.ProductNo = A.ProductNo AND D.DispatchDate < A.ActivationDate
),
T2(ProductNo, DispatchDate, LotQty)
AS
(
    SELECT D.ProductNo, D.DispatchDate, D.LotQty
    FROM Despatch D
    INNER JOIN Replaced R ON D.ProductNo = R.ProductNo AND D.DispatchDate < R.RecordDate
),
T3(Source, ProductNo, DispatchDate, LotQty)
AS
(
    SELECT 'T1', T1.* FROM T1
    UNION SELECT 'T2', * FROM T2 where T2.ProductNo NOT IN (SELECT T1.ProductNo from T1)
)
SELECT 
   D.LotQty,
   ApprovedQty = count(D.ProductNo),
   D.DispatchDate,
   Installed = count(T3.ProductNo)
FROM Despatch D
LEFT OUTER JOIN T3 ON T3.ProductNo = D.ProductNo
WHERE D.LotQty = 20
GROUP BY D.LotQty, D.DispatchDate