TSQL Function: Count Work Days from two dates.

A T-SQL function to calculate number of business days (work days) given two dates.
It uses CTE recursive to quickly get a row number count.

CREATE FUNCTION [hrr].[usf_GetWeekdayCount]
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
BEGIN

IF(@StartDate IS NULL)
  RETURN 1

IF(@EndDate IS NULL)
  RETURN 1

DECLARE @FlipBitFlag bit
SET @FlipBitFlag = 0

IF(@StartDate > @EndDate)
BEGIN   DECLARE @Temp datetime
   SET @Temp = @StartDate
   SET @StartDate = @EndDate
   SET @EndDate = @Temp
   SET @FlipBitFlag = 1
END
DECLARE @WEEKDAYS_DAY AS INT
 ---Count up to 2^16 = 65536 real fast!!
;WITH N1 (n) AS
(
  SELECT 1

  UNION ALL 
  SELECT 1
),
N2 (n) AS
(
   SELECT 1 FROM N1 AS X, N1 AS Y
),
N3 (n) AS
(
   SELECT 1 FROM N2 AS X, N2 AS Y
),
N4 (n) AS
(
   SELECT 1 FROM N3 AS X, N3 AS Y
),
N5 (n) AS
(
   SELECT ROW_NUMBER() OVER(ORDER BY X.n) FROM N4 AS X, N4 AS Y
)
  

SELECT @WEEKDAYS_DAY=SUM(1)
FROM N5
WHERE DATEADD(day,n-1,@startdate)<=@enddate
AND DATENAME(dw,DATEADD(day,n-1,@startdate)) NOT IN ('SATURDAY','SUNDAY')

IF(@FlipBitFlag = 1)
  RETURN -1 * @WEEKDAYS_DAY

RETURN @WEEKDAYS_DAY

END
GO

--EDITED: Added check for nulls and reverse dates will give negative value.

SSRS: Hide this column function

A bit of code from SQL Server Magazine on handling column specific security based on userid that is found in SSRS.

Function HideThisColumnFrom(strUserID as String) as Boolean
   Select Case strUserID
      Case "domain\user1" : Return False
      Case "domain\user2" : Return False
      Case Else : Return True
   End Select
End Function

Not ideal, and it is suggested using SQL Server to house a table of users, etc....

I think it would be best to write a sql scalar function that accepts a userid and based on permissions/roles send back either a bit-string or a string of roles separated by a delimiter which then can be deciphered by a function on the SSRS side. The function then would determine by sending in the roles into the function and return the True/False.



 

Multi-Processor - Parallel SQL does not work well with SCOPE_IDENTITY()

Just something to be aware of, that was brought up at work. When working with parallel processing, the SCOPE_IDENTITY() might bring back the wrong id. This is true for SQL Server 2008 R2 and lower. This is suppose to be fix for SQL Server 2012.

http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value

http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

Mean while, use the OUTPUT to get the newly inserted value, if need be.

OLD*******************************

 INSERT INTO [Database1].[dbo].[Table1]

([Field1]
,[LDTS]
,[RS])
VALUES(@Field1, @LDTS, @RS)

SET @FieldID = SCOPE_IDENTITY()

NEW******************************

Declare @GetID Table (FieldID smallint)

INSERT INTO [Database1].[dbo].[Table1]

([Field1]
,[LDTS]
,[RS])
OUTPUT Inserted.FieldID into @GetID      
VALUES(@Field1, @LDTS, @RS)

Select Top 1  @FieldID = FieldID from @GetID