Find the nonholiday end of the month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find the nonholiday end of the month

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2009-12-09 : 18:50:13

Find the nonholiday end of the month.

last day of any month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

but how to calculate the nonholiday last day of the month.
so it has to skip weekends AND holidays ( select holiday_date from Holidays)

Chandragupta Mourya

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-12-09 : 19:09:03

Not to appear snarky but...


select max(DayOfMonth)
from Calendar
where
    CalYear = Year(GetDate())
and CalMonth = Month(GetDate())
and Holiday = 0

Having a Calendar table that holds this information is very helpful...frequently.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

konark
Yak Posting Veteran

60 Posts

Posted - 2009-12-10 : 13:20:19

if we dont have calandar table, then ?

Chandragupta Mourya

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-10 : 13:48:33

Then create one - you won't regret it. Plenty of examples online. e.g.

http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

konark
Yak Posting Veteran

60 Posts

Posted - 2009-12-14 : 12:30:10

already There is a holiday table, in use. So we cant afford a new calandar table for only one use.

Chandragupta Mourya

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-14 : 12:55:42

calendar tables is all inclusive. holidays non holidays etc. it is worth investing in, Ryan is right you will not regret it. why do you have only a holiday table, now you are paying for that bad decision in that you are looking for a non holiday value.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-14 : 13:02:00

If you think there'll be only one use, you're not seeing the potential of a calendar table.

To easy yourself in gently, you could use a generic calendar/date table function, and then join it with your holiday table.

declare @Holidays table (holiday_date datetime)
insert @Holidays
          select '20091225'
union all select '20091231'

--F_TABLE_DATE: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
select YEAR_MONTH, max(DATE) as LastWorkingDayOfMonth
from dbo.F_TABLE_DATE('20060101', '20101231')
where date not in (select holiday_date from @Holidays)
group by YEAR_MONTH

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

konark
Yak Posting Veteran

60 Posts

Posted - 2009-12-14 : 13:30:52

I have a a function dbo.calcnextbusday( date, noofdaysto add) to calculate Next businessday .
Can I use this function to calculate the last nonholiday business day of the month

Chandragupta Mourya

konark
Yak Posting Veteran

60 Posts

Posted - 2009-12-14 : 14:49:28

CREATE FUNCTION dbo.fnEndOfMonth()
RETURNS DATETIME
AS
BEGIN
DECLARE @n INT
DECLARE @EndOfMonth Datetime
SET @n=-1
SET @EndOfMonth=DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

WHILE ((DATENAME(dw, @EndOfMonth) in ('Saturday','Sunday'))
OR convert( varchar,@EndOfMonth, 112)
in (SELECT convert( varchar ,holiday_date,112) FROM dbo.Holidays_TABLE))
BEGIN
SET @n=@n-1
SET @EndOfMonth=DATEADD(d,@n,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
END
RETURN @EndOfMonth
END

Chandragupta Mourya

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-14 : 15:10:23

quote:
Originally posted by konark

already There is a holiday table, in use. So we cant afford a new calandar table for only one use.

Chandragupta Mourya

What "Holidays" do you wish to look for

I mean, damn, these questions are so simple

If you where a Nazi for example....ummmm never mind

Or Dick Chenney...wait, that would be the same thing...

I'm Sorry

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-15 : 05:11:50

quote:
calculate the last nonholiday business day of the month

declare @Holidays table (holiday_date datetime)
insert @Holidays
          select '20091225'
union all select '20091231'

--F_TABLE_DATE: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
select YEAR_MONTH, max(DATE) as LastWorkingDayOfMonth
from dbo.F_TABLE_DATE('20060101', '20101231')
where date not in (select holiday_date from @Holidays)
  and WEEKDAY_NAME not in ('Sat', 'Sun')
group by YEAR_MONTH

See how easy it is?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources