Business Days - SQL Server Forums

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)

Business Days

Author

Topic

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-06-20 : 02:36:26

dear gurus,

I want to get the working days between two days..
in a single query.

i will give the start_date '06-02-2008',end_date '06-13-2008' the result should be as below.

06-02-2008 Monday
06-03-2008 Tuesday
06-04-2008 Wednesday
06-05-2008 Thursday
06-06-2008 Friday
06-09-2008 Monday
06-10-2008 Tuesday
06-11-2008 Wednesday
06-12-2008 Thursday
06-13-2008 Friday

Thanks in advance.

cool...,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 02:41:29

[code]SET DATEFIRST 7
SET DATEFORMAT mdy
DECLARE @StartDate datetime,@EndDate datetime

SELECT @StartDate='06-02-2008',@EndDate='06-13-2008'

SELECT DATEADD(d,number,@StartDate)
FROM master..spt_values
WHERE type='p'
AND DATEADD(d,number,@StartDate)<=@EndDate
AND DATEPART(dw,DATEADD(d,number,@StartDate)) NOT IN (1,7)[/code]

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-06-20 : 02:44:51

I don't have links to post made by PESO and MVJ for similar kind of question. I have got copy of their solution which may help you. I am copying their solution. Hope it helps

--By Peso
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)

--BY MVJ
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)

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-06-20 : 02:53:44

Can u explain this query to me.
wat is the use of this table spt_values

cool...,

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-06-20 : 02:54:41

Hi visakh..,
Can u explain this query to me.
wat is the use of this table spt_values

cool...,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 14:33:23

quote:
Originally posted by dass05555

Hi visakh..,
Can u explain this query to me.
wat is the use of this table spt_values

cool...,

spt_values is a count table which is used internally by sql server for various operations. I've just used it to iterate and get dates for specified period. Also i've used datepart of dw which returns day of week and using it i've filter only cases where its not equal to 1 (Sunday) & 7 (Saturday) which will give all working days.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources