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

On Sat, 17 Dec 2005 23:14:16 -0000, Al Newbie wrote:

(snip)
Show quoteHide quoteHi 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)

Ordered Insert misleading syntax
GETDATE() to insert into a datetime field
what is wrong with this UDF?
How can I create a unique constraint on 2 columns where one of them allows nulls?
Determining financial year quarter