Running Average for stock using Recursive CTE

Question

I have the below data and need to calculate the running average for each row using the amount from each of the previous rows.

CREATE TABLE [dbo].[AKTest](
[IntakeSellingPrice] [decimal](38, 20) NULL,
[IntakeSellingAmount] [decimal](38, 6) NULL,
[Item No_] [nvarchar](20) NOT NULL,
[Variant Code] [nvarchar](10) NOT NULL,
[Unit of Measure Code] [nvarchar](10) NOT NULL,
[Posting Date] [datetime] NOT NULL,
[PurchaseQty] [decimal](38, 20) NULL,
[ReceiptNo] [bigint] NULL,
[InventoryBalance] [decimal](38, 20) NOT NULL,
[NewBalance] [decimal](38, 20) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[AKTest] ([IntakeSellingPrice], [IntakeSellingAmount], [Item No_], [Variant Code], [Unit of Measure Code], [Posting Date], [PurchaseQty], [ReceiptNo], [InventoryBalance], [NewBalance]) VALUES (CAST(10.00000000000000000000 AS Decimal(38, 20)), CAST(1000.000000 AS Decimal(38, 6)), N'1000001', N'NO_SIZE', N'EACH', CAST(0x0000A80800000000 AS DateTime), CAST(100.00000000000000000000 AS Decimal(38, 20)), 1, CAST(0.00000000000000000000 AS Decimal(38, 20)), CAST(100.00000000000000000000 AS Decimal(38, 20)))
GO
INSERT [dbo].[AKTest] ([IntakeSellingPrice], [IntakeSellingAmount], [Item No_], [Variant Code], [Unit of Measure Code], [Posting Date], [PurchaseQty], [ReceiptNo], [InventoryBalance], [NewBalance]) VALUES (CAST(5.00000000000000000000 AS Decimal(38, 20)), CAST(250.000000 AS Decimal(38, 6)), N'1000001', N'NO_SIZE', N'EACH', CAST(0x0000A80E00000000 AS DateTime), CAST(50.00000000000000000000 AS Decimal(38, 20)), 2, CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(100.00000000000000000000 AS Decimal(38, 20)))
GO
INSERT [dbo].[AKTest] ([IntakeSellingPrice], [IntakeSellingAmount], [Item No_], [Variant Code], [Unit of Measure Code], [Posting Date], [PurchaseQty], [ReceiptNo], [InventoryBalance], [NewBalance]) VALUES (CAST(12.50000000000000000000 AS Decimal(38, 20)), CAST(625.000000 AS Decimal(38, 6)), N'1000001', N'NO_SIZE', N'EACH', CAST(0x0000A81900000000 AS DateTime), CAST(50.00000000000000000000 AS Decimal(38, 20)), 3, CAST(60.00000000000000000000 AS Decimal(38, 20)), CAST(110.00000000000000000000 AS Decimal(38, 20)))
GO

Expected Outcome

ReceiptNo        Average 
_________________________
 1                 10.00

 2                 7.50

 3                 8.86

The Formular I used to calculate it manually is defined below for the third row. The calculation is better if you start at the bottom first.

A) I start at the bottom using receiptNo 3 where the NewBalance is 110.

B) 50 units are purchased for 12.50 = 625

C) that leaves 60 units. on the previous row 50 units are purchased for 5 = 250

D) that leaves 10 units. on the previous row 100 units are purchased for 10 = 1000. But we only need the cost of 10 so 1000/10 = 100.

E) add all the cost up 625 + 250 + 100 = 975 / 110 = 8.86

Answer 1

I don't think this is possible using ROWS BETWEEN and OVER as the logic is a bit odd?

I created a temporary table to play with the data and test the results, but basically this is just your original script with a recursive CTE added:

CREATE TABLE #AKTest (
    [IntakeSellingPrice] [decimal](38, 20) NULL,
    [IntakeSellingAmount] [decimal](38, 6) NULL,
    [Item No_] [nvarchar](20) NOT NULL,
    [Variant Code] [nvarchar](10) NOT NULL,
    [Unit of Measure Code] [nvarchar](10) NOT NULL,
    [Posting Date] [datetime] NOT NULL,
    [PurchaseQty] [decimal](38, 20) NULL,
    [ReceiptNo] [bigint] NULL,
    [InventoryBalance] [decimal](38, 20) NOT NULL,
    [NewBalance] [decimal](38, 20) NULL);
