Extracting data by matching multiple tables in SQL Server 2008

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.

enter image description here

  • Weekending date in view
  • How to view the roles and permissions granted to any database user in Azure SQL server instance?
  • How to synchronize Database and DataGridView
  • DBMS Performance-Tuning Book Recommendations
  • Common Table Expression Where Roots are not present in both columns
  • SQL Server Datetime object persistent reformatting issue in Excel
  • 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
    enter image description here

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

    One Solution collect form web for “Extracting data by matching multiple tables in SQL Server 2008”

    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')
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.