Number of working days

Question

Post reply

Number of working days

weston_086

SSCommitted

Points: 1645
More actions
March 6, 2012 at 12:14 pm

#252041

i am just updating this topic.bcz i was not able to explain my problem earlier.i hope this might better explain my problem.
create table wrkdate(date datetime,
workday nvarchar(4),
NoWorkDaysInWeek nvarchar(10),
NoWorkdaysinMonth nvarchar(10),
NoWorkdaysInQtr nvarchar(10),
Noofworkdaysinyr nvarchar(10)
)
Insert into wrkdate(date,workday) values('2012-03-04 ','N'),
('2012-03-05','Y'),
('2012-03-05','y'),
('2012-03-06','y'),
('2012-03-07','y'),
('2012-03-08','Y'),
('2012-03-09','Y'),
('2012-03-10','N'),
('2012-03-11','N'),
('2012-03-12','Y'),
('2012-03-13','Y')
select * from wrkdate
/* In main table i have date column which has 1000 of dates from 2005 -today's date,
workday column has 'Y'and 'N' which means if DATE is working day then it is 'Y' else'N'
even we can see that 2012-03-04 is sunday that's why it is 'N' and for next 5 days it is 'y'
Now i want to calculate NoWorkDaysinWeek means where workday='Y' and similarly all other column based on where workday='Y'
bcz it will give me workdays in week,month,Qtr,year
*/
insert into wrkdate values('2012-03-04 ','N',5,22,62,252),
('2012-03-05','Y',5,22,62,252),
('2012-03-05','y',5,22,62,252),
('2012-03-06','y',5,22,62,252),
('2012-03-07','y',5,22,62,252),
('2012-03-08','Y',5,22,62,252),
('2012-03-09','Y',5,22,62,252),
('2012-03-10','N',5,22,62,252),
('2012-03-11','N',5,22,62,252),
('2012-03-12','Y',5,22,62,252),
('2012-03-13','Y',5,22,62,252)
select * from wrkdate

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

here is one article that may give you ideas

http://www.sqlservercentral.com/Forums/Topic153606-203-1.aspx

read the discussion that follows (link is at bottom of article)

here is one method for working days in the month

SELECT DATE,

Datediff(dd, Dateadd(mm, Datediff(mm, 0, DATE), 0), Dateadd(mm, Datediff(mm, 0, DATE) + 1, 0) - 1 + 1) -

Datediff(wk, Dateadd(mm, Datediff(mm, 0, DATE), 0) - 1, Dateadd(mm, Datediff(mm, 0, DATE) + 1, 0) - 1) -

Datediff(wk, Dateadd(mm, Datediff(mm, 0, DATE), 0), Dateadd(mm, Datediff(mm, 0, DATE) + 1, 0) - 1 + 1) as workingdaysinmonth

FROM wrkdate

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

weston_086 SSCommitted Points: 1645 More actions · Answer 2

thanks this give me the result .but i donot want by this way .as i want based on workday(where workday='y').bcz i have another columns like workday_international which are all based on workday_intl.that's why i will how to count noofworkdaysweek,month,year based on where workday='y' then i can calculate those too.so please try to give solution based on workday

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

weston_086 (3/8/2012)
thanks this give me the result .but i donot want by this way .as i want based on workday(where workday='y').bcz i have another columns like workday_international which are all based on workday_intl.that's why i will how to count noofworkdaysweek,month,year based on where workday='y' then i can calculate those too.so please try to give solution based on workday

What day of the week does your "week" start on?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)