T-SQL

How to get working days between two dates in SQL Server

Posted on Updated on

This post is about how to find the working day between Two Dates.

Consider the working days from Monday to Friday. We know that DATEDIFF function is used to calculate the Difference between two dates.

CREATE FUNCTION [dbo].fn_CountOfWorkingDays
( @StartDate DATETIME
  ,@EndDate  DATETIME
)
RETURNS  INT
AS
BEGIN
     DECLARE @CountOfWorkingDays INT
     SELECT @CountOfWorkingDays= ((DATEDIFF(dd, @StartDate, @EndDate) + 1) 
 -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
 -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
 -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END))
RETURN @CountOfWorkingDays
END

Output:

SELECT [dbo].[fn_CountOfWorkingDays] ( '2017-01-01','2017-01-31') CountOfWorkingDays 
GO
CountOfWorkingDays
22

In above function  DATEDIFF(dd,@StartDate,@EndDate ) + 1 gives us  the number of days between the two dates and  DATEDIFF(wk,@StartDate,@EndDate ) this gives the number of weeks between dates and * 2 gives us the weekend (Saturday and Sunday) count.CASE statement will excludes the day if it’s a Saturday or Sunday.

In case you want to calculate working days from Monday to Saturday then just remove last case statement and * 2 from  (DATEDIFF(wk, @StartDate, @EndDate) * 2)

ALTER FUNCTION [dbo].fn_CountOfWorkingDays
( @StartDate DATETIME, 
 @EndDate    DATETIME )
RETURNS  INT
AS
BEGIN
 DECLARE @CountOfWorkingDays INT
 SELECT @CountOfWorkingDays=
             ((DATEDIFF(dd, @StartDate, @EndDate) + 1)
 -(DATEDIFF(wk, @StartDate, @EndDate) )
 -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) )
RETURN @CountOfWorkingDays
END

Output:

SELECT [dbo].[fn_CountOfWorkingDays] ( '2017-02-01','2017-02-28') CountOfWorkingDays 
GO
CountOfWorkingDays
24

 

Advertisements

Concatenate Rows using FOR XML PATH()

Posted on Updated on

To concat multiple rows of data into single row we can use XML PATH(”).

SELECT ‘,’ + Column_Name … FOR XML PATH(”)

Generates a set of comma separated values, based on combining multiple rows of data from the Column_Name column. It will produce a value  ,abc,pqr,

STUFF(…,1,1,”)

Is then used to remove the leading comma that the previous trick generated, see STUFF for details about its parameters.

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

For example :

We will create a Temp table and insert some sample data.

DECLARE @ConcateTest TABLE(Id INT,CityName VARCHAR(20))

INSERT @ConcateTest(Id,CityName)

VALUES(1,’Los Angeles’),(1,’Austin’)

,(2,’Houston’),(2,’         Fort Worth’)

,(3,’New York’),(3,’Dallas’)

,(4,’Atlanta’),(4,’cape town’)

SELECT DISTINCT ID ,

STUFF((SELECT ‘,’+CityName

FROM @ConcateTest CT1

WHERE CT1.ID=CT2.ID

FOR XML PATH(”)),1,1,”) AS CityName FROM @ConcateTest CT2

ID CityName
1 Los Angeles,Austin
2 Houston,      Fort Worth
3 New York,Dallas
4 Atlanta,cape town