Using a CTE to aggregate seperate tables

  • Hello All,

    I am trying to tie together tables that show quantities of a product committed to an order and quantities on hand by a location.

    My end result should look like the below example.

    Item Location QtyOnHandByLocation SumQtyCommitTotal

    Prod1 NJ 10 10

    Prod1 NY 10 0

    Prod1 FL 0 0

    Prod1 PA 0 0

    So I can see I have 10 items in NJ On Hand and Committed to an order. 10 available in NY but not on an order. Then the other two locations have no quantities.

    Below is the CTE but it produces inaccurate results. I've tried running it several different ways by playing with the grouping but have no luck thus far.

    --create the temp table

    Create table #SalesLine

    (

    Novarchar (50) not null

    , LocationCodevarchar (50) not null

    , QtyCommitint not null

    )

    create table #ItemLedgerEntry

    (

    Novarchar (50) not null

    , LocationCodevarchar (50) not null

    , QtyRemainint not null

    )

    --insert test data

    insert into #SalesLine

    (

    NO, LocationCode, QtyCommit

    )

    Values

    ('0000-6896-31 MISC', 'NJ', '10')

    ,('0000-6896-31 MISC', 'NY', '0')

    insert into #ItemLedgerEntry

    (

    NO, LocationCode, QtyRemain

    )

    Values

    ('0000-6896-31 MISC', 'NJ', '10')

    ,('0000-6896-31 MISC', 'NY', '10')

    ,('0000-6896-31 MISC', 'FL', '0')

    ,('0000-6896-31 MISC', 'PA', '0')

    With SumSalesQtyCommit as

    (

    select [No]

    , [LocationCode]

    , Sum([QtyCommit]) as SumQtyCommitTotal

    From #SalesLine

    group by [No], [LocationCode]

    ),

    SumQtyOnHand as

    (

    select [No]

    , [LocationCode]

    , SUM([QtyRemain]) as QtyOnHand

    From #ItemLedgerEntry

    group by [No], [LocationCode]

    )

    select ile.[No] as ItemNo

    , ile.[LocationCode] as LocationCode

    , ile.QtyOnHand as QtyOnHandByLocation

    , sum(isnull(sot.SumQtyCommitTotal, 0)) as SumQtyCommitTotal

    From SumQtyOnHand as ile

    left join SumSalesQtyCommit as sot

    On sot.[No] = ile.[No]

    where ile.[No] = '0000-6896-31 MISC'

    group by ile.No, ile.LocationCode, ile.QtyOnHand

    Order by ile.[No]

    Please take a look and assist me if possible. I am close to the desired results but can't find a way.

    Keith

  • You are joining on "No" between your two CTEs and since all rows have the same No value all items in SumSalesQtyCommit are joined to SumQtyOnHand. add a join condition for location and you will get the desired results.

    With SumSalesQtyCommit as

    (

    select [No]

    , [LocationCode]

    , Sum([QtyCommit]) as SumQtyCommitTotal

    From #SalesLine

    group by [No], [LocationCode]

    ),

    SumQtyOnHand as

    (

    select [No]

    , [LocationCode]

    , SUM([QtyRemain]) as QtyOnHand

    From #ItemLedgerEntry

    group by [No], [LocationCode]

    )

    select ile.[No] as ItemNo

    , ile.[LocationCode] as LocationCode

    , ile.QtyOnHand as QtyOnHandByLocation

    , sum(isnull(sot.SumQtyCommitTotal, 0)) as SumQtyCommitTotal

    From SumQtyOnHand as ile

    left join SumSalesQtyCommit as sot

    On sot.[No] = ile.[No]

    and sot.LocationCode = ile.LocationCode

    where ile.[No] = '0000-6896-31 MISC'

    group by ile.No, ile.LocationCode, ile.QtyOnHand

    Order by ile.[No]

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Couldn't post this solution at the time because of a quirk in the forum, had to PM it.

    😎

    USE tempdb;

    GO

    /*

    Item Location QtyOnHandByLocation SumQtyCommitTotal

    Prod1 NJ 10 10

    Prod1 NY 10 0

    Prod1 FL 0 0

    Prod1 PA 0 0

    */

    --create the temp table

    Create table #SalesLine

    (

    Novarchar (50) not null

    , LocationCodevarchar (50) not null

    , QtyCommitint not null

    )

    create table #ItemLedgerEntry

    (

    Novarchar (50) not null

    , LocationCodevarchar (50) not null

    , QtyRemainint not null

    )

    --insert test data

    insert into #SalesLine

    (

    NO, LocationCode, QtyCommit

    )

    Values

    ('0000-6896-31 MISC', 'NJ', '10')

    ,('0000-6896-31 MISC', 'NY', '0')

    insert into #ItemLedgerEntry

    (

    NO, LocationCode, QtyRemain

    )

    Values

    ('0000-6896-31 MISC', 'NJ', '10')

    ,('0000-6896-31 MISC', 'NY', '10')

    ,('0000-6896-31 MISC', 'FL', '0')

    ,('0000-6896-31 MISC', 'PA', '0');

    SELECT

    IL.No AS Item

    ,IL.LocationCode AS Location

    ,IL.QtyRemain AS QtyOnHandByLocation

    ,ISNULL(SL.QtyCommit,0) AS SumQtyCommitTotal

    FROM #ItemLedgerEntry IL

    LEFT OUTER JOIN #SalesLine SL

    ON IL.LocationCode = SL.LocationCode

    AND IL.No = SL.No

    DROP TABLE #ItemLedgerEntry;

    DROP TABLE #SalesLine;

    Results

    Item Location QtyOnHandByLocation SumQtyCommitTotal

    ------------------ --------- ------------------- -----------------

    0000-6896-31 MISC NJ 10 10

    0000-6896-31 MISC NY 10 0

    0000-6896-31 MISC FL 0 0

    0000-6896-31 MISC PA 0 0

  • Thanks Eirikur,

    When I run the query in the actual database I get back over 7,000 records for 1 item. I should only get 8 results back for this item. Your query does work well with the test data but not in the actual database.

    I'll post back some more values to test with shortly.

  • Robert,

    This is very close but when I run it in my production system I only get back 2 records. The results I get back are the results where there is both QtyOnHand and SumQtyCommitTotal. I should see the ItemNo even if there is no QtyOnHand or if there is QtyOnHand and 0 SumQtyCommitTotal.

    When I run the Inner Query for the ItemLedgerEntry and specify the item in a where clause I get back 8 records.

    When I run the inner query for the SalesLine table I get back 2 records.

    Is there a way I can insert a 0 SumQtyCommitTotal record in the SalesLine if a location exists in the ItemLedgerEntry table?

  • Ok,

    Here is some more sample data.

    If you run the Inner query for #ItemLedgerEntry you will see 8 results appear.

    For the Locations that have no records for SumQtyCommitTotal in the #SalesLine table a 0 should appear.

    The CTE should then return 8 records even if no records are in the #SaleLine table.

    Please let me know if any more information is needed. Everyone's help has been very appreciated. Especially Greg Snidow for his PM and Eirikur.

    use tempdb

    go

    drop table #salesline;

    drop table #ItemLedgerEntry;

    --create the temp table

    Create table #SalesLine

    (

    Novarchar (50) not null

    , LocationCodevarchar (50) not null

    , QtyCommitint not null

    )

    create table #ItemLedgerEntry

    (

    Novarchar (50) not null

    , LocationCodevarchar (50) not null

    , QtyRemainint not null

    )

    --insert test data

    insert into #SalesLine

    (

    NO, LocationCode, QtyCommit

    )

    Values

    ('0000-6896-31 MISC', 'NJ', '150')

    ,('0000-6896-31 MISC', 'NY', '62')

    insert into #ItemLedgerEntry

    (

    NO, LocationCode, QtyRemain

    )

    Values

    ('0000-6896-31 MISC', 'NJ', '150')

    ,('0000-6896-31 MISC', 'NY', '966')

    ,('0000-6896-31 MISC', 'FL', '240')

    ,('0000-6896-31 MISC', 'ND', '8')

    ,('0000-6896-31 MISC', 'OD', '143')

    ,('0000-6896-31 MISC', 'LA', '0')

    ,('0000-6896-31 MISC', 'TRANSFER', '0')

    ,('0000-6896-31 MISC', 'DROP', '0')

    With SumSalesQtyCommit as

    (

    select [No]

    , [LocationCode]

    , Sum([QtyCommit]) as SumQtyCommitTotal

    From #SalesLine

    group by [No], [LocationCode]

    ),

    SumQtyOnHand as

    (

    select [No]

    , [LocationCode]

    , SUM([QtyRemain]) as QtyOnHand

    From #ItemLedgerEntry

    group by [No], [LocationCode]

    )

    select ile.[No] as ItemNo

    , ile.[LocationCode] as LocationCode

    , ile.QtyOnHand as QtyOnHandByLocation

    , sum(isnull(sot.SumQtyCommitTotal, 0)) as SumQtyCommitTotal

    From SumQtyOnHand as ile

    left join SumSalesQtyCommit as sot

    On sot.[No] = ile.[No]

    where ile.[No] = '0000-6896-31 MISC' and ile.locationcode = sot.locationcode

    group by ile.No, ile.LocationCode, ile.QtyOnHand

    Order by ile.[No]

  • Hi

    I think Eirikur's is right, except the summing CTEs were missed. Try Eirikur's with the CTEs in place.

    WITH SumSalesQtyCommit AS

    (

    SELECT [No]

    , [LocationCode]

    , Sum([QtyCommit]) AS SumQtyCommitTotal

    FROM #SalesLine

    GROUP BY [No], [LocationCode]

    ),

    SumQtyOnHand AS

    (

    SELECT [No]

    , [LocationCode]

    , SUM([QtyRemain]) AS QtyOnHand

    FROM #ItemLedgerEntry

    GROUP BY [No], [LocationCode]

    )

    SELECT ile.[No] AS ItemNo

    , ile.[LocationCode] AS LocationCode

    , ile.QtyOnHand AS QtyOnHandByLocation

    , isnull(sot.SumQtyCommitTotal, 0) AS SumQtyCommitTotal

    FROM SumQtyOnHand AS ile

    LEFT JOIN SumSalesQtyCommit AS sot

    ON sot.[No] = ile.[No] AND sot.LocationCode = ile.LocationCode

    WHERE ile.[No] = '0000-6896-31 MISC'

    ORDER BY ile.[No];

  • Thanks for the suggestion Mickey but even with the test data I only get two results back.

  • That's interesting

    From this drop table #salesline;

    drop table #ItemLedgerEntry;

    --create the temp table

    Create table #SalesLine

    (

    Novarchar (50) not null

    , LocationCodevarchar (50) not null

    , QtyCommitint not null

    )

    create table #ItemLedgerEntry

    (

    Novarchar (50) not null

    , LocationCodevarchar (50) not null

    , QtyRemainint not null

    )

    --insert test data

    insert into #SalesLine

    (

    NO, LocationCode, QtyCommit

    )

    Values

    ('0000-6896-31 MISC', 'NJ', '150')

    ,('0000-6896-31 MISC', 'NY', '62')

    insert into #ItemLedgerEntry

    (

    NO, LocationCode, QtyRemain

    )

    Values

    ('0000-6896-31 MISC', 'NJ', '150')

    ,('0000-6896-31 MISC', 'NY', '966')

    ,('0000-6896-31 MISC', 'FL', '240')

    ,('0000-6896-31 MISC', 'ND', '8')

    ,('0000-6896-31 MISC', 'OD', '143')

    ,('0000-6896-31 MISC', 'LA', '0')

    ,('0000-6896-31 MISC', 'TRANSFER', '0')

    ,('0000-6896-31 MISC', 'DROP', '0');

    WITH SumSalesQtyCommit AS

    (

    SELECT [No]

    , [LocationCode]

    , Sum([QtyCommit]) AS SumQtyCommitTotal

    FROM #SalesLine

    GROUP BY [No], [LocationCode]

    ),

    SumQtyOnHand AS

    (

    SELECT [No]

    , [LocationCode]

    , SUM([QtyRemain]) AS QtyOnHand

    FROM #ItemLedgerEntry

    GROUP BY [No], [LocationCode]

    )

    SELECT ile.[No] AS ItemNo

    , ile.[LocationCode] AS LocationCode

    , ile.QtyOnHand AS QtyOnHandByLocation

    , isnull(sot.SumQtyCommitTotal, 0) AS SumQtyCommitTotal

    FROM SumQtyOnHand AS ile

    LEFT JOIN SumSalesQtyCommit AS sot

    ON sot.[No] = ile.[No] AND sot.LocationCode = ile.LocationCode

    WHERE ile.[No] = '0000-6896-31 MISC'

    ORDER BY ile.[No];

    I get

    ItemNo LocationCode QtyOnHandByLocation SumQtyCommitTotal

    -------------------------------------------------- -------------------------------------------------- ------------------- -----------------

    0000-6896-31 MISC DROP 0 0

    0000-6896-31 MISC FL 240 0

    0000-6896-31 MISC LA 0 0

    0000-6896-31 MISC ND 8 0

    0000-6896-31 MISC NJ 150 150

    0000-6896-31 MISC NY 966 62

    0000-6896-31 MISC OD 143 0

    0000-6896-31 MISC TRANSFER 0 0

    (8 row(s) affected)

  • OK, Your right I didn't have

    left join SumSalesQtyCommit as sot

    On sot.[No.] = ile.[Item No.] and sot.[Location Code] = ile.[Location Code]

    I had

    left join SumSalesQtyCommit as sot

    On sot.[No.] = ile.[Item No.]

    where sot.[Location Code] = ile.[Location Code]

    group by ile.[Item No.], ile.[Location Code], , ile.QtyOnHand, sot.QtyCommit

    I think this wraps it up but I'll test more items in the morning.

    thanks for being persistent micky!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply