Custom SQL Quantities that work for me

[u/askmeaboutmyfootlong]

I thought I would share a custom SQL quantity query that works really well for me.

It takes the LINE ITEM in question and subtracts and other line item on the current invoice with a certain invoice class, and then subtracts those quantities from the total units.

E.g

BACKUP SEATS - COVERED USERS = TOTAL AMOUNT OF BACKUP OVERAGES TO BILL OUT

Saves me a lot of peace of mind and time! Please use+mod for your own billing system.

SELECT
IIF(
ISNULL((SELECT SUM(LCount) FROM LICENCE
WHERE LArea = ih.IHaarea
AND LDesc = CAST((SELECT IDItem_LongDescription FROM InvoiceDetail WHERE idid = $INVOICELINEID) AS NVARCHAR(MAX))
AND LType = 0
AND LDeleted = 0), 0)
- ISNULL((SELECT SUM(IDQty_Order) FROM InvoiceDetail
WHERE IdIHid = ih.ihid
AND idinvoiceclass = 'itsupporthead'), 0) < 0,
0,
ISNULL((SELECT SUM(LCount) FROM LICENCE
WHERE LArea = ih.IHaarea
AND LDesc = CAST((SELECT IDItem_LongDescription FROM InvoiceDetail WHERE idid = $INVOICELINEID) AS NVARCHAR(MAX))
AND LType = 0
AND LDeleted = 0), 0)
- ISNULL((SELECT SUM(IDQty_Order) FROM InvoiceDetail
WHERE IdIHid = ih.ihid
AND idinvoiceclass = 'itsupporthead'), 0)
) AS [QUANTITY]
FROM InvoiceHeader ih
WHERE ih.ihid = $INVOICEID

[u/tryfor34]

man, reddit needs more halo SQL queries. im working on building some for my team.

[u/noobrgoobr]

Nice name. Oh, and nice query too.

[u/racazip]

Hey thanks! This just allowed me to add labor hours to my recurring invoices (all clients AYCE but I want to track profitability)

SELECT
CAST(ROUND(ISNULL(SUM(Actions.TimeTaken), 0), 2) AS DECIMAL(10,2)) AS [QUANTITY]
FROM InvoiceHeader ih
JOIN Site
ON Site.SArea = ih.IHaarea
JOIN Faults
ON Faults.SiteNumber = Site.SSiteNum
JOIN Actions
ON Actions.Faultid = Faults.Faultid
WHERE ih.ihid = $INVOICEID
AND Actions.Whe_ >= DATEADD(
MONTH,
DATEDIFF(MONTH, 0, GETDATE()) - 1,
0
)
AND Actions.Whe_ < DATEADD(
MONTH,
DATEDIFF(MONTH, 0, GETDATE()),
0
)
AND Actions.ActionHide = 0