count of non-holiday weekdays

• kiran.rajenimbalkar

  SSCrazy

  Points: 2603

  More actions

  October 29, 2012 at 6:02 am

  #270215

  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

• robert.gerald.taylor

  SSChampion

  Points: 13133

  More actions

  October 29, 2012 at 6:08 am

  #1553689

  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