Convert time to string - SQL

Scenario where a db field contains seconds, the user wanted a SSRS report displaying various aggregates of the seconds field as Hours - Minutes - Seconds.


IFOBJECT_ID('dbo.udf_SCC_TimeToString') IS NOT NULL
DROP FUNCTION dbo.udf_SCC_TimeToString ;
GO

CREATEFUNCTION dbo.udf_SCC_TimeToString
(
@timesec INT) --input in seconds as integer

RETURNSVARCHAR(25)

/*==========================================

Dom Horton

17/10/2012

==========================================*/

AS

 
BEGIN
DECLARE
@return
AS VARCHAR(25),

@a AS INT,

@b AS INT,

@hours AS INT,

@mins AS INT,

@secs AS INT;

SET@a = @timesec%3600 --get no. of seconds over the hour

SET@hours = (@timesec - @a)/3600 --get no. of seconds of completed hours, divide by hour in seconds to get completed hours

SET@b = @a --hold no. of secs over the hour

SET@a = @b%60--get no. of secs over the min

SET@mins = (@b - @a)/60 --get no. of seconds of completed mins, divide by min in secs to get completed minutes

SET@secs = @a

SET@return =

CASE
 
WHEN @timesec >=3600

THEN
 
convert(varchar(10),@hours) + ' hr ' +

convert(varchar(10),@mins) + ' min ' +

convert(varchar(10),@secs) + ' sec '

WHEN @timesec >=60

THEN

convert(varchar(10),@mins) + ' min ' +

convert(varchar(10),@secs) + ' sec '

ELSE

convert(varchar(10),@secs) + ' sec '

END;

RETURN
@return

END











Example below showing the Stored Procedure in use:

and in use in a SSRS report:

Missing indexes - SQL

SELECT index_handle, database_id, object_id, equality_columns, inequality_columns, included_columns, statement

FROM sys.dm_db_missing_index_details

Index usage - SQL

SELECT OBJECT_NAME(I.object_id) AS TableName, I.name, I.index_id, I.type_desc, I.is_unique,


I.fill_factor, I.is_padded, I.is_disabled, I.is_hypothetical,

IUS.index_id , IUS.user_seeks, IUS.user_scans, IUS.user_lookups, IUS.user_updates, IUS.last_user_seek,

IUS.last_user_scan, IUS.last_user_lookup, IUS.last_user_update, IUS.system_seeks, IUS.system_scans, IUS.system_lookups, IUS.system_updates,

IUS.last_system_seek, IUS.last_system_scan, IUS.last_system_lookup, IUS.last_system_update

FROM sys.indexes AS I LEFT OUTER JOIN

sys.dm_db_index_usage_stats AS IUS

ON I.object_id = IUS.object_id AND

I.index_id = IUS.index_id


ORDER BY OBJECT_NAME(I.object_id)

Useful SSRS expression syntax

=IIF(Fields!a.value>100, True, False)

--------------------------------------------------------
=IIF(Fields!a.value >=10, "Green", IIF(Fields!a.value >=1, "Blue", "Red"))

values >=10 are green, between 1 & 9 are blue, less than 1 are red

--------------------------------------------------------

=SWITCH(
    Fields!a.value >=10, "green",
    Fields!a.value >=1, "blue",
    Fields!a.value = 1, "yellow",
    Fields!a.value <=0, "Red")

>=10 are green, between 1 & 9 blue, =1 yellow, <=0 red

--------------------------------------------------------

SWITCH function finds the first expression that's true. It will not catch errors further along.
IIF function evaluates all parts of the expression.

--------------------------------------------------------


=IIF(Rownumber("scope") Mod 2 = 0, "Khaki", "White")

set "scope" to reset row colours for every group

--------------------------------------------------------