Sql query doubt

[nikhilboorla]

I have table like this

Ticket Date  |Total Tickets Created | Pending Tickets 
------------------------------------------------------
01-04-2021   |5000                  | 200 
02-04-2021   |2000                  | 100 
03-04-2021   |3000                  | 300 

   select t.created_date,t.source,t.ticket_type,
   count(*) as Total_Tickets_Created,
   sum(case when tch.before='Pending Credential' then 1 else 0 end) as Pending_Tickets,
        from r4e_mongo.mongo_repbiz_tickets t
        join r4e_mongo.mongo_repbiz_ticket_changelog tch on t.id=cast(tch.ticket_id as string) 

I want to get Pending Tickets Backlog until the previous day like below to create a report in google data studio with date filter.

Ticket Date  |Total Tickets Created | Pending Tickets | Pending Tickets Backlog
---------------------------------------------------------------------------------
01-04-2021   |5000                  | 200             |    1000
02-04-2021   |2000                  | 100             |    1100 --(1000+100)
03-04-2021   |3000                  | 300             |    1400 --(1100+300)

For example...i have selected October 15th date in report...it should show pending tickets backlog count until October 14

[Pitta]

ltt

[8pm]

3 hours ago, nikhilboorla said:

I have table like this

Ticket Date  |Total Tickets Created | Pending Tickets 
------------------------------------------------------
01-04-2021   |5000                  | 200 
02-04-2021   |2000                  | 100 
03-04-2021   |3000                  | 300 

   select t.created_date,t.source,t.ticket_type,
   count(*) as Total_Tickets_Created,
   sum(case when tch.before='Pending Credential' then 1 else 0 end) as Pending_Tickets,
        from r4e_mongo.mongo_repbiz_tickets t
        join r4e_mongo.mongo_repbiz_ticket_changelog tch on t.id=cast(tch.ticket_id as string) 

I want to get Pending Tickets Backlog until the previous day like below to create a report in google data studio with date filter.

Ticket Date  |Total Tickets Created | Pending Tickets | Pending Tickets Backlog
---------------------------------------------------------------------------------
01-04-2021   |5000                  | 200             |    1000
02-04-2021   |2000                  | 100             |    1100 --(1000+100)
03-04-2021   |3000                  | 300             |    1400 --(1100+300)

For example...i have selected October 15th date in report...it should show pending tickets backlog count until October 14

select ticket date, total_tickets_created,pending_tickets

,sum(pending_tickets) over(order by date) as pending_tickets_backlog

from mongo_repbiz_tickets

 

[8pm]

TS @nikhilboorla sound ledu

[nikhilboorla]

8 hours ago, 8pm said:

select ticket date, total_tickets_created,pending_tickets

,sum(pending_tickets) over(order by date) as pending_tickets_backlog

from mongo_repbiz_tickets

 

worked..thanks..

[8pm]

15 minutes ago, nikhilboorla said:

worked..thanks..

Aithe like kottu 

[8pm]

@modi_bhakth 🤣

[nikhilboorla]

5 hours ago, 8pm said:

Aithe like kottu 

will it work for any date?

for ex. if i select oct 13th in date filter,it should show pending ticket count until oct 12..

[8pm]

4 hours ago, nikhilboorla said:

will it work for any date?

for ex. if i select oct 13th in date filter,it should show pending ticket count until oct 12..

Adhi running sum it keeps on adding that’s it.

now you have to chose filters wisely