How to merge two tables with unlike fields

Question

Post reply

How to merge two tables with unlike fields

landonh

SSC Enthusiast

Points: 127
More actions
October 31, 2024 at 5:06 pm
Go to Answer
#4476010

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
October 31, 2024 at 5:16 pm

This was removed by the editor as SPAM
rjaye03

Mr or Mrs. 500

Points: 505
More actions
October 31, 2024 at 8:13 pm
Answer
#4476085

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
November 1, 2024 at 10:58 am

This was removed by the editor as SPAM
November 1, 2024 at 11:00 am

This was removed by the editor as SPAM
November 3, 2024 at 9:01 am

This was removed by the editor as SPAM
November 3, 2024 at 9:03 am

This was removed by the editor as SPAM
November 5, 2024 at 2:58 pm

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

landonh SSC Enthusiast Points: 127 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

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