SQL function to calculate number of work days

Found this code that uses CTE on the internet and modified to my own needs:


CREATE FUNCTION uf_get_num_of_work_days (@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
DECLARE @workdays INT;


WITH DATE (Date1)
AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', @StartDate), '19000101')
UNION ALL
SELECT DATEADD(DAY, 1, Date1)
FROM DATE
WHERE Date1 < @EndDate
)
SELECT
@workdays = COUNT(*) --CONVERT(VARCHAR(15),d1.DATE1 ,110) as [Working Date], DATENAME(weekday, d1.Date1) [Working Day]
From
DATE d1
Where
(DATENAME(weekday, d1.Date1)) not in ('Saturday','Sunday')

RETURN @workdays
END

Script to de-normalize Parent - Child Hierarchy

Parent Child data hierarchies are great for storing hierarchical data, however it becomes a major pain when you need to convert hierarchy structure into a flat view (e.g. to be used in reporting or SSAS cubes). While SSAS provide a build in support for parent-child dimensions, it only allows for one parent attribute per dimension.

I've looked into various techniques to convert parent-child into flat table and found a tool designed by Jon Burchel specifically for normalizing SSAS parent-child dimensions. The original tool is available on codeplex at http://pcdimnaturalize.codeplex.com

Base on this, I came up with my own version - a SQL Script that creates a SQL Server view for each lookup table in the database that contains parent-child hierarchy and converts it into a flat hierarchy with up to 3 levels.

The script assumes that lookup tables have "lu_" prefix. And it detects parent-child structure by searching for a column that starts with name "parent_".


Declare @Sql varchar(8000)


Declare @SrcTableName varchar(250), @AttributeName varchar(250)


Declare Cur Cursor For
Select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like 'lu_%' and COLUMN_NAME like 'parent_%'
Open Cur


Fetch Next From Cur Into @SrcTableName


While @@FETCH_STATUS = 0
Begin


Set @AttributeName = SUBSTRING(@SrcTableName, 4, 250)


Set @Sql =
'
CREATE VIEW [dbo].[vw_naturalized_' + @SrcTableName + '] AS
WITH 
PCStructure(Level, [parent_' + @AttributeName + '_id], [' + @AttributeName + ' Name_KeyColumn], [' + @AttributeName + ' 01_KeyColumn], [' + @AttributeName + ' 02_KeyColumn], [' + @AttributeName + ' 03_KeyColumn])
AS
(
SELECT 
3 Level, [parent_' + @AttributeName + '_id], [' + @AttributeName + '_id], [' + @AttributeName + '_id] as [' + @AttributeName + ' 01_KeyColumn], [' + @AttributeName + '_id] as [' + @AttributeName + ' 02_KeyColumn], [' + @AttributeName + '_id] as [' + @AttributeName + ' 03_KeyColumn] 
FROM 
[dbo].[' + @SrcTableName + '] 
WHERE [parent_' + @AttributeName + '_id] IS NULL OR [parent_' + @AttributeName + '_id] = [' + @AttributeName + '_id] 

UNION ALL 

SELECT 
Level + 1, e.[parent_' + @AttributeName + '_id], e.[' + @AttributeName + '_id], 
CASE Level WHEN 2 THEN e.[' + @AttributeName + '_id] ELSE [' + @AttributeName + ' 01_KeyColumn] END AS [fetal_therapy 01_KeyColumn], 
CASE Level WHEN 2 THEN e.[' + @AttributeName + '_id] WHEN 3 THEN e.[' + @AttributeName + '_id] ELSE [' + @AttributeName + ' 02_KeyColumn] END AS [' + @AttributeName + ' 02_KeyColumn],
CASE Level WHEN 2 THEN e.[' + @AttributeName + '_id] WHEN 3 THEN e.[' + @AttributeName + '_id] WHEN 4 THEN e.[' + @AttributeName + '_id] ELSE [' + @AttributeName + ' 03_KeyColumn] END AS [' + @AttributeName + ' 03_KeyColumn] 
FROM [dbo].[' + @SrcTableName + '] e 
INNER JOIN PCStructure d ON e.[parent_' + @AttributeName + '_id] = d.[' + @AttributeName + ' Name_KeyColumn] AND e.[parent_' + @AttributeName + '_id] != e.[' + @AttributeName + '_id]
)


select 
Level4Subselect.*
from PCStructure a, 
(select [' + @AttributeName + '_id] [' + @AttributeName + ' 03_KeyColumn], [' + @AttributeName + '_name] [' + @AttributeName + ' 03_NameColumn], [' + @AttributeName + '_order_num] [' + @AttributeName + ' 03_' + @AttributeName + ' Order Num_KeyColumn], Level3Subselect.*
from [dbo].[' + @SrcTableName + '] b,
(select [' + @AttributeName + '_id] [' + @AttributeName + ' 02_KeyColumn], [' + @AttributeName + '_name] [' + @AttributeName + ' 02_NameColumn], [' + @AttributeName + '_order_num] [' + @AttributeName + ' 02_' + @AttributeName + ' Order Num_KeyColumn] , Level2Subselect.*
from [dbo].[' + @SrcTableName + '] b,
(select [' + @AttributeName + '_id] [' + @AttributeName + ' 01_KeyColumn], [' + @AttributeName + '_name] [' + @AttributeName + ' 01_NameColumn], [' + @AttributeName + '_order_num] [' + @AttributeName + ' 01_' + @AttributeName + ' Order Num_KeyColumn], CurrentMemberSubselect.* 
from [dbo].[' + @SrcTableName + '] b,
(select [' + @AttributeName + '_id] as [original_' + @AttributeName + '_id], [' + @AttributeName + '_name] [original_' + @AttributeName + '_name], [parent_' + @AttributeName + '_id] [original_parent_' + @AttributeName + '_id], [' + @AttributeName + '_order_num] [original_' + @AttributeName + '_order_num]
from [dbo].[' + @SrcTableName + '] b) CurrentMemberSubselect
) Level2Subselect
) Level3Subselect
) Level4Subselect
where
Level4Subselect.[' + @AttributeName + ' 03_KeyColumn] = a.[' + @AttributeName + ' 03_KeyColumn] and
Level4Subselect.[' + @AttributeName + ' 02_KeyColumn] = a.[' + @AttributeName + ' 02_KeyColumn] and
Level4Subselect.[' + @AttributeName + ' 01_KeyColumn] = a.[' + @AttributeName + ' 01_KeyColumn] and
Level4Subselect.[original_' + @AttributeName + '_id] = a.[' + @AttributeName + ' Name_KeyColumn]
GO
'
Print @Sql


Fetch Next From Cur Into @SrcTableName
end


close cur
deallocate cur 

First Blog

Hello there!

My name is Maxim Smirnov. I am an IT consultant working in Toronto, ON, Canada.

I specialize in Data Architecture, Database Design and Business Intelligence primarily focusing on products developed by Microsoft (MS SQL Server, SSAS - Analysis Services, SSRS - Reporting Services, Performance Point, etc).

You can find me on LinkedIn at http://www.linkedin.com/in/maximsmirnovtoronto

I am starting this blog to share tips and tricks I've learned on the job. Hope they'll help somebody same way other people blogs have helped me on many occasions.