Query that links software list with purchases of that software?
I need a list of software and the purchases with pricing for each software in our list.
I can get a simple list of software via:
select
sw.swmanufacturerid,
sw.softwareid,
mfg.name as 'Manufacturer',
sw.softwarename as 'Software Name'
from softwarelist sw
left join softwaremanufacturer mfg on sw.swmanufacturerid = mfg.swmanufacturerid
order by 3,4
I can get a list of software purchased/received via:
select
po.purchaseorderid,
po.poname,
po.vendorid,
org.name as 'Vendor' ,
poe.quantityordered as 'Qty',
poe.price as 'Price' ,
po.totalprice as 'Total',
poe.componentvendorid,
poe.componentid ,
r.resourcename,
r.componentid,
cd.componentname,
cd.manufacturername,
ct.componenttypename,
po.remarks
from purchaseorder po
left join sdorganization org on po.vendorid = org.org_id
left join purchaseorderext poe on po.purchaseorderid = poe.purchaseorderid
left join purchaselot pl on poe.purchaseorderextid = pl.purchaseorderextid
left join resources r on pl.purchaselotid = r.purchaselotid
left join componentdefinition cd on r.componentid = cd.componentid
left join componenttype ct on cd.componenttypeid = ct.componenttypeid
where cd.componenttypeid in (8,302,1503,2402,4202, 5101)
But I'd like to turn it around so for each software item in Software List, show all purchases with pricing.
But I can't find the ID that links the componentid in the resource file back to the ids in the software list.
Or is there a better way to do "for all software listed in SoftwareList, list all PO, quantity, unit$, total$
Please and thanks!
I can get a simple list of software via:
select
sw.swmanufacturerid,
sw.softwareid,
mfg.name as 'Manufacturer',
sw.softwarename as 'Software Name'
from softwarelist sw
left join softwaremanufacturer mfg on sw.swmanufacturerid = mfg.swmanufacturerid
order by 3,4
I can get a list of software purchased/received via:
select
po.purchaseorderid,
po.poname,
po.vendorid,
org.name as 'Vendor' ,
poe.quantityordered as 'Qty',
poe.price as 'Price' ,
po.totalprice as 'Total',
poe.componentvendorid,
poe.componentid ,
r.resourcename,
r.componentid,
cd.componentname,
cd.manufacturername,
ct.componenttypename,
po.remarks
from purchaseorder po
left join sdorganization org on po.vendorid = org.org_id
left join purchaseorderext poe on po.purchaseorderid = poe.purchaseorderid
left join purchaselot pl on poe.purchaseorderextid = pl.purchaseorderextid
left join resources r on pl.purchaselotid = r.purchaselotid
left join componentdefinition cd on r.componentid = cd.componentid
left join componenttype ct on cd.componenttypeid = ct.componenttypeid
where cd.componenttypeid in (8,302,1503,2402,4202, 5101)
But I'd like to turn it around so for each software item in Software List, show all purchases with pricing.
But I can't find the ID that links the componentid in the resource file back to the ids in the software list.
Or is there a better way to do "for all software listed in SoftwareList, list all PO, quantity, unit$, total$
Please and thanks!