/***Drop temp table if exisits***/
IF OBJECT_ID('tempdb..#CalculteWorkingDays') IS NOT NULL
DROP TABLE #CalculteWorkingDays
GO
/***Temp table for data***/
CREATE TABLE #CalculteWorkingDays
(
[CreatedDate] DATE,
[ClosedDate] DATE,
[ATA(days)] INT
)
/***Insert data to temp table***/
INSERT INTO #CalculteWorkingDays
( [CreatedDate], [ClosedDate] )
SELECT '12/01/2003', '12/31/2003' UNION ALL
SELECT '12/01/2004', '12/31/2004' UNION ALL
SELECT '12/01/2005', '12/31/2005' UNION ALL
SELECT '12/01/2006', '12/31/2006' UNION ALL
SELECT '12/01/2007', '12/31/2007' UNION ALL
SELECT '12/01/2008', '12/31/2008' UNION ALL
SELECT '12/01/2009', '12/31/2009' UNION ALL
SELECT '12/01/2010', '12/31/2010' UNION ALL
SELECT '12/01/2011', '12/31/2011' UNION ALL
SELECT '12/01/2012', '12/31/2012' UNION ALL
SELECT '12/01/2013', '12/31/2013' UNION ALL
SELECT '12/01/2014', '12/31/2014'
/***Now calculte the Working days***/
--DATEDIFF Returns the number of dd(day),wk(week) boundaries that are
--crossed between two specified dates.
--By Default Sunday is considered as 1st day of week and Saturday
--as Last day of week.
-- Calculate the working days and update it to [ATA(days)] column
-- Caluculate the days between dates . DATEDIFF + 1 Includes Both Dates
-- Exclude the no of Weeks crossed between dates * 2 (for Sat and Sun)
-- If start date falls on Sunday then exclude this from Working days caluculation
-- If end date falls on Saturday then exclude this from Working days caluculation
-- Exclude BankHolidays(Doesn't include here.We need to calcul;ate them eother by mainitng sepetare table for holidays)
UPDATE #CalculteWorkingDays
SET [ATA(days)] =(DATEDIFF(dd, [CreatedDate], [ClosedDate]) + 1)
-(DATEDIFF(wk, [CreatedDate], [ClosedDate]) * 2)
-(CASE WHEN DATENAME(dw, [CreatedDate]) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, [ClosedDate]) = 'Saturday' THEN 1 ELSE 0 END)
SELECT * FROM #CalculteWorkingDays
