sql doubt

[nikhilboorla]

I have to find Pending tickets from Previous day/Backlog(from previous days) and Converted from Pending tickets to Open for creating a report.

I have got the pending tickets using the following query.

select ticket_date,count(ticket_id) as Total_tickets_created,
sum(case when s.name = 'Pending Credential' then 1 else 0 end) as Pending_Credentials_On_hold,
( select Pending_Credentials from tickets where date >= DATEADD(day, -1, GETDATE())) ) as Pending_tickets_Backlog 
      from tickets t 
and datetime_trunc(Datetime(t.created_date,"America/Los_Angeles"),day) <> datetime_trunc(current_datetime("America/Los_Angeles"),day)

I tried above query for find Pending tickets from Previous day/Backlog but couldn't get the required output also I need to find Converted from Pending tickets to Open also using above query.Required output is below

 

[NiranjanGaaru]

@Hydrockers

[8pm]

10 minutes ago, nikhilboorla said:

I have to find Pending tickets from Previous day/Backlog(from previous days) and Converted from Pending tickets to Open for creating a report.

I have got the pending tickets using the following query.

select ticket_date,count(ticket_id) as Total_tickets_created,
sum(case when s.name = 'Pending Credential' then 1 else 0 end) as Pending_Credentials_On_hold,
( select Pending_Credentials from tickets where date >= DATEADD(day, -1, GETDATE())) ) as Pending_tickets_Backlog 
      from tickets t 
and datetime_trunc(Datetime(t.created_date,"America/Los_Angeles"),day) <> datetime_trunc(current_datetime("America/Los_Angeles"),day)
where date(ticket_date) between '2021-04-01' and '2021-04-02'

I tried above query for find Pending tickets from Previous day/Backlog but couldn't get the required output also I need to find Converted from Pending tickets to Open also using above query.Required output is below

 

Use window function Lag to get previous day 

[nikhilboorla]

8 minutes ago, 8pm said:

Use window function Lag to get previous day 

how to get backlog tickets(not only yesterday)...

[8pm]

Just now, nikhilboorla said:

how to get backlog tickets(not only yesterday)...

Ante enni rojulu backlog kavali?

oka range untadi kada

[Hydrockers]

13 minutes ago, NiranjanGaaru said:

@Hydrockers

Manaki antha telive unte ee patiki ee hitech city lo flat koni nelaki lacha emi lu Kattukuntu undevadini

[8pm]

2 minutes ago, nikhilboorla said:

how to get backlog tickets(not only yesterday)...

Table structure veyyi morning choosta

[NiranjanGaaru]

Just now, Hydrockers said:

Manaki antha telive unte ee patiki ee hitech city lo flat koni nelaki lacha emi lu Kattukuntu undevadini

Ok

[nikhilboorla]

1 hour ago, 8pm said:

Ante enni rojulu backlog kavali?

oka range untadi kada

I want from january 1st to march 31st...column should be created seperately for this as 'Pending tickets Backlog'.

April 1st Pending tickets will be stored in 'Pending Credentials'

[nikhilboorla]

1 hour ago, 8pm said:

Table structure veyyi morning choosta

columns mentioned in the query are the ones...no additional columns

[former]

2 hours ago, nikhilboorla said:

I have to find Pending tickets from Previous day/Backlog(from previous days) and Converted from Pending tickets to Open for creating a report.

I have got the pending tickets using the following query.

select ticket_date,count(ticket_id) as Total_tickets_created,
sum(case when s.name = 'Pending Credential' then 1 else 0 end) as Pending_Credentials_On_hold,
( select Pending_Credentials from tickets where date >= DATEADD(day, -1, GETDATE())) ) as Pending_tickets_Backlog 
      from tickets t 
and datetime_trunc(Datetime(t.created_date,"America/Los_Angeles"),day) <> datetime_trunc(current_datetime("America/Los_Angeles"),day)
where date(ticket_date) between '2021-04-01' and '2021-04-02'

I tried above query for find Pending tickets from Previous day/Backlog but couldn't get the required output also I need to find Converted from Pending tickets to Open also using above query.Required output is below

 

what is the current table structure & also give some sample data?

[former]

Is ticket table have all the ticket status events?

[ZoomNaidu]

46 minutes ago, nikhilboorla said:

I want from january 1st to march 31st...column should be created seperately for this as 'Pending tickets Backlog'.

April 1st Pending tickets will be stored in 'Pending Credentials'

Seems Last where condition is only checking for a certain date range where as ur ‘Pending Tickets backlog’ looking for just previous day’s one ? 

[nikhilboorla]

52 minutes ago, former said:

Is ticket table have all the ticket status events?

yes so i have filtered the table using status=pending credential

[nikhilboorla]

 

12 minutes ago, ZoomNaidu said:

Seems Last where condition is only checking for a certain date range where as ur ‘Pending Tickets backlog’ looking for just previous day’s one ? 

sorry..i have removed this line...Pending tickets backlog i am able to do until yesterday but i want to calculate overall tickets until yesterday(except today)