I have been creating custom SQL queries for our own reporting tools and I am having a hard time pulling the total purchase costs on a per license basis.
My question is what IDs and or Tables are being used in FNMS to link these tables (foreign keys for these tables) does anyone have experience here?
‎Jan 08, 2020 01:01 PM
Hi,
This should get you started.
SELECT
sl.Name LicenseName
,SUM(pod.TotalPrice) LicenseValue
FROM SoftwareLicense sl
LEFT JOIN EntitlementTransaction et
ON sl.SoftwareLicenseID = et.SoftwareLicenseID
LEFT JOIN PurchaseOrderDetail pod
ON et.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
GROUP BY sl.Name
There are a few more things to consider.
Best regards,
Markward
‎Jan 08, 2020 01:57 PM
Hi,
This should get you started.
SELECT
sl.Name LicenseName
,SUM(pod.TotalPrice) LicenseValue
FROM SoftwareLicense sl
LEFT JOIN EntitlementTransaction et
ON sl.SoftwareLicenseID = et.SoftwareLicenseID
LEFT JOIN PurchaseOrderDetail pod
ON et.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
GROUP BY sl.Name
There are a few more things to consider.
Best regards,
Markward
‎Jan 08, 2020 01:57 PM
This is what I was looking for, I can customize and incorporate this. Thanks!
‎Jan 08, 2020 02:14 PM