SQL Server Innovations

CREATE FUNCTION [dbo].[fnSplitString] (     @string NVARCHAR(MAX)   , @delimiter CHAR(1) ) RETURNS @output TABLE (     splitdata NVARCHAR(MAX) ) BEGIN     DECLARE @start INT           , @end   INT;     SELECT  @start = 1           , @end = CHARINDEX(@delimiter, @string);     WHILE @start < LEN(@string) + 1     BEGIN         IF @end = 0             SET @end = LEN(@string) + 1;         INSERT INTO @output         (             splitdata         )         VALUES (SUBSTRING(@string, @start, @end - @start));         SET @start = @end + 1;         SET @end = CHARINDEX(@delimiter, @string, @start);     END;     RETURN; END; GO

CREATE FUNCTION [dbo].[ufn_utl_RemoveAllSpaces] (     @InputStr VARCHAR(8000) ) RETURNS VARCHAR(8000) AS BEGIN     DECLARE @ResultStr VARCHAR(8000);     SET @ResultStr = @InputStr;     WHILE CHARINDEX(' ', @ResultStr) > 0     SET @ResultStr = REPLACE(@InputStr, ' ', '');     RETURN @ResultStr; END; GO

CREATE FUNCTION [dbo].[ufn_utl_GetLocalDateTime] (     @StartDate DATETIME ) RETURNS DATETIME AS BEGIN     RETURN (DATEADD(                        HOUR,                        (CAST(DATEPART(HOUR, SYSDATETIME()) AS INT) - CAST(DATEPART(HOUR, SYSUTCDATETIME()) AS INT)),                        @StartDate                    )            ); END; GO

CREATE FUNCTION [dbo].[ufn_utl_GetDateToInteger] (     @Date DATETIME2 ) RETURNS INTEGER AS /* Author: Mihir Shah Modified Date: 03/07/2018 Purpose: Function to convert date to integer Example: select [dbo].[ufn_GetDateToInteger](StartTime) AS StartTimeInt from  [NDISBillCalc].[WorkSegments] Output: 20180310 20180311 */ BEGIN     RETURN (CAST(CONVERT(VARCHAR(8), @Date, 112) AS INTEGER)); END; GO

CREATE FUNCTION [dbo].[ufn_utl_GetDateToInteger] (     @Date DATETIME2 ) RETURNS INTEGER AS /* Author: Mihir Shah Modified Date: 03/07/2018 Purpose: Function to convert date to integer Example: select [dbo].[ufn_GetDateToInteger](StartTime) AS StartTimeInt from  [NDISBillCalc].[WorkSegments] Output: 20180310 20180311 */ BEGIN     RETURN (CAST(CONVERT(VARCHAR(8), @Date, 112) AS INTEGER)); END; GO

--get the difference between two datetimes in the format: 'h:m:s' CREATE FUNCTION [dbo].[ufn_utl_getDateDiff] (     @startDate DATETIME   , @endDate DATETIME ) RETURNS VARCHAR(10) -- ============================================= -- Author: Mihir Shah (UHC\mshah1) -- Create date: 4/09/2018 -- Purpose: Purpose of this function is calculate Date differance between 2 dates -- Usage: --DECLARE @StartDate DATETIME = '10/01/2012 08:40:18.000' --DECLARE @endDate DATETIME = '10/04/2012 09:52:48.000' --SELECT dbo.ufn_utl_getDateDiff(@startDate, @endDate) AS DateDifference -- Output: -- ============================================= AS BEGIN     DECLARE @seconds INT = DATEDIFF(s, CONVERT(DATETIME, @startDate), CONVERT(DATETIME, @endDate));     DECLARE @difference VARCHAR(10) = CONVERT(VARCHAR(4), @seconds / 3600) + ':'                                       + CONVERT(VARCHAR(2), @seconds % 3600 / 60) + ':'                        

