Extracting data by matching multiple tables in SQL

Question

I want to extract result based on match from multiple tables, three tables to be specific. The tables are Despatch, Activation and Replaced. Diagram below shows structure and sample data in them.

The Expected result based on above diagram is:

LotQty | ApprovedQty | DispatchDate | Installed
  15   |      10     |   2013-8-7   |    9

The result should be on following criteria:

LotQty, ApprovedQty and DispatchDate are just count and distinct from Despatch table

Installed(Real Problem). This field is based on calculation:

1. Query should first match SerialNo from Despatch table for Lot 20 with SerialNo under Lot 20 in Activation table. It finds 8 match ignoring Product1&2 which have different Lot quantities.

2. Next for 8 matched SerialNo's it should compare date. If DispatchDate for a SerialNo is less than ActivationDate then it is count as 1.(Product3's DispatchDate is greater than ActivationDate. Hence it is ignored. For rest of the SerialNo's DispatchDate is < ActivationDate). So total resultant count from Activation table is 7.

3. For unmatched SerialNo's from Despatch table (Product1,2 & 3) it should perform a match in Replaced table. It finds match for all the SerialNo's but as Product4 to Product10 are already matched from Activation, here it should match only 3 leftover SerialNo's(Product1,2 & 3). Product1 & 2 matches here. Next similar to above it should check if DispatchDate is < RecordDate. Both Product1 & 2 have DispatchDate < RecordDate so count from Replaced table is 2.

4. Now the total Installed should be 9 (7 from Activation table and 2 from Replaced table).

There can be multiple entries of a SerialNo from Desptach in both Replaced and Activation table as observed in above diagram . So first the match of SerialNo's of Despatch table should be done with Activation table and if any of SerialNo is unmatched or it's DispatchDate > ActivationDate only then for those SerialNo's it should go ahead to find a match in Replaced table.

So far with the help of @ jpw I have managed to form a query as below :

select
(
    select distinct LOTQty
    from Despatch
    where LotQty = '15'
)as LotQty

,(
    select COUNT(SerialNo)
    from Despatch
    where LotQty = '15'
)as ApprovedQty

,(
    select distinct(DispatchDate)
    from Despatch
    where LotQty = '15'
)as DispatchDate

,(
    select COUNT(A.SerialNo) + count(R.NewSerialNo)
    from Despatch D
    left join
        Activation A
        on D.SerialNo = A.SerialNo
        and D.DispatchDate <= A.ActivationDate
        and D.LotQty = A.LotQty
    left join
        Replaced R
        on D.SerialNo = R.NewSerialNo
        and D.DispatchDate <= R.RecordDate
)as Installed

which yields wrong output i.e :

LotQty | ApprovedQty | DispatchDate | Installed
  15   |      10     |   2013-8-7   |    17

Help is deeply appreciated, Thanks in advance

EIDT1

So from above diagram even now the Installed should be 9 as I would like result only for Lot15

Answer 1

The problem is with your Replaced data. Looking at your condition, it's matching all of the records that are in that table that match the serial number in the Dispatch table. You indicate it should only be 2 though. You are missing a condition that would indicate that it's unmatched in Activation to narrow the results.

Ensuring that A's SerialNo in the second join should do the trick for you

select
(
    select distinct LOTQty
    from Despatch
    where LotQty = '15'
)as LotQty

,(
    select COUNT(SerialNo)
    from Despatch
    where LotQty = '15'
)as ApprovedQty

,(
    select distinct(DispatchDate)
    from Despatch
    where LotQty = '15'
)as DispatchDate

,(
    select COUNT(A.SerialNo) + count(R.NewSerialNo)
    from Despatch D
    left join
        Activation A
        on D.SerialNo = A.SerialNo
        and D.DispatchDate <= A.ActivationDate
        and D.LotQty = A.LotQty
    left join
        Replaced R
        on D.SerialNo = R.NewSerialNo
        and D.DispatchDate <= R.RecordDate
        and A.SerialNo IS NULL
    where D.LotQty = '15'
)as Installed

I can't seem to login to SQLFiddle right now, so here is the schema you need if you want to try it out.

CREATE TABLE Despatch (
    SerialNo VARCHAR(20),
    DispatchDate DATETIME,
    LOTQty INT )

CREATE TABLE Activation (
    SerialNo VARCHAR(20),
    LOTQty INT,
    ActivationDate DATETIME )

CREATE TABLE Replaced (
    NewSerialNo VARCHAR(20),
    RecordDate DATETIME )

INSERT INTO Despatch
VALUES ('Product1', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product2', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product3', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product4', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product5', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product6', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product7', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product8', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product9', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product10', '2013-08-07', 15)
INSERT INTO Despatch
VALUES ('Product11', '2013-08-07', 20)
INSERT INTO Despatch
VALUES ('Product12', '2013-08-07', 20)

INSERT INTO Activation
VALUES ('Product1', 55, '2013-07-13')
INSERT INTO Activation
VALUES ('Product2', 20, '2013-11-13')
INSERT INTO Activation
VALUES ('Product3', 15, '2013-07-13')
INSERT INTO Activation
VALUES ('Product4', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product5', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product6', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product7', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product8', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product9', 15, '2013-11-13')
INSERT INTO Activation
VALUES ('Product10', 15, '2013-11-13')

INSERT INTO Replaced
VALUES ('Product1', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product2', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product4', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product5', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product6', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product7', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product8', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product9', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product10', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product11', '2013-12-07')
INSERT INTO Replaced
VALUES ('Product12', '2013-12-07')