sql running sum doubt

[nikhilboorla]

I have query with below columns  but not gettin backlog count for each day.

with tickets_current_pending as (
select t.tenant_id,source,pd.param_date pending_date,count(t.id) backlog_pending
from ticket_status st
join tickets t on t.id=st.id
join ticket_changelog ts on cast(ts.ticket_id as string)=st.id and ts.created_date=st.created_date
join param_date pd on 1=1
where ts.after='Pending Credential'
group by 1,2,3
)

select p.period 
,p.tenant_id
,p.source
,t.ticket_type,t.name
,ifnull(t.tickets_created,0) tickets_created
,ifnull(backlog_pending,0) tickets_backlog
,ifnull(ifnull(tickets_pending,0)+ifnull(backlog_pending,0),0) tickets_to_pending
,ifnull(tickets_open,0) tickets_pending_to_open
,ifnull(open_tickets,0) open_tickets
,ifnull(closed_tickets,0) closed_tickets
,SUM (ifnull(backlog_pending,0)+ifnull(tickets_pending,0)-ifnull(tickets_open,0)) OVER (partition by p.tenant_id,p.source ORDER BY period) AS total_pending_tickets
from period p 
left join tickets_current_pending tp on tp.pending_date=p.period and tp.tenant_id=p.tenant_id and tp.source=p.source
left join tickets_to_pending tpend on tpend.pending_date=p.period and tpend.tenant_id=p.tenant_id and tpend.source=p.source
left join tickets_to_open topen on topen.open_date=p.period and topen.tenant_id=p.tenant_id and topen.source=p.source
left join open_tickets open on open.open_tickets_date=p.period and open.tenant_id=p.tenant_id and open.source=p.source
left join closed_tickets closed on closed.closed_tickets_date=p.period and closed.tenant_id=p.tenant_id and closed.source=p.source

Period	Ticket Type	Tickets Created	Tickets to Pending	Tickets Backlog Pending Credentials	Tickets Pending to Open	Open Tickets	Closed Tickets	Total Pending Tickets
May 2, 2021	RespondToReview	3169	1744	1376	0	40	2274	2680
May 3, 2021	RespondToReview	6528	900	0	2	51	5184	4884
May 4, 2021	RespondToReview	5893	699	0	4	181	6291	5880
May 5, 2021	RespondToReview	5824	1224	0	1	34	7284	10559

 

My query should show backlog count like below table.

Period	Ticket Type	Tickets Created	Tickets to Pending	Tickets Backlog Pending Credentials	Tickets Pending to Open	Open Tickets	Closed Tickets	Total Pending Tickets
May 2, 2021	RespondToReview	3169	1744	1376	0	40	2274	3120
May 3, 2021	RespondToReview	6528	900	3120	2	51	5184	4018
May 4, 2021	RespondToReview	5893	699	4018	4	181	6291	4713
May 5, 2021	RespondToReview	5824	1224	4713	1	34	7284	5936

[DummyVariable]

ltt

[RoadRomeo]

I'm not related to IT

May the force be with you

[afacc123]

LTT