Deferred Revenue Calculation in T-SQL
Deferred revenue calculations are important to publishers who work on an accrued revenue accounting basis. This effectively means that revenue is deferred to the issue to which it belongs for revenue reporting. To calculated deferred revenue, the fees received for a subscription are divided it up for each of the specific issues purchased. In the MA Circulation Manager, a subscription record is created for each order with a start issue and end issue number representing the issues purchased.
If a subscriber purchases a six-issue subscription, starting with say start issue 101, the end issue would be 206. If you wanted to know what the deferred revenue would be starting with issue 101, the actual calculation in Transaction-SQL (T-SQL) in a simplified form is:
DECLARE @nIssueNo AS INT = 101;
DECLARE @dBooked AS DATETIME = '2022-01-01';
/* Sample calculation of deferred revenue by issue */
WITH cteDeffDetails (Copies, IssueNo, IssDesc, IssueValue) AS
(
SELECT s2.Copies, i1.IssueNo, i1.IssDesc, IIF(i1.IssueNo = s2.EndIssue, (o2.UnitPrice * o2.DetailQty) - (ROUND((o2.UnitPrice * o2.DetailQty) / (s2.EndIssue - s2.StartIssue + 1),2) * (s2.EndIssue - s2.StartIssue)), ROUND((o2.UnitPrice * o2.DetailQty) / (s2.EndIssue - s2.StartIssue + 1),2))
FROM tblSubscriptions AS s2
INNER JOIN tblOrderDetails AS o2 ON o2.SubID = s2.SubID
INNER JOIN tblOrders AS o1 ON o1.Order_No = o2. Order_No
INNER JOIN tblIssues AS i1 ON i1.IssueNo BETWEEN s2.StartIssue and s2.Endissue
WHERE i1.IssueNo >= @nIssueNo AND s2.EndIssue >= @nIssueNo AND
o1.OrderDate < @dBooked AND o2.UnitPrice <> 0.0
)
SELECT IssueNo, MAX(IssDesc) AS IssueName, COUNT(Copies) AS Subscriptions, SUM(Copies) as Copies,
SUM(IssueValue) AS DeferredRevenue
FROM cteDeffDetails
GROUP BY IssueNo
ORDER BY IssueNo;
A CTE (Common Table Equation) is used to split the amount paid for a given subscription into the individual issues. Then a simple GROUP BY is implemented in a follow-up SELECT to sum the totals of the average issue price for each individual issue starting with issue 101 for all orders entered before 01/01/2022. To circumvent rounding issues, an inline-IF (IIF) function calculates the last issue average price [(o2.UnitPrice*o2.DetailQty) - (ROUND((o2.UnitPrice*o2.DetailQty) / (s2.EndIssue - s2.StartIssue + 1),2) * (s2.EndIssue - s2.StartIssue)] differently from the average price [ROUND((o2.UnitPrice*o2.DetailQty) / (s2.EndIssue - s2.StartIssue + 1),2)].
BUT!
As mentioned, this is a very simple T-SQL command, which has been tested in SQL Server 2017 and Azure SQL Server. It is similar to our production version of this command, but with a number of conditions and field references left out for illustration purposes. Cancelled subscriptions and back issue purchases, as examples, need to be accounted for separately in a full version.
Associations using accrued accounting with either variable length membership ranges or annual memberships that do not match their fiscal year, must also divided up the amounts paid for a given year and defer going further. Instead of issue numbers, however, that deferral calculation is over time generally expressed in monthly increments. Instead of a start and end issue number, there is a start and end membership date that must be divided into the remaining period from the start of the deferred date, usually the day after their fiscal year end. More on deferred revenue by dates in a future post.
If you have questions about deferred revenue calculations, please use our contact page to submit them.