I am fairly new to coding TSQL script and am looking for some second view on my script.
The goal here is to to pull in some data and amongst that data to show a field with a counting the number of working days since the order date only (i.e. minus weekend days and UK bank holidays). The
SELECT script below is working as expected in that it shows results and is therefore calling the
fn_WorkDays function. What would be helpful is to have a second glance of the function itself as a double check/review. The last part of the function is looking at a date field of a table that contains all of the bank holidays.
I think the script is working okay as I have checked a few going back to January, so I'm looking to get the double check to highlight any unforeseen potential errors or even code improvements.
SELECT cir.[PW Number] AS PWNum --Get the correct number of working dayts since the order date by using the fn_WorkDays function ,CONVERT(VARCHAR(10),singleended2.dbo.fn_WorkDays(cir.[Install Date], GETDATE()),103) AS NumberOfDaysSinceOrderDate ,CONVERT(VARCHAR(10), ISNULL(cir.[Install Date],'01/01/1900'),103) AS OrderDate--Get the order dates ,ISNULL(cirRep.CurrentStage, 'Not Set') AS CurrentStage ,cir.[ID] as CircuitID FROM Quotebase.dbo.Circuits cir LEFT JOIN Quotebase.dbo.CircuitReports cirRep ON Cir.[PW Number] = CirRep.PWNumber WHERE Cir.Status='New Circuit Order' ORDER BY Cir.[PW Number]
Here is the function script that is called:
ALTER FUNCTION dbo.fn_WorkDays (@StartDate AS DATETIME, @EndDate AS DATETIME) --Define the output data type. RETURNS INT AS --Calculate the RETURN of the function. BEGIN RETURN ( SELECT (DATEDIFF(dd,@StartDate, @EndDate)+1)--Start with total number of days including weekends +1 Includes the day run -(DATEDIFF(wk,@StartDate, @EndDate)*2)--Subtact 2 days for each full weekend -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' --If StartDate is a Sunday, Subtract 1 THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'--If EndDate is a Saturday, Subtract 1 THEN 1 ELSE 0 END) --Now check if there are any bank holidays between the start and end and remove that amount - (SELECT COUNT(ivm.[Date]) FROM [EUROPEVUK386].[InvoiceManagement].[dbo].[tblBankHolidays] ivm WHERE ivm.[Date] BETWEEN @StartDate AND @EndDate) --WHERE ivm.[Date] BETWEEN '2006-04-14' AND '2006-05-29') ) END GO