Thursday, August 30, 2012

Get only business days in a month in SQL SERVER

DECLARE @Start_Date DATETIME
SET @Start_Date = GETDATE()
SET @Start_Date = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Start_Date)-1),@Start_Date),101)

DECLARE @Holiday TABLE(HoliDay DATETIME)

INSERT INTO @Holiday(HoliDay) VALUES('2012-08-15')
 
;WITH MonthDays(day_num, calendar_dt) AS
     ( SELECT 1                                        AS day_num,
             DATEADD(d, -DAY(@Start_Date)+1, @Start_Date ) AS calendar_dt
     
     UNION ALL
     
     SELECT day_num + 1,
            DATEADD(d, 1, calendar_dt)
     FROM   MonthDays
     WHERE  MONTH(DATEADD(d, 1, calendar_dt)) = MONTH(@Start_Date)
     )
--SELECT * FROM MonthDays 

/*
SELECT *, datename(dw, calendar_dt), (DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, calendar_dt), 0), calendar_dt) +1) as [Week]
FROM MonthDays
WHERE ((DATEPART(dw, calendar_dt) + @@DATEFIRST) % 7) NOT IN (1)
-- 0 for saturday, 1 for sunday

--WHERE ((DATEPART(dw, calendar_dt) + @@DATEFIRST) % 7) NOT IN (0, 1)
--http://msdn.microsoft.com/en-us/library/ms174420(v=sql.90).aspx
*/

SELECT day_num    ,
       calendar_dt,
       WeekDayName,
       [Week]
FROM  ( SELECT day_num                                 ,
               calendar_dt                             ,
               DATENAME(dw, calendar_dt)                                                            AS WeekDayName,
               (DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, calendar_dt), 0), calendar_dt) +1) AS [Week]
       FROM    MonthDays
       WHERE   ((DATEPART(dw, calendar_dt) + @@DATEFIRST) % 7 ) NOT IN (1)
       ) AS T
WHERE  ( WeekDayName != 'Saturday' 
   OR     [Week] NOT IN (1, 3, 5)
       )
       --To remove the holiday
AND    day_num NOT IN
       (SELECT DATEPART(DD, HoliDay)
       FROM    @Holiday
       )

No comments: