Need help with date subtract from 3 business days.

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)

Need help with date subtract from 3 business days.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-07-27 : 12:28:50

I need to subtract 3 business days. Exclude Sunday but include Sat.
ex: if today Monday 07/26/10 then count 22, 23, 24,
07/27/10 then count 26, 24, 23

SQL 2005.

Thank you in advance.

DECLARE @EnterDt DATETIME
SET @EnterDt = GETDATE()

SELECT *
FROM t
WHERE EnterDt > @EnterDt ??

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-27 : 13:03:13

try this:


declare @date smalldatetime
set @date = getdate()
declare @days int
set @days = 4

SELECT 
@date as date_var
,@days as days_back
,DATEPART(WEEKDAY, @date)  as date_number
,buss_week=case when  DATEPART(WEEKDAY, @date-@days) between 1 and 5 then 'bussines' else 'weekend' end
,bussines_day=case 
	when datepart(weekday, @date)-@days >= 0 then @date-@days
	when datepart(weekday, @date)-@days < 0 then @date-@days-2
	end

Sachin.Nand

2937 Posts

Posted - 2010-07-28 : 03:08:47

[code]
declare @dt as datetime
select @dt='26-july-2010'

;with datecte
as
(

select
dateadd(dd,number,datediff(dd,7,@dt))as date
from master.dbo.spt_values where type='p'
and dateadd(dd,number,datediff(dd,7,@dt))<@dt
and datename(weekday,dateadd(dd,number,datediff(dd,7,@dt)))<>'Sunday'
)

select date from datecte where date between dateadd(dd,-4,@dt) and @dt

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-07-28 : 12:16:51

quote:

Originally posted by Idera


declare @dt as datetime
select @dt='26-july-2010'

;with datecte
as
(

select 
	dateadd(dd,number,datediff(dd,7,@dt))as date
	from master.dbo.spt_values where type='p'
	and  dateadd(dd,number,datediff(dd,7,@dt))<@dt
	and datename(weekday,dateadd(dd,number,datediff(dd,7,@dt)))<>'Sunday'
)

select date from datecte where date between dateadd(dd,-4,@dt) and @dt

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Thank you for your codes everything is working fine up til assume today is Thur, Fri, and Sat then the results are not quite correct.
Below is the testing. Again, thank you.

declare @dt as datetime
select @dt= GETDATE()
SELECT @dt AS 'today'

;with datecte
as
(

select
dateadd(dd,number,datediff(dd,7,@dt))as date
from master.dbo.spt_values where type='p'
and dateadd(dd,number,datediff(dd,7,@dt))<@dt
--and datename(weekday,dateadd(dd,number,datediff(dd,7,@dt))) <>'Sunday'
)

select date from datecte where date between dateadd(dd,-5,@dt) and @dt

-- Testing for Thu, Fri and Sat are not correct result.

-- Result from a query.
today
-----------------------
2010-07-29 08:55:43.030

(1 row(s) affected)

date
-----------------------
2010-07-25 00:00:00.000
2010-07-26 00:00:00.000
2010-07-27 00:00:00.000
2010-07-28 00:00:00.000
2010-07-29 00:00:00.000

Everything is working O.K so far except assume today is Thur 07/29, Fri 07/30 and Sat 07/31 I want to return:

-- Desire results:

today: 07/29

date
-----------------------
2010-07-26 00:00:00.000
2010-07-27 00:00:00.000
2010-07-28 00:00:00.000
2010-07-29 00:00:00.000

-- Friday 07/30.

today
-----------------------
2010-07-30 08:57:05.340

date
-----------------------
2010-07-27 00:00:00.000
2010-07-28 00:00:00.000
2010-07-29 00:00:00.000
2010-07-30 00:00:00.000

07/31
today
-----------------------
2010-07-31 08:58:36.420

date
-----------------------
2010-07-28 00:00:00.000
2010-07-29 00:00:00.000
2010-07-30 00:00:00.000
2010-07-31 00:00:00.000

Sachin.Nand

2937 Posts

Posted - 2010-07-28 : 12:59:00

[code]

declare @dt as datetime
select @dt='31-july-2010'

;with datecte
as
(

select ROW_NUMBER()over(order by (select 1))rid,
dateadd(dd,number,datediff(dd,7,@dt))as date
from master.dbo.spt_values where type='p'
and dateadd(dd,number,datediff(dd,7,@dt))<=@dt
and datename(weekday,dateadd(dd,number,datediff(dd,7,@dt))) <>'Sunday'
)
,cte
as
(
select rid,date from datecte where date=@dt
)

select date from datecte where rid between(select rid-3 from cte)and (select rid from cte)

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-07-28 : 13:57:52

I suspect that eventually you will want to factor in holidays, too, as non-business days. I'll suggest that you create a Calendar table that has a column that defines if the day is a business day or not.

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources