CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Andrew Bullock
Andrew Bullock

Reputation: 37378

TSQL Averaging over datepart

I have a table with a datetime column in it, consider it an event log for simple, analogous purposes.

I want to produce a report detailing the average number of events that occur at each time of day, to 30 min accuracy.

so the logic is,

I also don't want to have any time holes in my data, for example, if nothing occurred in the 00:00 - 00:30 range, i want to report a 0, rather than having a missing row.

How can I achieve this?

Upvotes: 1

Views: 492

Answers (4)

elvis
elvis

Reputation: 312

What I've done is use an auxiliary table of numbers (a 1 column table with number 1 to 1 million) and join to it, adding the value of the number with the dateadd function to the midnight of the date.

since you want 30 minute intervals, then you want to use the dateadd(minute, number*30, yourdate) where number <= 48 (since there are 1440 minutes in a day)/30 = 48 intervals. This will create your time intervals.

Then simply count your occurrences that happen in between the time intervals.

Upvotes: 0

daniloquio
daniloquio

Reputation: 3902

Here is the code you need: (tested in sql2008 and works fine!)

-- Table with the 48 30mins periods of the day
CREATE TABLE #Periods
(
Num INT 
)    
DECLARE @idt INT
SET @idt = 1
WHILE (@idt <= 48)
BEGIN
INSERT INTO #Periods VALUES (@idt)
SET @idt = @idt + 1
END
--Average of the count for each period on all days.
SELECT DayTable.Num, AVG(CAST(DayTable.DayCount AS DECIMAL))
FROM
(   --Total incidents for each interval on each day.
    SELECT CAST(FLOOR(CAST(#MyLog.LogDate AS FLOAT)) AS DATETIME) AS DayWithOutTime,
           #Periods.Num AS Num, 
           COUNT(#MyLog.ID) AS DayCount
    FROM #Periods LEFT JOIN #MyLog
            ON #Periods.Num = (DATEPART(hh, #MyLog.LogDate)*60 + DATEPART(mi,#MyLog.LogDate))/30
    GROUP BY CAST(FLOOR(CAST(#MyLog.LogDate AS FLOAT)) AS DATETIME),
             #Periods.Num
) AS DayTable
GROUP BY DayTable.Num

DROP TABLE #Periods

Where #NyLog is the table where your datetime is. It shows the count of incidences for each 30min period. The Period 1 is 00:00 -> 00:30 and Period 48 is 23:30 -> 24:00.

Upvotes: 1

gonsalu
gonsalu

Reputation: 3184

WITH TestDates (date) AS (
    SELECT CONVERT(DATETIME, '2011-11-15 10:00') UNION ALL
    SELECT CONVERT(DATETIME, '2011-11-15 11:31') UNION ALL
    SELECT CONVERT(DATETIME, '2011-11-16 10:00')

-- CTE to generate 4 million rows with a sequential integer starting at 0
), GeneratedRows (seq) AS (
    SELECT ROW_NUMBER() OVER (ORDER BY N1.number) - 1
      FROM master..spt_values AS N1
     CROSS JOIN master..spt_values AS N2
     WHERE N1.name IS NULL
       AND N2.name IS NULL

), RoundedTestDates (date) AS (
    SELECT CASE
             -- Subtract the minute part
             WHEN DATEPART(MINUTE, date) <  25 THEN DATEADD(MINUTE, -1 * DATEPART(MINUTE, date), date)
             -- Subtract the minute part, then add an hour
             WHEN DATEPART(MINUTE, date) >= 45 THEN DATEADD(HOUR, 1, DATEADD(MINUTE, -1 * DATEPART(MINUTE, date), date))
             -- Subtract the minute part, then add an half-hour
             ELSE DATEADD(MINUTE, 30, DATEADD(MINUTE, -1 * DATEPART(MINUTE, date), date))
           END
      FROM TestDates
)

SELECT rounded_date = GeneratedPeriod.date
     , ocurrences   = COUNT(RoundedTestDates.date)
  FROM (SELECT DATEADD(MINUTE, 30 * seq, (SELECT MIN(date) FROM RoundedTestDates))
          FROM GeneratedRows
       ) AS GeneratedPeriod (date)
  LEFT JOIN RoundedTestDates
    ON GeneratedPeriod.date = RoundedTestDates.date
 WHERE GeneratedPeriod.date <= (SELECT MAX(date) FROM RoundedTestDates)
 GROUP BY GeneratedPeriod.date
 ORDER BY 1

Upvotes: 1

aF.
aF.

Reputation: 66687

In sybase sql is something like this, in sql-server you might need to do some changes but not much :)

create procedure Test @startDay varchar(8), @endDay varchar(8)
as

declare @ocurrence int

declare @numberOfDays int
select @numberOfDays = 0

create table #intervals (
    interval_hour int,
    interval_min_minute int,
    interval_max_minute int,
    ocurrences int
)

create table #insertions (
    hour int,
    minute int
)

declare @hour int, @minute int
select @hour = 0


-- create the intervals
while (@hour <> 24)
begin
    insert into #intervals values(@hour,0,29,0)
    insert into #intervals values(@hour,30,59,0)

    select @hour = @hour + 1
end


while(@startDay <> @endDay)
begin
    insert into #insertions
    select datepart(hh, *yourcolumn*), datepart(mm, *yourcolumn*) from *yourdb..yourtable* where convert(varchar(8), *yourcolumn*, 112) = @startDay

    select @startDay = convert(varchar(8), dateadd(dd, 1, convert(datetime, @startDay, 112)), 112)
    select @numberOfDays = @numberOfDays + 1
end



declare cursor1 cursor for
select hour, minute from #insertions


open cursor1
fetch cursor1 into @hour, @minute

while (@@sqlstatus=0)
begin


update #intervals
set i.ocurrences = i.ocurrences + 1
from #intervals i
where interval_hour = @hour and @minute between interval_min_minute and interval_max_minute

fetch cursor1 into @hour, @minute
end

close cursor1

select interval_hour 'hour', interval_min_minute 'min minute', interval_max_minute 'max minute', ocurrences,
    case when ocurrences > 0 then convert(float, ocurrences) / convert(float, @numberOfDays) else 0 end 'ocurrences average' from #intervals

drop table #intervals
drop table #insertions

go

Upvotes: 0

Related Questions