CODEWITHSUNDEEP
sqlsql-server
AlisonGrey
AlisonGrey

Reputation: 507

Get average for everyday - SQL

I have a field of Timestamp and I want to find the average hours spent every day.

Lets say:

created: 2020-01-01 23:15:44
Resolved: 2020-01-04 12:00:00

I need my output to be:

Date         Mins
2020-01-01   45
2020-01-02   1440
2020-01-03   1440
2020-01-04   720

I have built a calendar table, but I'm not sure how to use it.

Upvotes: 0

Views: 76

Answers (3)

procra
procra

Reputation: 555

Let tickets be your table and creation_timestamp as resolve_timestamp are your described fields. All in all we have three steps to compute:

  • calculate the interval between two timestamps of one row

  • group the intervals by day

  • for each day, calculate the average interval

      SELECT
      t.date,

      /*Convert the interval of this function into a number*/
      extract(

          /*calculate the intervals here and draw the average*/
          day from AVG(t.resolve_timestamp - t.creation_timestamp)* 1440 + 
          hour from AVG(t.resolve_timestamp - t.creation_timestamp) * 60 +
          minute from AVG(t.resolve_timestamp - t.creation_timestamp)

      ) AS avg_duration_in_minutes

      FROM tickets t

  /*do that distinctly for every t.date*/
  GROUP BY t.date
  ORDER BY t.date DESC;

NOTE: This is a syntax you would use on an Oracle DB. There might be differences to SQL Server

Upvotes: 1

Thiyagu
Thiyagu

Reputation: 1330

Try this RECURSIVE CTE

 DECLARE @CreateDate DateTime='2020-01-01 23:15:44',
 @EndDate DATETIME='2020-01-04 12:00:00'

;WITH sample AS (
  SELECT @CreateDate  AS StartDate
  UNION ALL
  SELECT DATEADD(dd, 1, StartDate)
  FROM sample s
  WHERE DATEADD(dd, 1, StartDate) <= DATEADD(dd, 1,@EndDate)
  )

SELECT CAST(StartDate as date),case when StartDate=@CreateDate then 
DATEDIFF(MI,StartDate,DATEADD(day, DATEDIFF(day, 0, StartDate), '23:59:59'))+1
else DATEDIFF(MI,CAST(StartDate as date),DATEADD(day, DATEDIFF(day, 0, StartDate), 
'23:59:59'))+1 end
 FROM sample

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

You can join to your calendar table and calculate the time different for beginning and ending of the date range.

select c.[date] as [Date], 
       case when c.[date] = convert(date, t.created) 
            then datediff(minute, t.created, dateadd(day, 1, c.[date]))
            when c.[date] = convert(date, t.resolved) 
            then datediff(minute, c.[date], t.resolved)
            else 1440
            end AS [Mins]
from   yourtable t
       inner join calendar c on  c.[date] >= convert(date, t.created)
                             and c.[date] <= convert(date, t.resolved)

db<>fiddle

If you need the average, just use GROUP BY and AVG() accordingly

Upvotes: 2

Related Questions