I want to count # of workingg days for week

0 votes
asked Dec 28, 2015 in T-SQL by hetal (120 points)

I want to count # of working days for all the week in 2015 excluding Saturday and Sunday
but in each month I want to start the counting of working days from 1st and end at last day of month.

Suppose 5th week of March-15 only I want to write 2 days and 1st week of April -15
3 days.

I wrote query below just need little cleanup.

SELECT DateDiff (day,DateADD(day,-1,[WeekStart]),ISNULL([WeekEnd],getDate())) -
( CASE WHEN DATENAME(dw, [WeekStart]) = 'Sunday' OR
DATENAME(dw,ISNULL([WeekEnd],getDate())) = 'Sunday' THEN 1 ELSE 0 END)
- ( CASE WHEN DATENAME(dw, [WeekStart]) = 'Saturday' OR
DATENAME(dw,ISNULL([WeekEnd],getDate())) = 'Saturday' THEN 1 ELSE 0 END)
numberOfDays
FROM
my table

Please log in or register to answer this question.

Welcome to I Can Crack, where you can ask questions and receive answers from other members of the community.

68 questions

47 answers

3 comments

5,318 users

...