Function Work Days of Month

Transact-SQL
1

Hi to all,
I'm learning SQL and need to create a funcition to count the work days of current month including saturdays and not considering the holidays Can You help Me please?.

thanks in advance

2 
select count(*) as days_minus_sunday
  from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
             ,(10),(11),(12),(13),(14),(15),(16),(17),(18),(19)
             ,(20),(21),(22),(23),(24),(25),(26),(27),(28),(29)
             ,(30)
       ) as cte(n)
 where datepart(month,dateadd(day,n,dateadd(month,datediff(month,0,current_timestamp),0)))=datepart(month,current_timestamp)
   and datepart(weekday,dateadd(day,n,dateadd(month,datediff(month,0,current_timestamp),0)))>1
;
1 Like
3

Thank You so much Bitsmed, it works great.

Kind Regards...

4

That code depends on a specific DATEFIRST setting. The code below doesn't:

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
)
SELECT SUM(CASE WHEN DATEDIFF(DAY, 0, DATEADD(DAY, t.number, FirstDayOfMonth)) % 7 < 6 THEN 1 ELSE 0 END) AS DayCount
FROM cteTally100 t
CROSS APPLY (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS FirstDayOfMonth
) AS alias1
WHERE t.number <= DATEDIFF(DAY, FirstDayOfMonth, DATEADD(MONTH, 1, FirstDayOfMonth)) - 1
5

Hi,

the code works perfect but I need to know how to add a new column with the remaining bussines days

Thanks a lot

select count(*) as days_minus_sunday
from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
,(10),(11),(12),(13),(14),(15),(16),(17),(18),(19)
,(20),(21),(22),(23),(24),(25),(26),(27),(28),(29)
,(30)
) as cte(n)
where datepart(month,dateadd(day,n,dateadd(month,datediff(month,0,current_timestamp),0)))=datepart(month,current_timestamp)
and datepart(weekday,dateadd(day,n,dateadd(month,datediff(month,0,current_timestamp),0)))>1

6

Please elaborate with samples

7

Hi Bitsmed,

I tried but it's not working, I'm receiving 16 as result when it must be 13

SELECT YearNrTY, MonthNrTY, WDaysLeft, Days_Minus_Sunday
FROM (SELECT YEAR(GETDATE()) AS YearNrTY, MONTH(GETDATE()) AS MonthNrTY, DAY(GETDATE()) AS WDaysLeft, COUNT(*) AS Days_Minus_Sunday
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31)) AS Cte(n)
WHERE DATEPART(MONTH, DATEADD(DAY, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))) <= DATEPART(MONTH, CURRENT_TIMESTAMP) AND DATEPART(WEEKDAY,
DATEADD(DAY, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))) > 1) AS A

8

it is May 16th here in West Coast USA. there are 16 days left including today May 16th. Why are you saying 13. What kind of calendar are you using?

Wait I think you want the working days left?

9

Hi yosiasz, I'need the remaining work days of the month including Saturdays, My frist day of the week is Sunday.

Thanks

10

Maybe this:

select count(*) as days_minus_sunday
  from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
             ,(10),(11),(12),(13),(14),(15),(16),(17),(18),(19)
             ,(20),(21),(22),(23),(24),(25),(26),(27),(28),(29)
             ,(30)
       ) as cte(n)
 where datepart(month,dateadd(day,n,dateadd(month,datediff(month,0,current_timestamp),0)))=datepart(month,current_timestamp)
   and datepart(day,,dateadd(day,n,dateadd(month,datediff(month,0,current_timestamp),0)))>=datepart(day,current_timestamp)
   and datepart(weekday,dateadd(day,n,dateadd(month,datediff(month,0,current_timestamp),0)))>1
;

Ps.: please have in mind the issue @ScottPletcher mentioned regarding datefirst.

11

Thank You Bitsmed, It's Works Great.

Kind Regards...