Finding current day is a nth businees day

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)

Finding current day is a nth businees day

Author

Topic

sqldev6
Starting Member

18 Posts

Posted - 2008-06-10 : 06:33:06

Hi All,

I am trying to find the nth business day in a month and in a quarter.
But I am not able to find any solution for this.

Could you please any one provide the scripts for me.

Thanks in advance...........

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 06:34:19

What have you tried this far?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 06:42:53

[code]DECLARE @StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = '20080101',
@EndDate = '20081231'

;WITH Yak(theDate)
AS (
SELECT @StartDate

UNION ALL

SELECT DATEADD(DAY, 1, theDate)
FROM Yak
WHERE theDate < @EndDate
)

SELECT y.theDate,
w.WorkDate,
w.nthBusinessDayMonth,
w.nthBusinessDayQuarter
FROM Yak AS y
LEFT JOIN (
SELECT theDate AS WorkDate,
ROW_NUMBER() OVER (PARTITION BY DATEDIFF(MONTH, '19000101', theDate) ORDER BY theDate) AS nthBusinessDayMonth,
ROW_NUMBER() OVER (PARTITION BY DATEDIFF(QUARTER, '19000101', theDate) ORDER BY theDate) AS nthBusinessDayQuarter
FROM Yak
WHERE DATENAME(WEEKDAY, theDate) NOT IN ('Saturday', 'Sunday')
) AS w ON w.WorkDate = y.theDate
ORDER BY y.theDate
OPTION (MAXRECURSION 0)[/code]

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-10 : 09:44:34

You could use the date table function on the link below.

Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519


-- 9th day of month
select
	WD9M = max(a.[DATE])
from
	(
	select	top 9
		aa.[DATE]
	from
		F_TABLE_DATE('20080601','20080630') aa
	where
		--Monday to Friday
		aa.DAY_OF_WEEK between 2 and 6
	order by
		aa.[DATE]
	) a

-- 9th day of Quarter
select
	WD9Q = max(a.[DATE])
from
	(
	select	top 9
		aa.[DATE]
	from
		F_TABLE_DATE('20080401','20080630') aa
	where
		--Monday to Friday
		aa.DAY_OF_WEEK between 2 and 6
	order by
		aa.[DATE]
	) a


Results:
WD9M                                                   
-----------------------
2008-06-12 00:00:00.000

(1 row(s) affected)

WD9Q                                                   
-----------------------
2008-04-11 00:00:00.000

(1 row(s) affected)

CODO ERGO SUM

sqldev6
Starting Member

18 Posts

Posted - 2008-06-11 : 01:30:06

Thanks to all........ for your help

Hi peso, I got the soulution through your idea. thanks yar...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources