Uncategorized

Making date differences in SQL queries stand out

I had another employee here create a report that lists transactions from the transaction ledger for customers where the previous balance doesn’t match the balance on the next transaction, using SQL Server’s LEAD and LAG functions. To help quickly identify spans of time, next to the time I wanted something like ‘5s’, ’44m 5s’, or ‘2d 0h 45m 5s’. My eyes would automatically go to longer strings and identify those as a larger span of time.

-- User Defined Function

CREATE FUNCTION [dbo].[Humanize_DateDiff]
 (
 @t1 DATETIME2(7),
 @t2 DATETIME2(7),
 @showOnlyItems VARCHAR(50) = NULL
 )
 RETURNS VARCHAR(MAX)
 AS BEGIN
 /*
 PURPOSE
 Pretty prints a time difference between 2 dates.

@showOnlyItems should be NULL to include all fields, or a comma delimited list
 (with no spaces) of any combination of D,H,M,S,MS.

This won't print any zero-unit items until at least one non-zero unit is printed. For example,
 if there are no days, then '0d ' won't be printed at all.

TEST: [
 SELECT dbo.Humanize_DateDiff('12/1/2016', '12/25/2016 3:45 AM', NULL); -- 24d 3h 45m 12s 678ms
 SELECT dbo.Humanize_DateDiff('12/1/2016', '12/25/2016 3:45:12.678 AM', 'd,h,m'); -- 24d 3h 45m
 ]

REVISION HISTORY
 20170823 DKD
 Created
 */

/* for testing.
 DECLARE @t1 DATETIME2(7) = DATEADD(MILLISECOND, -((1000 * 60 * 60 * 24 * 3) + 4890976), SYSUTCDATETIME());
 DECLARE @t2 DATETIME2(7) = SYSUTCDATETIME();
 DECLARE @showOnlyItems VARCHAR(50) = 'D,H,M,S'; -- Comma delimited, no spaces between items, only use combos of ms,s,m,h,d; NULL = show everything
 --*/

---

DECLARE @op VARCHAR(MAX) = '';
 DECLARE @t_total BIGINT = 0;
 DECLARE @t_ms BIGINT, @show_ms BIT = 1;
 DECLARE @t_s INT, @show_s BIT = 1;
 DECLARE @t_m INT, @show_m BIT = 1;
 DECLARE @t_h INT, @show_h BIT = 1;
 DECLARE @t_d INT, @show_d BIT = 1;

IF (@showOnlyItems IS NOT NULL) BEGIN
 SET @showOnlyItems = ',' + @showOnlyItems + ',';
 IF (CHARINDEX(',d,', @showOnlyItems) = 0) SET @show_d = 0;
 IF (CHARINDEX(',h,', @showOnlyItems) = 0) SET @show_h = 0;
 IF (CHARINDEX(',m,', @showOnlyItems) = 0) SET @show_m = 0;
 IF (CHARINDEX(',s,', @showOnlyItems) = 0) SET @show_s = 0;
 IF (CHARINDEX(',ms,', @showOnlyItems) = 0) SET @show_ms = 0;
 END;

SET @t_total = DATEDIFF(MILLISECOND, @t1, @t2);

-- Days
 SELECT @t_d = FLOOR(@t_total / (1000 * 60 * 60 * 24));
 SELECT @t_total = @t_total - (@t_d * 1000 * 60 * 60 * 24);
 --PRINT '@t_d = ' + COALESCE(CAST(@t_d AS VARCHAR(MAX)), '') + '; @t_total = ' + COALESCE(CAST(@t_total AS VARCHAR(MAX)), '');
 IF ((@op != '' OR @t_d > 0) AND @show_d = 1) SELECT @op = @op + ' ' + CAST(@t_d AS VARCHAR(MAX)) + 'd';

-- Hours
 SELECT @t_h = FLOOR(@t_total / (1000 * 60 * 60));
 SELECT @t_total = @t_total - (@t_h * 1000 * 60 * 60);
 --PRINT '@t_h = ' + COALESCE(CAST(@t_h AS VARCHAR(MAX)), '') + '; @t_total = ' + COALESCE(CAST(@t_total AS VARCHAR(MAX)), '');
 IF ((@op != '' OR @t_h > 0) AND @show_h = 1) SELECT @op = @op + ' ' + CAST(@t_h AS VARCHAR(MAX)) + 'h';

-- Minutes
 SELECT @t_m = FLOOR(@t_total / (1000 * 60));
 SELECT @t_total = @t_total - (@t_m * 1000 * 60);
 --PRINT '@t_m = ' + COALESCE(CAST(@t_m AS VARCHAR(MAX)), '') + '; @t_total = ' + COALESCE(CAST(@t_total AS VARCHAR(MAX)), '');
 IF ((@op != '' OR @t_m > 0) AND @show_m = 1) SELECT @op = @op + ' ' + CAST(@t_m AS VARCHAR(MAX)) + 'm';

-- Seconds
 SELECT @t_s = FLOOR(@t_total / 1000);
 SELECT @t_total = @t_total - (@t_s * 1000);
 --PRINT '@t_s = ' + COALESCE(CAST(@t_s AS VARCHAR(MAX)), '') + '; @t_total = ' + COALESCE(CAST(@t_total AS VARCHAR(MAX)), '');
 IF ((@op != '' OR @t_s > 0) AND @show_s = 1) SELECT @op = @op + ' ' + CAST(@t_s AS VARCHAR(MAX)) + 's';

-- Milliseconds
 SELECT @t_ms = @t_total;
 SELECT @t_total = @t_total - (@t_ms); -- Should == ZERO now
 --PRINT '@t_ms = ' + COALESCE(CAST(@t_ms AS VARCHAR(MAX)), '') + '; @t_total = ' + COALESCE(CAST(@t_total AS VARCHAR(MAX)), '');
 IF ((@op = '' OR @t_ms > 0) AND @show_ms = 1) SELECT @op = @op + ' ' + CAST(@t_ms AS VARCHAR(MAX)) + 'ms';

SET @op = LTRIM(@op);

--SELECT @op;

RETURN @op;
 END
 GO

Note that I didn’t have the code recalculate figures if a time format was excluded, meaning that if I were to exclude days, the hours wouldn’t reflect the number of days.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s