SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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

sample month table for time period calculations SQL tutorial

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'

sample SQL tutorial data for server downtimes calculation

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

calculated downtime in SQL Server based on monthly periods







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems