How to calculate the number of working minutes…FAST :)

This is always a performance thing, ETL-wise, but also in stored procedures.
I’ve published an example on how to calculate the number of working days (rounded) via a scalar-valued function:
Function: Calculate working days

This example however calculates the working minutes between dates, while taking into account the company and/or national holidays and weekends.
You could extend the functionality to also include local office hours.

Three examples:
1) The number of working minutes between a given date/time-value and the present (NOW)
2) The number of working minutes between two given date/time-values
3) The number of working minutes between two given date/time-values from a large dataset

Number-of-working-minutes-between-dates-sql-server-set-based

Testing results:
I ran a test on a large dataset which included actual order dates and after the so called ‘WorkingMinutes’-table was made in 4~5 seconds, it took only 3 seconds to process more than 1 million records!

Download the SQL script here:
WorkingMinutes-SQL-Examples








Advertisements

SET DATEFORMAT, a thing to remember.

Today I faced with an issue that an older SQL 2000 server (source db) had different formatting settings (my guess is the OS’s regional settings) than my DWH server (target db) or my local machine. Googled a bit and found the ‘SET DATEFORMAT‘-statement:
SET DATEFORMAT { dym | dmy | mdy | myd | ydm | ymd }

An easy method to check which format setting the server is applying/using, is to execute this SQL query:
SELECT DatePart(Month, '11-7-2014') AS CurrentMonth


How it works

SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '07/11/14'
SELECT @datevar AS CurrentDate
GO

SET DATEFORMAT ydm
GO
DECLARE @datevar datetime
SET @datevar = '14/11/07'
SELECT @datevar AS CurrentDate
GO

SET DATEFORMAT ymd
GO
DECLARE @datevar datetime
SET @datevar = '14/07/11'
SELECT @datevar AS CurrentDate
GO

The results:

sql-set-dateformat-results








SQL: A neat trick to create a SELECT-query which will include all the column names

For this one you’ll need to thank SQL Chick (Melissa Coates)
Based on here blog post:
http://www.sqlchick.com/entries/2014/2/1/shortcut-to-script-individual-columns-from-a-table-valued-function-in-ssms

Basically start building a basic SELECT-query with ‘Select *‘, then select it all (CTRL+A) and hit CTRL+SHIFT+Q to open up the Query Designer. Now you can easily copy/paste the query which it has generated for you and this one does include all the column names! 😉

Neat-trick-to-create-a-select-statement-which-includes-all-columns-handy








Table Valued Constructors: insert multiple rows of hard-coded values into a table

This one I borrowed from Plamen Ratchev:
http://pratchev.blogspot.nl/2008/01/table-value-constructors-in-sql-server.html

One of the new features of SQL Server 2008 is the support for table value constructors (part of ANSI SQL). Here are a couple quick examples of using them.
-- Populate sample table

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol VARCHAR(30));

In the past, populating table rows was done like this:
INSERT INTO Foo VALUES (1, 'Books');
INSERT INTO Foo VALUES (2, 'CDs');
INSERT INTO Foo VALUES (3, 'DVDs');

-- or

INSERT INTO Foo (keycol, datacol)
SELECT 1, 'Books'
UNION ALL
SELECT 2, 'CDs'
UNION ALL
SELECT 3, 'DVDs';

-- or using on the fly

SELECT keycol, datacol
FROM ( SELECT 1, 'Books'
UNION ALL
SELECT 2, 'CDs'
UNION ALL
SELECT 3, 'DVDs') AS Foo (keycol, datacol);

Here is how the same can be done with SQL Server 2008 table value constructors:
INSERT INTO Foo (keycol, datacol)
VALUES (1, 'Books'), (2, 'CDs'), (3, 'DVDs');

-- or using on the fly

SELECT keycol, datacol
FROM ( VALUES (1, 'Books'),
(2, 'CDs'),
(3, 'DVDs') ) AS Foo (keycol, datacol);

-- and CTE version

WITH Foo (keycol, datacol)
AS( SELECT *
FROM ( VALUES (1, 'Books'),
(2, 'CDs'),
(3, 'DVDs') ) AS F (keycol, datacol))
SELECT keycol, datacol
FROM Foo;

Another interesting option is to derive a row value from a subquery, like this:
INSERT INTO Foo (keycol, datacol)
VALUES ((SELECT MAX(keycol) + 1 FROM Foo), 'Tapes');


The big advantage of TVCs is that you save typing 🙂








Why not to use a MONEY data type to represent a currency or other financial value

Remember my last post ‘Why not to use a float to represent a currency or other financial value‘?

Here’s another one in regards to the usage of the MONEY data type:


--Decimal/Numeric versus Money:

DECLARE
@mon1 MONEY,@mon2 MONEY,@mon3 MONEY,@mon4 MONEY,
@num1 DECIMAL(19,4),@num2 DECIMAL(19,4),@num3 DECIMAL(19,4),@num4 DECIMAL(19,4),@num5 DECIMAL(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SET @num5 = @num1/@num2

SET @num5 = @num5*@num3

SELECT @mon4 AS moneyresult,
@num4 AS numericresult,
@num5 AS numericresult2

The result:

money-data-type-result



Download the complete example:
Decimal-Numeric versus Float and Money








Truncate certain tables (or in this case Error-tables)

Here’s another stored procedure which truncates certain tables (in this case *_Errors tables).
In case you’re using Fast Load on your SSIS Destination, it may occur that it fully loads that gigantic table into your Error-table.
So make sure to monitor those or truncate them every few days.


-- =============================================
-- Author: Clint Huijbers
-- Create date: 2014-08-07
-- Description: This SP is used to truncate *_Errors tables
-- =============================================
CREATE PROCEDURE [dbo].[usp_TruncateErrorTables]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SQL VARCHAR(800) --SQL query to execute

DECLARE CursorName CURSOR FOR
SELECT 'TRUNCATE TABLE [dbo].['+Name+']' AS SQLStatement
FROM sys.tables
WHERE is_ms_shipped = 0
AND Name LIKE '%_Errors'

OPEN CursorName
FETCH NEXT FROM CursorName INTO @SQL
WHILE (@@fetch_status = 0)
BEGIN

EXEC(@SQL)

PRINT @SQL+' --- Executed'

FETCH NEXT FROM CursorName INTO @SQL
END

CLOSE CursorName
DEALLOCATE CursorName

END