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