oh SQL Query Gurus,
I’ve past my SQL knowledge and googling ability…
I have 2 SQL queries (see below); one which is StockStatus (all parts in stock) and one which is parts with STK-* Transactions. StockStatus gives me 4,610 rows, and STK-* gives me 4200 rows.
I want to join the 2 to return all of the data from STK-* and those parts that show up in the StockStatus but are not in the STK-*.
If I paste the data from queries into excel (Stk-* on top) and run the find duplicates it will give me that data I want, but I’m hoping to do it automatically.
(StockStatus)
SELECT DISTINCT
pb.PartNum, LEFT(p2.PartDescription, 25) PartDesc
, p.TypeCode, pb.WarehouseCode, pb.BinNum, pb.OnhandQty
, pt.TranDate as '1stTranDate', DatePart(year, pt.TranDate) as '1stYear'
, '01/01/1900' as LastTransDate
, 9999 as DaysNotUsed, 1900 as 'LastYear'
, (pc.AvgBurdenCost + pc.AvgLaborCost + pc.AvgMaterialCost
+ pc.AvgMtlBurCost + pc.AvgSubContCost) AvgUnitCost
, ((pc.AvgBurdenCost + pc.AvgLaborCost + pc.AvgMaterialCost
+ pc.AvgMtlBurCost + pc.AvgSubContCost) * pb.OnhandQty) TotalCost
FROM Erp.PartBin pb
JOIN Erp.PartTran pt
ON pb.Company = pt.Company AND pb.PartNum = pt.PartNum
JOIN Erp.PartCost pc
ON pb.Company = pc.Company AND pb.PartNum = pc.PartNum
INNER JOIN Erp.WhseBin wb
ON pb.Company = wb.Company AND pb.WarehouseCode = wb.WarehouseCode
AND pb.BinNum = wb.BinNum
INNER JOIN Erp.Part p
ON pb.Company = p.Company AND pb.PartNum = p.PartNum
WHERE
wb.NonNettable = 0
AND pb.WarehouseCode IN ('ANT', 'COAX', 'HOWM', 'LAB', 'MACH', 'MPA'
,'OVSHOP', 'PACK', 'RADOME', 'RAWSTK', 'SHVM', 'WELD')
AND pt.TranDate =
(SELECT MIN(PartTran.TranDate)
FROM Erp.PartTran
WHERE pt.Company = PartTran.Company
AND pt.PartNum = PartTran.PartNum)
(STK-*)
SELECT DISTINCT
pb2.PartNum, LEFT(p2.PartDescription, 25) PartDesc
, p2.TypeCode, pb2.WarehouseCode, pb2.BinNum, pb2.OnhandQty
,'00/00/0000' as '1stTransDate', 0000 as '1stYear'
, pt2.TranDate as LastTransDate
, DATEDIFF(day, pt2.TranDate, Convert(date, GETDATE())) DaysNotUsed
, DatePart(year, pt2.TranDate) as 'Year'
, (pc2.AvgBurdenCost + pc2.AvgLaborCost + pc2.AvgMaterialCost
+ pc2.AvgMtlBurCost + pc2.AvgSubContCost) AvgUnitCost
, ((pc2.AvgBurdenCost + pc2.AvgLaborCost + pc2.AvgMaterialCost
+ pc2.AvgMtlBurCost + pc2.AvgSubContCost) * pb2.OnhandQty) TotalCost
FROM Erp.PartBin pb2
JOIN Erp.PartTran pt2
ON pb2.Company = pt2.Company AND pb2.PartNum = pt2.PartNum
JOIN Erp.PartCost pc2
ON pb2.Company = pc2.Company AND pb2.PartNum = pc2.PartNum
INNER JOIN Erp.WhseBin wb2
ON pb2.Company = wb2.Company AND pb2.WarehouseCode = wb2.WarehouseCode
AND pb2.BinNum = wb2.BinNum
INNER JOIN Erp.Part p2
ON pb2.Company = p2.Company AND pb2.PartNum = p2.PartNum
WHERE
wb2.NonNettable = 0
AND pt2.TranType IN ('STK-ASM', 'STK-MTL', 'STK-CUS', 'STK-KIT')
AND pb2.WarehouseCode IN ('ANT', 'COAX', 'HOWM', 'LAB', 'MACH', 'MPA',
'OVSHOP' , 'PACK', 'RADOME', 'RAWSTK', 'SHVM', 'WELD')
AND pt2.TranDate =
(SELECT MAX(PartTran.TranDate)
FROM Erp.PartTran
WHERE PartTran.TranType IN
('STK-ASM', 'STK-MTL', 'STK-CUS', 'STK-KIT')
AND pt2.Company = PartTran.Company
AND pt2.PartNum = PartTran.PartNum)