Hi,
Below is my DDL
CREATE TABLE #Temp
(
Id INT,
ReportMonth int,
ReportQuarter int,
PaymentAmount MONEY
)
INSERT INTO #Temp
VALUES (123,1,1,25), (123,3,1,50),(123,4,2,50),(123,5,2,50),(135,1,1,50),(135,4,2,50)
SELECT * FROM #Temp
DROP TABLE #Temp
Below are the images of Input & Output i am trying to achieve, Can someone please help me with this
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Echo
Hot issues October--https://docs.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html
Try this query:
;
with Y
as
(
select m
from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) as Y(m)
),
X as
(
select Id, MAX(ReportMonth) as mm
from #Temp
group by Id
)
select X.Id,
Y.m as ReportMonth,
(Y.m - 1) / 3 + 1 as ReportQuarter,
isnull(t.PaymentAmount, $0) as PaymentAmount,
sum(t.PaymentAmount) over (partition by X.Id order by Y.m rows between unbounded preceding and current row) as CummulativeAmount
from Y
cross apply X
left join #Temp as t on t.Id = X.Id and t.ReportMonth = Y.m
where Y.m <= X.mm
order by Id, ReportMonth
5 people are following this question.