GO
INSERT #AKTest ([IntakeSellingPrice], [IntakeSellingAmount], [Item No_], [Variant Code], [Unit of Measure Code], [Posting Date], [PurchaseQty], [ReceiptNo], [InventoryBalance], [NewBalance]) VALUES (CAST(10.00000000000000000000 AS Decimal(38, 20)), CAST(1000.000000 AS Decimal(38, 6)), N'1000001', N'NO_SIZE', N'EACH', CAST(0x0000A80800000000 AS DateTime), CAST(100.00000000000000000000 AS Decimal(38, 20)), 1, CAST(0.00000000000000000000 AS Decimal(38, 20)), CAST(100.00000000000000000000 AS Decimal(38, 20)))
GO
INSERT #AKTest ([IntakeSellingPrice], [IntakeSellingAmount], [Item No_], [Variant Code], [Unit of Measure Code], [Posting Date], [PurchaseQty], [ReceiptNo], [InventoryBalance], [NewBalance]) VALUES (CAST(5.00000000000000000000 AS Decimal(38, 20)), CAST(250.000000 AS Decimal(38, 6)), N'1000001', N'NO_SIZE', N'EACH', CAST(0x0000A80E00000000 AS DateTime), CAST(50.00000000000000000000 AS Decimal(38, 20)), 2, CAST(50.00000000000000000000 AS Decimal(38, 20)), CAST(100.00000000000000000000 AS Decimal(38, 20)))
GO
INSERT #AKTest ([IntakeSellingPrice], [IntakeSellingAmount], [Item No_], [Variant Code], [Unit of Measure Code], [Posting Date], [PurchaseQty], [ReceiptNo], [InventoryBalance], [NewBalance]) VALUES (CAST(12.50000000000000000000 AS Decimal(38, 20)), CAST(625.000000 AS Decimal(38, 6)), N'1000001', N'NO_SIZE', N'EACH', CAST(0x0000A81900000000 AS DateTime), CAST(50.00000000000000000000 AS Decimal(38, 20)), 3, CAST(60.00000000000000000000 AS Decimal(38, 20)), CAST(110.00000000000000000000 AS Decimal(38, 20)))
GO
SELECT * FROM #AKTest;

WITH cte AS (
    SELECT
        ReceiptNo,
        ReceiptNo AS linked_to,
        NewBalance,
        NewBalance - PurchaseQty AS remaining,
        PurchaseQty AS purchased,
        IntakeSellingPrice
    FROM
        #AKTest
    UNION ALL
    SELECT
        c.ReceiptNo,
        c.linked_to - 1 AS linked_to,
        a.NewBalance,
        c.remaining - a.PurchaseQty AS remaining,
        CASE WHEN a.PurchaseQty > c.remaining THEN c.remaining ELSE a.PurchaseQty END AS purchased,
        a.IntakeSellingPrice
    FROM
        cte c
        INNER JOIN #AKTest a ON a.ReceiptNo = c.linked_to - 1
    WHERE
        c.linked_to > 1)
SELECT
    ReceiptNo,
    SUM(purchased * IntakeSellingPrice) / MAX(NewBalance) AS avg_price
FROM
    cte
GROUP BY
    ReceiptNo
ORDER BY
    ReceiptNo;

Gets the correct answer:

ReceiptNo   avg_price
1           10.000000
2           7.500000
3           8.863636

As requested, this will display all of the data from the table, with the average price on the end:

WITH cte AS (
    SELECT
        ReceiptNo,
        ReceiptNo AS linked_to,
        NewBalance,
        NewBalance - PurchaseQty AS remaining,
        PurchaseQty AS purchased,
        IntakeSellingPrice
    FROM
        #AKTest
    UNION ALL
    SELECT
        c.ReceiptNo,
        c.linked_to - 1 AS linked_to,
        a.NewBalance,
        c.remaining - a.PurchaseQty AS remaining,
        CASE WHEN a.PurchaseQty > c.remaining THEN c.remaining ELSE a.PurchaseQty END AS purchased,
        a.IntakeSellingPrice
    FROM
        cte c
        INNER JOIN #AKTest a ON a.ReceiptNo = c.linked_to - 1
    WHERE
        c.linked_to > 1),
Averages AS (
    SELECT
        ReceiptNo,
        SUM(purchased * IntakeSellingPrice) / MAX(NewBalance) AS avg_price
    FROM
        cte 
    GROUP BY
        ReceiptNo)
SELECT
    a.*,
    v.avg_price
FROM
    Averages v
    INNER JOIN #AKTest a ON a.ReceiptNo = v.ReceiptNo
ORDER BY
    a.ReceiptNo;