SQL Period Calculation for Total Downtime per Month
Period calculation in SQL or time calculations where developers sum specific events durations based on start and end times require coding by Transact-SQL developers. Generally SQL time duration calculation is difficult if you have to report time durations per pre-defined time blocks or time periods. And in those cases using DATEDIFF SQL function is not enough to get the result easily.
In this T-SQL tutorial, I'll try to prepare a simple report which lists server downtimes per each month. I had to use a reference table where time periods are stored. In this tutorial since downtimes per month is required to be listed, our reference time period table is going to include month data like start and end times, etc. You will see the details of the reference period database table and which fields are used how in the SQL script for the report.
First of all, create a helper table where we sql programmers frequently join to get data in their sql scripts. In this table we will store month information which forms the base of reporting in our SQL tutorial.
create table downtime_month_data (
dm_id int not null,
dm_year int not null,
dm_month smallint not null,
dm_description as (DATENAME(mm,dm_starttime) + space(1) + Datename(yyyy, dm_starttime)),
dm_starttime datetime not null,
dm_endtime datetime not null,
dm_fullmonth int not null
constraint PK_downtime_month_data_dm_id Primary Key (dm_id)
)
Now we can populate sample data in this base table. It is important that each month should be inserted into downtime_month_data base table in order. Otherwise, sql calculations will return wrong values.
declare @i int = 0
declare @d datetime
while @i < 12
begin
select @d = dateadd(mm, @i,'20140101'), @i = @i + 1
insert into downtime_month_data
select @i, datepart(YYYY,@d), datepart(MM,@d),
@d,dateadd(ms,-3,dateadd(mm,1,@d)),
datediff(mi, @d, dateadd(mm, 1, @d))
end
select * from downtime_month_data
And now let's create sample data table for downtime calculation in our SQL tutorial scripts. This table can also be used for gap time calculation as well.
create table server_downtimes (
id int identity(1,1) not null,
servername nvarchar(200) not null,
downtime_start datetime not null,
downtime_end datetime
constraint PK_server_downtimes_id Primary Key (id)
)
And enter sample sample data where downtime start and downtime end values are provided.
insert into server_downtimes select 'SQLServer2012', '2014-01-10 10:10', '2014-01-10 16:30'
insert into server_downtimes select 'SQLServer2014', '2014-01-10 10:10', '2014-02-01 17:10'
insert into server_downtimes select 'KodyazTestServer', '2014-01-10 10:10', '2014-05-17 09:00'
I need two simple SQL user defined functions to use within SQL codes for calculating time periods. One of these two sql functions returns the period start time and the other one returns the period end time.
create function get_period_starttime(@dm_id int)
returns datetime
as
begin
declare @period_MonthStart datetime
select @period_MonthStart = dm_starttime from downtime_month_data where dm_id = @dm_id
return @period_MonthStart
end
go
create function get_period_endtime(@dm_id int)
returns datetime
as
begin
declare @period_MonthEnd datetime
select @period_MonthEnd = dm_endtime from downtime_month_data where dm_id = @dm_id
return @period_MonthEnd
end
After all our database tables and user defined functions are created and sample tables are populated with example data, we can now create SQL script which will return downtimes grouped by the time periods expressed as months as follows.
declare @t as table (
monthly_periods_id int,
downtimes_id int,
value int
);
with cte as (
select
*,
(select max(dm_id) from downtime_month_data where d.downtime_start > dm_starttime) s,
(select min(dm_id) from downtime_month_data where dm_endtime > d.downtime_end) e
from server_downtimes d
)
insert into @t
select
s, id, datediff(mi, downtime_start, downtime_end)
from cte
where e = s
union
select
s, id, datediff(mi, downtime_start, dbo.get_period_endtime(s))
from cte
where e = s + 1
union
select
e, id, datediff(mi, dbo.get_period_starttime(e), downtime_end)
from cte
where e = s + 1
union
select
s, id, datediff(mi, downtime_start, dbo.get_period_endtime(s))
from cte
where e - s > 1
union
select
e, id, datediff(mi, dbo.get_period_starttime(e), downtime_end)
from cte
where e - s > 1
union
select
p.dm_id, cte.id, p.dm_fullmonth
from cte
inner join downtime_month_data p on p.dm_id > s and p.dm_id < e
select
dm_description,
sum(value) as total_downtime
from @t
inner join downtime_month_data on monthly_periods_id = dm_id
group by dm_description