CREATE FUNCTION [dbo].[ufn_utl_FixBadTime] (     @StrTime VARCHAR(10) ) RETURNS TIME AS BEGIN     DECLARE @TimeValue TIME           , @TimeR     REAL;     SET @TimeR = CONVERT(REAL, @StrTime);     IF LEN(@StrTime) > 1         AND @TimeR < 24     BEGIN         DECLARE @Hr CHAR(2);         DECLARE @Min CHAR(2);         SET @Hr = RIGHT('00' + CONVERT(VARCHAR(5), ROUND(@TimeR, 0, 1)), 2);         SET @Min = RIGHT('00' + CONVERT(VARCHAR(5), CONVERT(INT, (@TimeR - ROUND(@TimeR, 0, 1)) * 60)), 2);         SET @TimeValue = CONVERT(TIME, @Hr + ':' + @Min);     END;     ELSE     BEGIN         SET @TimeValue = NULL;     END;     RETURN (@TimeValue); END; GO

/*********************************************************************************** Calculates rostered rate types based on work segment This does not calculate rates. It's purely for breaking down the roster record. SELECT * FROM [dbo].[GetWorkSegments] ('B0271037793', 'grp-act-cen') SELECT r.* FROM nbc.Roster r WHERE DATEPART(hour, Start) < 8 AND DATEPART(hour, [End]) > 10 ***********************************************************************************/ CREATE FUNCTION [dbo].[GetWorkSegments] (     @WorkSegmentId VARCHAR(100) , @RosteredService VARCHAR(50) ) RETURNS @Totals TABLE ( RosteredServiceID VARCHAR(100)   , RosteredService VARCHAR(50)   , ShiftId INT   , RateDayType VARCHAR(20)   , WeekdayId INT   , ShortDay CHAR(3)   , [Weekday] VARCHAR(12)   , ShiftDateKey INT   , ShiftStart DATETIME2   , ShiftEnd DATETIME2   , MinutesInShift MONEY -- so we can multiply it by a rate  

CREATE FUNCTION [dbo].[ufn_utl_FixBadDate] (     @StringDate VARCHAR(12) ) RETURNS DATE AS BEGIN     DECLARE @DateValue DATE;     IF LEN(@StringDate) > 1     BEGIN         DECLARE @DayDate CHAR(2);         DECLARE @Month CHAR(2);         DECLARE @Year VARCHAR(4);         SET @DayDate = RIGHT('00' + ISNULL(SUBSTRING(@StringDate, 1, CHARINDEX('/', @StringDate) - 1), ''), 2);         DECLARE @monthYear VARCHAR(100) = SUBSTRING(@StringDate, CHARINDEX('/', @StringDate) + 1, 100);         SET @Month = RIGHT('00' + ISNULL(SUBSTRING(@monthYear, 1, CHARINDEX('/', @monthYear) - 1), ''), 2);         SET @Year = RIGHT('20' + ISNULL(SUBSTRING(@monthYear, CHARINDEX('/', @monthYear) + 1, 100), ''), 4);         SET @DateValue = CONVERT(DATE, @DayDate + '/' + @Month + '/' + @Year, 103);     END;     ELSE     BEGIN         SET @DateValue = NULL;     END;     RETURN (@DateVal

CREATE PROCEDURE [dbo].[usp_utl_WriteMerge1] (     @DstSchema VARCHAR(255)   , @DstTable VARCHAR(255) ) AS /* /*======================================================================================================================================= -- Author       :Mihir Shah -- Created      :04/07/2018 -- Purpose/Description    --  This stored procedure generates creation script for a stored procedure containing Merge statments to load data from source to dimension (destination). ========================================================================================================================================= Usage:        EXEC   [dbo].[usp_utl_WriteMerge1]                  @DstSchema = 'trn' , @DstTable = 'C_Client'; ==========================================================================================================================================*/ Notes:        - Add mapping information between source and destination in dbo