I have a dataset that has two date columns for each transaction. An invoice date and a charge date. I am trying to do a cumulative or running total of the table in sql.
Data table looks like this:
InvoiceDate ChargeDate TransactionID
----------- ---------- -------------
jan/1/2021 jan/1/2021 11111
jan/1/2021 jan/1/2021 55555
jan/1/2021 Feb/1/2021 11111
jan/1/2021 Feb/1/2021 22222
jan/1/2021 Feb/1/2021 33333
jan/1/2021 Feb/1/2021 44444
Feb/1/2021 jan/1/2021 11111
Feb/1/2021 jan/1/2021 22222
Feb/1/2021 jan/1/2021 33333
Feb/1/2021 jan/1/2021 44444
Feb/1/2021 Feb/1/2021 55555
I want the output in this format:
InvoiceDate ChargeDate cumulativeCount
----------- ---------- ---------------
jan/1/2021 jan/1/2021 2
jan/1/2021 Feb/1/2021 5
Feb/1/2021 jan/1/2021 4
Feb/1/2021 Feb/1/2021 5
I tried the following code but it doesn't give me a running total. it gives me total of each of the two columns
select
chargeDate,
InvoiceDate,
Count(Distinct TransactionID) as CountofIDs
from mytable1
where InvoiceDate<=InvoiceDate
group by ChargeDate, InvoiceDate
Asked by Rishtin
(1 rep)
Jul 26, 2021, 04:42 PM
Last activity: Jul 27, 2021, 10:02 PM
Last activity: Jul 27, 2021, 10:02 PM