Calulating working days without cosidering Holidays

 

/***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