SQL Windowing Feature for Monday

I'm looking to have a query that sum accumulate on a column but it needs to restart every monday but seems is not possible to make it with window function Here's my code:

    WITH t  (fecha, nombre_dia, distribuidor, monto)as (
SELECT CAST(t.fecha AS DATE)fecha
, CASE WHEN datename(dw,t.fecha)='Monday' then 1 else 2 end  nombre_dia
,d.NombreDistribuidor distribuidor
,sum(tr.Monto)monto

from HechosTransferencia tr inner join DimensionTiempo t on tr.DimensionTiempoId=t.DimensionTiempoId
inner join DimensionDistribuidor d on d.DimensionDistribuidorId=tr.DimensionDistribuidorId
WHERE        (t.Fecha BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, - 2, GETDATE()) - 2, 0) AND GETDATE())
group by CAST(t.fecha AS DATE)
, CASE WHEN datename(dw,t.fecha)='Monday' then 1 else 2 end ,d.NombreDistribuidor
)SELECT *,
SUM(monto) OVER(PARTITION BY nombre_dia, distribuidor
                ORDER BY fecha, nombre_dia, distribuidor  ROWS UNBOUNDED PRECEDING) AS RunningTotal
 from t where distribuidor='XYZ'

a simple sample of output would be

monday           XYZ    5      5
tuesday          XYZ    1      6
wednesday        XYZ    2      8
..
....
monday           XYZ    2       2         -- restart value every monday
tuesday          XYZ    2       4
..
...

---

I've just set up an example in rextester: http://rextester.com/MEXHM63945

Instead of trying to break the cumulative sum every Monday, simply set Monday as first day of week, and SUM() OVER PARTITION by Provider and WeekOfMonth.

I have not tested it, but I think you can get it without using a CTE window function. (Take a look at end of the answer.)

-- Set monday first day of week
SET DATEFIRST 1;

create table #data ([date] datetime, provider varchar(10), value int);
insert into #data values
('2017-01-02', 'XYZ', 1),
('2017-01-03', 'XYZ', 5),
('2017-01-04', 'XYZ', 3),
('2017-01-05', 'XYZ', 5),
('2017-01-06', 'XYZ', 4),
('2017-01-07', 'XYZ', 11),
('2017-01-08', 'XYZ', 11),
('2017-01-09', 'XYZ', 1),
('2017-01-10', 'XYZ', 5),
('2017-01-11', 'XYZ', 3),
('2017-01-12', 'XYZ', 5),
('2017-01-13', 'XYZ', 4),
('2017-01-14', 'XYZ', 11),
('2017-01-15', 'XYZ', 11);

SELECT provider, DATEPART(WEEK, date) as week_of_year, value,
       sum(value) OVER (Partition by provider, DATEPART(WEEK, date) ORDER BY date, provider) Acm
FROM #data
ORDER BY date, provider;

This is the result:

+----------+--------------+-------+-----+
| provider | week_of_year | value | Acm |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   1   |  1  |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   5   |  6  |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   3   |  9  |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   5   |  14 |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   4   |  18 |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   11  |  29 |
+----------+--------------+-------+-----+
|    XYZ   |       2      |   11  |  40 |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   1   |  1  |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   5   |  6  |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   3   |  9  |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   5   |  14 |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   4   |  18 |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   11  |  29 |
+----------+--------------+-------+-----+
|    XYZ   |       3      |   11  |  40 |
+----------+--------------+-------+-----+

I think this should do the job:

SELECT
    CAST(t.fecha AS DATE) fecha
    , d.NombreDistribuidor distribuidor
    , SUM(tr.Monto) OVER (PARTITION BY d.NombreDistribuidor, DATEPART(week, CAST(t.fecha AS DATE))
                          ORDER BY CAST(t.fecha AS DATE), d.NombreDistribuidor) monto
FROM
    HechosTransferencia tr
    INNER JOIN DimensionTiempo t ON tr.DimensionTiempoId=t.DimensionTiempoId
    INNER JOIN DimensionDistribuidor d ON d.DimensionDistribuidorId=tr.DimensionDistribuidorId
WHERE
    (t.Fecha BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, - 2, GETDATE()) - 2, 0) AND GETDATE())
GROUP BY
    CAST(t.fecha AS DATE), d.NombreDistribuidor;