I have a table (tblJobs) that has a delivery date - to this date I want to
add a set amount of 'working' days
I have a table of dates with columns that specify whether the date is a
weekday and a holiday.

for each record in the tblJobs table I want the calculated date to appear in
the record.

Eg
tblJobs
JobNo DeliveryDate
G123 03/01/2006

tblHols
dt isWeekday isHoliday
01/01/2006 0 1
02/01/2006 1 1
03/01/2006 1 0
04/01/2006 1 0
05/01/2006 1 0
06/01/2006 1 0
07/01/2006 0 0
08/01/2006 0 0
09/01/2006 1 0
10/01/2006 1 1
11/01/2006 1 0

ResultSet
Job DeliveryDate CalcDate (i.e. DeliveryDate + 5
WorkingDays)
G123 03/01/2006 11/01/2006

I have the query to calculate the date but I don't know how to pass the
DeliveryDate for each record to the subquery

Select *,(SELECT c.dt
FROM dbo.tblHols c
WHERE
c.isWeekday = 1
AND c.isHoliday =0
AND c.dt > @dte
AND c.dt <= DATEADD(day, 25, @dte) = = = @dte needs to equal
DeliveryDate for each record
AND 5 = (
SELECT COUNT(*)
FROM dbo.tblHols c2
WHERE c2.dt >= @dte
AND c2.dt <= c.dt
AND c2.isWeekday=1
AND c2.isHoliday=0
)
) as CalcDate
FROM tblJobs


Here are the scripts to create the tables etc

CREATE TABLE [dbo].[tblHols] (
[dt] [datetime] NOT NULL ,
[isHoliday] [bit] NULL ,
[isWeekday] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblHols] ADD
CONSTRAINT [PK_tblHols] PRIMARY KEY CLUSTERED
(
[dt]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblJobs] (
[Job] [nvarchar] (8) COLLATE Latin1_General_BIN NOT NULL ,
[DeliveryDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblJobs] ADD
CONSTRAINT [PK_tblJobs] PRIMARY KEY CLUSTERED
(
[Job]
) ON [PRIMARY]
GO

SET NOCOUNT ON
DECLARE @dt SMALLDATETIME
SET @dt = '20060101'
WHILE @dt < '20070101'
BEGIN
INSERT dbo.tblHols(dt) SELECT @dt
SET @dt = @dt + 1
END

UPDATE dbo.tblHols SET

isWeekday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,

isHoliday = 0

UPDATE tblHols
SET
isHoliday = 1
WHERE datepart(d,dt) IN (1,2,10)

INSERT INTO tblJobs ( Job, DeliveryDate )
SELECT 'G1234' AS Expr1, '20060102' AS Expr2

INSERT INTO tblJobs ( Job, DeliveryDate )
SELECT 'G2234' AS Expr1, '20060105' AS Expr2

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi Al,

Not sure if you still need this after my previous reply, but you can
simply replace "@dte" with "tblJobs.DeliveryDate".

Best, Hugo
--

i think it is better to be later than to be never :)

SET NOCOUNT ON;
SET ANSI_NULLS ON;
USE YOUR_DB;

IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='Jobs') DROP TABLE Jobs;

IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='Calendar') DROP TABLE Calendar;

CREATE TABLE Jobs(
job_id CHAR(4) NOT NULL PRIMARY KEY,
dlvr_dt DATETIME NOT NULL);

INSERT INTO Jobs VALUES('G123', '2006-01-03');
INSERT INTO Jobs VALUES('G234', '2005-01-01');

CREATE TABLE Calendar( -- Hols(
cal_dt DATETIME NOT NULL PRIMARY KEY,
is_wday INTEGER NOT NULL CHECK(is_wday IN(0,1)),
is_hday INTEGER NOT NULL CHECK(is_hday IN(0,1)))

INSERT INTO Calendar
SELECT '2006-01-01', 0, 1 UNION ALL
SELECT '2006-01-02', 1, 1 UNION ALL
SELECT '2006-01-03', 1, 0 UNION ALL
SELECT '2006-01-04', 1, 0 UNION ALL
SELECT '2006-01-05', 1, 0 UNION ALL
SELECT '2006-01-06', 1, 0 UNION ALL
SELECT '2006-01-07', 0, 0 UNION ALL
SELECT '2006-01-08', 0, 0 UNION ALL
SELECT '2006-01-09', 1, 0 UNION ALL
SELECT '2006-01-10', 1, 1 UNION ALL
SELECT '2006-01-11', 1, 0;

SELECT J.job_id, J.dlvr_dt, C.cal_dt
FROM Jobs as J, Calendar as C
WHERE C.cal_dt >= J.dlvr_dt
AND C.is_wday = 1 AND C.is_hday = 0
AND 6 = (SELECT COUNT(*)
FROM Calendar as C2
WHERE C2.cal_dt between J.dlvr_dt AND C.cal_dt
AND C2.is_wday = 1 AND C2.is_hday = 0)