Finding the Business Days In a Quarter and Number them in SQL Server 2005/2008

I recently wrote a post about Find the WeekEnds in this Quarter or Year using SQL Server 2005/2008 . For the same report, a requirement was to pull the working days in the current Quarter and number them sequentially. The requirement also stated that the weekends should be numbered as 0 in the report. Here’s how the query works for this requirement

DECLARE @date1 datetime
DECLARE @date2 datetime
SELECT @date1 = DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)
SELECT @date2= DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1);

WITH CTE (dt) 
AS
(
SELECT @date1
UNION ALL
SELECT DATEADD(d,1,dt) FROM CTE
WHERE dt < @date2
)

SELECT cte.dt as BDate, COALESCE (dte.WorkingDay,'') as DayNumber from CTE cte
LEFT JOIN
(
SELECT dt as d, 
 ROW_NUMBER() OVER (PARTITION BY DATEDIFF(mm, '19000101', dt) ORDER BY dt) WorkingDay
 FROM CTE 
WHERE DATENAME(dw,dt) Not In ('Saturday', 'Sunday')
)
AS dte ON dte.d = cte.dt 
ORDER BY cte.dt

OUTPUT

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook