quote:

---

Originally posted by Sanjay_Bakshi

Dear All,
I have one table
Session-DaysRequired-SessionStartDate
1-2-07/14/2008
total 100 session i have

now i have to update Sesison Start Date in rest 99 line based on session start date with dateadd(dd,DaysRequired,SessionStartDate)
if value is Sunday then add 1 if value is Saturday then add2.

How to do this thing in SQL 2000



Sanjay

---

CREATE FUNCTION BusinessDayAdd
(
@DateValue datetime,
@count int
)
RETURNS datetime 
AS
BEGIN
DECLARE @ReturnDate datetime
SELECT @ReturnDate=DATEADD(d,Holiday,MAX(DateValue))
FROM
(SELECT DATEADD(d,number,@DateValue) AS DateValue,
CASE WHEN DATENAME(dw,DATEADD(d,number,@DateValue))='Sunday'
OR DATENAME(dw,DATEADD(d,number,@DateValue))='Saturday' 
THEN 1 ELSE 0 END AS Holiday
FROM master..spt_values
WHERE type='p'
AND number>0
AND number<=@count)t

RETURN @ReturnDate
END

DECLARE @InitialSessionDate datetime
SELECT @InitialSessionDate=SessionDate
FROM Yourtable
WHERE Session=1

UPDATE y
SET y.SessionStartDate=tmp.SessionStartDate
FROM yourtable y
INNER JOIN
(
SELECT Session,dbo.BusinessDayAdd(@InitialSessionDate,b.daycount)AS SessionStartDate
FROM yourtable t
CROSS APPLY (SELECT SUM(DaysRequired) AS daycount
             FROM yourtable t
             WHERE Session <t.Session) b
)tmp
ON tmp.Session=y.Session

quote:

---

Originally posted by khtan

Visakh, Sanjay is using SQL 2000

---

KH
[spoiler]Time is always against us[/spoiler]

---

CREATE FUNCTION BusinessDayAdd
(
@DateValue datetime,
@count int
)
RETURNS datetime 
AS
BEGIN
DECLARE @ReturnDate datetime
SELECT @ReturnDate=DATEADD(d,SUM(Holiday),MAX(DateValue))
FROM
(SELECT DATEADD(d,number,@DateValue) AS DateValue,
CASE WHEN DATENAME(dw,DATEADD(d,number,@DateValue))='Sunday'
OR DATENAME(dw,DATEADD(d,number,@DateValue))='Saturday' 
THEN 1 ELSE 0 END AS Holiday
FROM master..spt_values
WHERE type='p'
AND number>0
AND number<=@count)t

RETURN @ReturnDate
END




and use it in your query below

DECLARE @InitialSessionDate datetime
SELECT @InitialSessionDate=SessionDate
FROM Yourtable
WHERE Session=1

UPDATE y
SET y.SessionStartDate=tmp.SessionStartDate
FROM yourtable y
INNER JOIN
(
SELECT t.Session,dbo.BusinessDayAdd(@InitialSessionDate,SUM(b.DaysRequired))AS SessionStartDate
FROM yourtable t
INNER JOIN yourtable b
ON b.Session <t.Session
WHERE t.Session>1
GROUP BY t.Session
)tmp
ON tmp.Session=y.Session