## 30 day calculation

### 30 day calculation

I need to get 30 day from date which excludes weekends and holidays.

I have holiday table

holiday  year statdate enddate

xmas     02

--
Posted via http://dbforums.com

### 30 day calculation

I need to calculate 30day from given date, excluding weekends and
holidays.

I have holiday table

holiday     year     stardate    enddate

x-mas       03        12/24/03   12/25/03

i need to write a function to calculate the weekdays.

how do i do this.

Thanks.

--
Posted via http://dbforums.com

### 30 day calculation

write a function or proc to calculate this, i think you
know the first day, use the dateadd function and increment
each day, for each day, check whether is weekend (sat or
sun) or check is whether in holiday, when the day is not
weekend and not holiday increment a counter, and loop it.
once the counter is 30, you can get the 30 days from the
given date considering the weekend and holidays....

i used this in one of my applications.

sathya

Quote:>-----Original Message-----

>I need to calculate 30day from given date, excluding
weekends and
>holidays.

>I have holiday table

>holiday     year     stardate    enddate

>x-mas       03        12/24/03   12/25/03

>i need to write a function to calculate the weekdays.

>how do i do this.

>Thanks.

>--
>Posted via http://dbforums.com
>.

### 30 day calculation

I will try that.

--
Posted via http://dbforums.com

### 30 day calculation

You can do it by adding 30 and the weekends and holidays but that's
always a pain to get right so (not guaranteeing this is correct either)

begin

end

--
Posted via http://dbforums.com

### 30 day calculation

A calendar table might help:

DROP TABLE CALENDAR
CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')

Populate with as many years as you need:

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'20101231'
INSERT INTO Calendar (caldate)
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

Set the weekends and the holidays from your table:

UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')

UPDATE Calendar SET workingday = 'N'
WHERE EXISTS
(SELECT *
FROM Holidays
WHERE caldate
BETWEEN startdate AND enddate)

Here's the query for today plus 30 days:

SELECT C1.caldate
FROM Calendar AS C1
JOIN Calendar AS C2
ON C1.caldate>=C2.caldate

AND C1.workingday='Y'
AND C2.workingday='Y'
GROUP BY C1.caldate
HAVING COUNT(*)=30

--
David Portas
------------
--

### 30 day calculation

This will do it for you
Nav

returns  datetime
as
begin

begin
/* this only excludes weekends you
can use your look up table for this*/

begin
/* do not
increment the days if saturday or sund */

end
else
begin

+1
end

end

end

Quote:>-----Original Message-----

>I need to get 30 day from date which excludes weekends
and holidays.

>I have holiday table

>holiday  year statdate enddate

>xmas     02

>--
>Posted via http://dbforums.com
>.

### 30 day calculation

Thank you very much for the reply; I like David Portas idea of creating
the table.  I need to get 0-30 day, 31 -60 day and 61 to 90 and over 90
day.  How do I go about it?

Thank You.

--
Posted via http://dbforums.com

### 30 day calculation

To get a range (e.g. 0-30 days) you can use BETWEEN in the HAVING clause:

SELECT C1.caldate
FROM Calendar AS C1
JOIN Calendar AS C2
ON C1.caldate>=C2.caldate

AND C1.workingday='Y'
AND C2.workingday='Y'
GROUP BY C1.caldate
HAVING (COUNT(*)-1) BETWEEN 0 AND 30

Note that the value 120 should be greater than the largest range you require
+ the maximum number of non-working days possible within that range. The
purpose of that value is to improve the query performance by defining an
upper limit to the join.

--
David Portas
------------
--

Quote:

> Thank you very much for the reply; I like David Portas idea of creating
> the table.  I need to get 0-30 day, 31 -60 day and 61 to 90 and over 90
> day.  How do I go about it?

> Thank You.

> --
> Posted via http://dbforums.com