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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply