30 day calculation

30 day calculation

Post by 9jen » Wed, 10 Sep 2003 02:29:41



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

Post by 9jen » Wed, 10 Sep 2003 02:39:04


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

Post by sathy » Wed, 10 Sep 2003 03:32:46


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

Post by 9jen » Wed, 10 Sep 2003 03:41:27


I will try that.

--
Posted via http://dbforums.com

 
 
 

30 day calculation

Post by nigelrivet » Wed, 10 Sep 2003 03:35:37


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

Post by David Porta » Wed, 10 Sep 2003 08:57:25


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 DATEADD(D,DATEDIFF(D,'19991231',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
------------
Please reply only to the newsgroup
--

 
 
 

30 day calculation

Post by nav » Wed, 10 Sep 2003 13:53:02


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

Post by 9jen » Wed, 10 Sep 2003 15:59:21


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

Post by David Porta » Wed, 10 Sep 2003 19:14:46


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
------------
Please reply only to the newsgroup
--


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

 
 
 

30 day calculation

Post by 9jen » Thu, 11 Sep 2003 01:44:47


Thank you very much for your reply.

--
Posted via http://dbforums.com