Group By with an Over Clause for Date data

Question

I'm still a junior when it comes to SQL and I was hoping to get some help from you on this.

I have data being summed cumulatively throughout a month and I'm trying to get the value matching the last day of the ISOWeek.

SELECT Date, [ISOWeek]
,SUM([Value]) OVER (PARTITION BY YEAR(Date), MONTH(Date) order by Date) AS [Value]

FROM [Demo].[MTD_Daily]
ORDER BY Date DESC

Date        ISOWeek Value
2020-07-19  2029    1006353.56
2020-07-18  2029    951399.59
2020-07-17  2029    895296.1
2020-07-16  2029    843615.05
2020-07-15  2029    793697.11
2020-07-14  2029    743885.91
2020-07-13  2029    687345.41
2020-07-12  2028    631264.57
2020-07-11  2028    576558.97
2020-07-10  2028    519336.1
2020-07-09  2028    468372.56
2020-07-08  2028    423332.98
2020-07-07  2028    371895.4
2020-07-06  2028    318428.22
2020-07-05  2027    265560.11
2020-07-04  2027    214018.45
2020-07-03  2027    159850.71
2020-07-02  2027    108456.77
2020-07-01  2027    53559.46

What I'm trying to get is this:

Date        ISOWeek Value
2020-07-19  2029    1006353.56
2020-07-12  2028    631264.57
2020-07-05  2027    265560.11

This is all I've been able to get

SELECT MAX(Date) AS Date
,MAX([ISOWeek]) AS [ISOWeek]
,SUM(SUM([Value])) OVER (PARTITION BY YEAR(MAX(Date)), MONTH(MAX(Date)) ORDER by DAY(MAX(Date))) AS [Value]

FROM [Demo].[MTD_Daily]
GROUP BY ISOWeek
ORDER BY Date DESC

However, this is what it yielded:

Date    ISOWeek Value
2020-07-19 00:00:00 2029    1111193.86
2020-07-12 00:00:00 2028    736104.87
2020-07-05 00:00:00 2027    370400.41

I can provide a .csv file with this data if it helps to reproduce this.

Thank you for taking the time.

Answer 1

Starting from your existing query, you can rank records by descending date for each ioweeek in a subquery with row_number(), then use that information to filter in the outer query:

selet date, isoweek, value
from (
    select 
        date, 
        isoweek,
        sum(value) over (partition by year(date), month(date) order by date) as value,
        row_number() over(partition by isoweek order by date desc) rn
    from demo.mtd_daily
) t
where rn = 1
order by date desc