I’m trying to create output from a table where the invoice date contains the value of the invoice, more than one invoice can be raised on one day.

I want the output to show each row by the date and also the subtotal of that day AND finally the total based on todays date.

The table looks like this:

Invoice_Id companyname invoicedate invoiceamount paid invoicepaiddate user_id
1 AA Recruitment 2024-06-28 5 0 NULL 5
2 AA Recruitment 2024-06-28 5 0 NULL 6
3 AA Recruitment 2024-07-01 5 0 NULL 13

The output kind of needs to looks like this (below) but in one query

I have tried ROLLUPs but I never end up with SUB and GRAND and lines

WITH a AS (
SELECT
companyname,invoicedate,invoiceamount ,ROW_NUMBER() OVER(ORDER BY NEWID())
AS RowNumber FROM Billing

)
SELECT
CompanyName
,RowNumber
,invoicedate
,SUM(invoiceamount) over (order by RowNumber) AS SpendTotal

FROM a

WHich gives me

Which is kind of correct figures wise but I lose my line detail

Any help greatly received