How to merge two tables with unlike fields

  • How to merge two tables with unlike fields.

    I have two table with one unlike field that I need to show on one row.

    The query I have this far is this.

    /* Asset value per line */

    Select

    ast.siteid,

    ast.location,

    ast.linenum,

    SUM(ast.purchaseprice) as [purchaseprice],

    ast.replacecost

    from asset as ast with (NOLOCK)

    where ast.siteid like ('p%') AND ast.linenum in ('fa35','fa37','pm4','bas4','ehv 3','gsp3 camera')

    Group by ast.siteid, ast.location, ast.linenum,ast.replacecost

    UNION --ALL

    /* Summary Asset Line invent0ry c0st */

    select

    ast.siteid,

    ast.location,

    ast.linenum,

    SUM(ast.replacecost),

    SUM((iv.minlevel+iv.orderqty) * (ivc.avgcost)) as [Parts-Total-Cost]

    from sparepart as sp with (NOLOCK) -- used to get where the part is being used

    LEFT OUTER JOIN asset as ast ON ast.assetnum=sp.assetnum -- used to get the asset location, linenum and purchase price

    LEFT OUTER JOIN inventory as iv on iv.itemnum = sp.itemnum AND iv.siteid = sp.siteid -- used to get maxlevel and stored site and siteid

    LEFT OUTER JOIN invcost as ivc on ivc.itemnum = sp.itemnum AND ivc.siteid = sp.siteid -- used to get cost data

    where ast.linenum in ('fa35','fa37','pm4','bas4','ehv 3','gsp3 camera')

    GROUP BY ast.siteid,ast.location,ast.linenum ,ast.replacecost

    ORDER BY ast.siteid,ast.linenum

    I need to results to show on row per linenum.

    The field ast.replacecost was added to try to see the results and I really do not need that field.

    I need to see SUM(ast.purchaseprice) as [purchaseprice], and SUM((iv.minlevel+iv.orderqty) * (ivc.avgcost)) as [Parts-Total-Cost] on the same row.

    I am just an advanced beginner in SQL and this project is making be bonkers.

    I appreciate any guidance given.

    Respectively

    LandonH

     

  • This is what the results sets look like as individual queries.

    Here are the individual queries.

    /* Summary by Line c0st versus invent0ry c0st */

    select DISTINCT

    ast.siteid,

    ast.location,

    ast.linenum,

    SUM((iv.minlevel+iv.orderqty) * (ivc.avgcost)) as [Parts-Total-Cost]

    from sparepart as sp -- used to get where the part is being used

    LEFT OUTER JOIN asset as ast ON ast.assetnum=sp.assetnum AND ast.siteid=sp.siteid -- used to get the asset location, linenum and purchase price

    LEFT OUTER JOIN inventory as iv on iv.itemnum = sp.itemnum and iv.binnum >'0' AND iv.siteid = sp.siteid -- used to get maxlevel and stored site and siteid

    LEFT OUTER JOIN invcost as ivc on ivc.itemnum = sp.itemnum AND ivc.siteid = sp.siteid -- used to get cost data

    where iv.status in ('ACTIVE') AND ast.status in ('OPERATING')

    --AND ((ast.purchaseprice >'0')) --and ((ast.assettag) is not null)) --AND (ast.linenum is not null))

    AND ast.linenum in ('fa35','fa37','pm4','bas4','ehv 3','gsp3 camera')

    GROUP BY ast.siteid,ast.location,ast.linenum

    ORDER BY ast.siteid,ast.linenum

    /* line-value vs spare part value % IE: Linevalue 2,000000.00 spare part value 250,000 = 0.125% or 12.5% */

    select

    siteid,

    --count(DISTINCT assetnum) as[#asset-records],

    location,

    linenum,

    SUM(purchaseprice) as [line value]

    from asset

    where siteid in ('p201','p202','p203') and linenum in ('fa35','fa37','pm4','bas4','ehv 3','gsp3 camera')

    --AND maintby not in ('JM') AND purchaseprice > '1.00'

    group by siteid, linenum ,location

    ORDER BY siteid, linenum

  • This was removed by the editor as SPAM

  • SELECT siteid, location, linenum, SUM(purchaseprice) AS purchaseprice, SUM(replacecost) AS replacecost

    FROM (

    -- INSERT your query here .. Remove ORDER BY)

    ) tbl1

    GROUP BY siteid, location, linenum

    ORDER BY siteid, location, linenum

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Can people please use the {} Code tags when posting code, as it makes it so much easier to read.

    DROP TABLE IF EXISTS #SomeData;

    CREATE TABLE #SomeData
    (
    SomeDate DATE
    );

    INSERT #SomeData
    (
    SomeDate
    )
    VALUES
    ('20240101')
    ,('20241231');

    SELECT sd.SomeDate
    FROM #SomeData sd;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • This was removed by the editor as SPAM

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

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