SQL - Running Total by MONTH & YEAR date from different table-CodePudding

I am trying to work out how to get running total by EOMONTH year and month where the date comes from a different table and is in days.

My query so far:

SELECT
    EOMONTH(EP.ProcessedDate),
    EE.Company ID,
    SUM (CASE WHEN EE.status = 1 THEN 1 ELSE 0 END) OVER (ORDER BY EOMONTH(Ep.processedDate)
FROM
    Employee AS EE
INNER JOIN 
    payments AS EP ON EE.Id = Ep.Id
GROUP BY
    EOMONTH(Ep.processedDate),
    EE.CompanyId

The problem I'm facing is that it does the running total on the day of the month displaying the end of the month/year which returns a list of 5000 records when reality should only be 12 (one record per month) per client

What I need is for it to aggregate and display by the full month and year (yyyy-mm-dd) and companyid

Any advice would be helpful at this stage. And as always treat me like I know nothing

Thanks in advance

CodePudding user response：

It sounds like you don't want the company id:

select EOMONTH(EP.ProcessedDate),
       Sum(Sum(case when EE.status = 1 then 1 else 0 end)) over (Order by EOMONTH(Ep.processedDate)
From Employee EE Inner Join
     payment EP
     on EE.Id = Ep.Id
Group by EOMONTH(Ep.processedDate)