More SQL love...

Dates and calculations:

/* This calculates the various day threshholds for document processing, given a 5-digit julian date. */

CREATE PROCEDURE [dbo].[sp_45daycalc]

/* This is the 5-digit Julian Date, supplied as an argument to the stored procedure. */

@jd int

AS

/* The actual calendar date from the calendar table, [nbdcheck], that the julian date corresponds to. */

declare @actdate datetime
set @actdate = (select caldate from nbdcheck where absdate = @jd)

/* Some documents must be processed in 45 calendar days, so just add that to the calendar date */

declare @doctype1 datetime
set @doctype1 = (select caldate from nbdcheck where caldate = (@actdate + 45))

/* to calculate business days out from a julian date, we create a table of business days only, starting with the beginning calendar date. That date is day 1, so the index field gives us a sort of 'relative' date. We go 75 days out at this point. If we need a larger business day range, then we can extend that as far as we need to.*/

create table ##nbdlist
(idx int IDENTITY (1,1) NOT NULL,
caldate datetime NOT NULL)

insert into ##nbdlist
select caldate from nbdcheck where
caldate >= @actdate and caldate <= (@actdate + 75) and (weekend = 0 and holiday = 0)

/* Some docs must be processed in 45 business days, so from ##nbdlist, get the 45th day. */

declare @doctype2 datetime
set @doctype2 = (select caldate from ##nbdlist where idx = 45)

/* More processing threshholds for various types of documents, and document processes. The next 4 are in calendar days, so it's just adding to the original calendar date. */

declare @corrrev datetime
set @corrrev = (select caldate from nbdcheck where caldate = (@actdate + 60))

declare @revack datetime
set @revack = (select caldate from nbdcheck where caldate = (@actdate + 21))

declare @revassn datetime
set @revassn = (select caldate from nbdcheck where caldate = (@actdate + 30))

declare @revcompl datetime
set @revcompl = (select caldate from nbdcheck where caldate = (@actdate + 120))

/* These are in business days, so we go again to the ##nbdlist table, and get the relative date. */

declare @prrs10 datetime
set @prrs10 = (select caldate from ##nbdlist where idx = 10)

declare @prrs20 datetime
set @prrs20 = (select caldate from ##nbdlist where idx = 20)

declare @prrs45 datetime
set @prrs45 = (select caldate from ##nbdlist where idx = 45)

/* Now we need something to pull from, to get this onto the php page, so we create a temporary table called ##datecalc, and populate it with each of the dates listed above. */

create table ##datecalc
(actdate datetime not null,
benedate datetime not null,
provdate datetime not null,
corrrev datetime not null,
revack datetime not null,
revassn datetime not null,
revcompl datetime not null,
prrs10 datetime not null,
prrs20 datetime not null,
prrs45 datetime not null
)

insert into ##datecalc
(actdate, doctype1, doctype2, corrrev, revack, revassn, revcompl, prrs10, prrs20, prrs45)
values(@actdate, @doctype1, @doctype2, @corrrev, @revack, @revassn, @revcompl, @prrs10, @prrs20, @prrs45)
GO