SQL Server Missing records for a running sum using window functions

qxsslcnc  于 2023-06-04  发布在  其他
关注(0)|答案(2)|浏览(166)
AccountdateAmount
X12023050110
X22023050111
X32023050112
X12023050220
X32023050221
X12023050330
X42023050310

I want to retrieve a running total of "Amount" using windows function to get the following output:

AccountdateRunning Total
X12023050110
X22023050111
X32023050112
X12023050230
X22023050211
X32023050233
X12023050360
X22023050311
X32023050333
X42023050310

I've tried the following SQL approach:

SELECT Account,  
       Date,  
       SUM(Amount) OVER (PARTITION BY Account ORDER BY Date) AS RunningTotal
FROM tab1;

But it does not provide data as above. How do I get output as above to show data for accounts which are no longer as of latest date?

bqjvbblv

bqjvbblv1#

One possible way of addressing this problem is to:

  • select all possible accounts from your table
  • generate a calendar table in range (minimum_date, maximum_date) belonging to your table dates
  • cross join all possible accounts with all possible dates, then left join this to your table, and fire your running sum
WITH cte_accounts AS(
    SELECT DISTINCT Account FROM tab
), cte_dates AS (
    SELECT MIN(Date) AS date_val, MAX(Date) AS last_date_val FROM tab
    UNION ALL 
    SELECT DATEADD(DAY, 1, date_val), last_date_VAL FROM cte_dates WHERE last_date_val > date_val 
)
SELECT a.Account,  
       d.date_val,  
       SUM(tab.Amount) OVER (PARTITION BY a.Account ORDER BY d.date_val) AS RunningTotal
FROM       cte_accounts a
CROSS JOIN cte_dates    d
LEFT JOIN tab 
       ON tab.Account = a.Account
      AND tab.date = d.date_val
ORDER BY d.date_val, a.Account

Output

Accountdate_valRunningTotal
X12023-05-0110
X22023-05-0111
X32023-05-0112
X42023-05-01null
X12023-05-0230
X22023-05-0211
X32023-05-0233
X42023-05-02null
X12023-05-0360
X22023-05-0311
X32023-05-0333
X42023-05-0310

Check the demo here .

The last ORDER BY clause is not necessary: it's just for visualization purposes. On a side note, if you do mind about having the null values, you can filter them out in a subquery.

xfb7svmp

xfb7svmp2#

Another example, similar to @lemon answer. Added checking for first date for Account. Without full calendar.

with AccDates as(
  select  Account, min(date) minDate
  from tab1
  group by account
)
,Dates as( select distinct date from tab1)

select a.Account
  ,d.date
  ,sum(t.Amount)over(partition by a.Account order by d.Date) RunningTotal
from AccDates a 
left join Dates d on  d.date>=a.minDate
left join tab1 t on t.Account=a.Account and t.date=d.date
order by d.date,a.account
;

Result

AccountdateRunningTotal
X12023-05-0110
X22023-05-0111
X32023-05-0112
X12023-05-0230
X22023-05-0211
X32023-05-0233
X12023-05-0360
X22023-05-0311
X32023-05-0333
X42023-05-0310

相关问题