Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

count of non-holiday weekdays Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 6:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 7:21 AM
Points: 98, Visits: 251
Hi Expert,

I want to populate records in my one of the column as per below,

count of non-holidays i.e. weekdays from initial 'Submitted' status to 'Paid' status.


CRETE TABLE
--------------------------------

create table bbc 
(id int,st_date date,end_date date,status_id nvarchar(200))

INSERT INTO TABLE
---------------------------
insert into bbc values (5,'2012-10-08','2012-10-08','Paid')
insert into bbc values (4,'2012-10-06','2012-10-06','Approved: Ready for Pay')
insert into bbc values (3,'2012-10-03','2012-10-03','Submitted')
insert into bbc values (2,'2012-10-01','2012-10-01','Authorizd Required')
insert into bbc values (1,'2012-10-01','2012-10-01','Draft')
insert into bbc values (5,'2012-09-05','2012-09-06','Paid')
insert into bbc values (4,'2012-09-05','2012-09-05','Approved: Ready for Pay')
insert into bbc values (3,'2012-08-30','2012-08-30','Submitted')
insert into bbc values (2,'2012-08-29','2012-08-29','Authorizd Required')
insert into bbc values (1,'2012-08-29','2012-08-29','Draft')

go

So please how to do this?

Regards,
Kiran
Post #1378207
Posted Monday, October 29, 2012 6:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:47 PM
Points: 1,170, Visits: 1,211
One way to do this is to create a Calendar or Date table.

Take a look at: http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

HTH,
Rob
Post #1378209
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse