-1

I have a table having attributes: ticket#, closed date & resolve time.
I need to write a SQL query to calculate the average resolve time in each quarter.

eg in quarter1: 5 tickets are closed (10 days,1 day,3, day,1day.10 days) are resolved time for each ticket then average resolve time is 5 days
output should be as below 

Quarter  days    
Q1       5    
Q2       2 (similarly)   
Q3       7   
Q4       9

sample data

I really stuck in this query

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Nisha Verma
  • 91
  • 2
  • 12
  • 1
    Can you explain with a minimal and reproducible sample query where you were stuck. – Barbaros Özhan Jan 19 '20 at 05:47
  • look at this: [T-SQL calculating average time](https://stackoverflow.com/questions/15349586/t-sql-calculating-average-time) – Rebwar Jan 19 '20 at 05:49
  • "SQL Developer" is a SQL tool only for the oracle database, it can't be used with SQL Server. I re-tagged the question with `oracle` instead of `sql-server` –  Jan 19 '20 at 09:13

2 Answers2

0

You can write something like this.

SELECT AVG(resolvetime) AS 'Average Resolve Time' 
FROM [TableName]
WHERE closeddate BETWEEN [Start date of Quarter] AND [End date of Quarter]
LahiruK717
  • 47
  • 7
  • itried as per suggestion as per LahiruK717 SELECT avg(p.resolvetime) as AverageRT FROM sampletable WHERE closeddate BETWEEN '2019-07-01' AND '2019-09-30' but unable to get desired result – Nisha Verma Jan 19 '20 at 06:13
0

** This code is tested on Oracle Database. For any syntax related error, you can replace according to you database. Also, update your column name,date,timestamp,timezone and table name accordingly.

The sample query is like this ...

 select temp.quarter,avg(resolve_time) 

from 
    (
    select resolve_time,
    CASE  
    WHEN (close_date between TO_DATE('2019-APR-01 00:00','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN') and TO_DATE('2019-JUN-30 23:59','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN')) THEN 1
    WHEN (close_date between TO_DATE('2019-JUL-01 00:00','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN') and TO_DATE('2019-SEP-30 23:59','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN')) THEN 2
    WHEN (close_date between TO_DATE('2019-OCT-01 00:00','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN') and TO_DATE('2019-DEC-31 23:59','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN')) THEN 3
    WHEN (close_date between TO_DATE('2020-JAN-01 00:00','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN') and TO_DATE('2020-MAR-31 23:59','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN')) THEN 4
    ELSE 0 END quarter
    from TestStack
    ) temp 

group by temp.quarter;
Singh Arun
  • 320
  • 2
  • 5