DateDiff or Between 2 Dates Need to Exclude Sundays 2

[cghoga]

Greetings,

I have a situation where I need to check if a call occurs between 2 dates. This is easy, the problem I am having is it needs to consider business days only which in my situation would exclude Sundays.

So if it was Monday morning and a call had occurred Saturday morning at the same time, it would not be considered 2 biz days until Tuesday morning that same time.

I am using SQL 2000.

Thanks for any and all help,

cgh

 

[RyanEK]

Hi,

Would this work for you?

declare @temp table (id int, adate datetime)
insert into @temp values (1, '2009-06-01')
insert into @temp values (1, '2009-06-02')
insert into @temp values (1, '2009-06-06') -- sat
insert into @temp values (1, '2009-06-07') -- sun
insert into @temp values (1, '2009-06-08') 
insert into @temp values (1, '2009-06-09') 
insert into @temp values (2, '2009-06-01') 
insert into @temp values (2, '2009-06-02') 
insert into @temp values (2, '2009-06-07') -- sat

select id, 
sum(case when DATENAME(dw,adate) in ('Saturday','Sunday') then 0 else 1 end) as count
from @temp
group by id

Ryan

 

[markros]

Can you post your current query and where exactly do you need help?

 

[cghoga]

Thanks guys,

Here is the basic code snipped prior to exclusion...

IF DATEDIFF ( d, calldatetime , getdate()) <= 2 Biz Days then delete from temp table

If a Sunday falls between the startdate and enddate I need to not count the Sunday.

Thanks again,

cgh

 

[PWise]

What about holidays other days off?

What id whould do is have a dates table

1/1/2000
1/2/2000
1/3/2000
.....
12/29/2xxx
12/30/2xxx
12/31/2xxx
for statrers

Select count(*),calldatetime 
from CallTable
inner join(
           Select Dayofyear
            from Daystable
            Where datepart(dw,Dayofyear) Between 2 and 6
            and holiday=0
            )Daystable
on CallTable.calldatetime Between Dayofyear and getdate()
Group by calldatetime

 

[cghoga]

Thanks for all the help, will take a look see.

Thanks,

cgh