how can I put this into one query

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
wday 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 isWday 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.isWday = 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.isWday=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 ,
[isWday] [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
isWday = 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 Expr2On Sat, 17 Dec 2005 23:14:16 -0000, Al Newbie wrote:
(snip)
>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.isWday = 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.isWday=1
> AND c2.isHoliday=0
> )
> ) as CalcDate
>FROM tblJobs
Hi Al,
Not sure if you still need this after my previous reply, but you can
simply replace "@.dte" with "tblJobs.DeliveryDate".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||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)