SQL Query - 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)

SQL Query

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2007-05-29 : 07:12:54

declare @start datetime,@end datetime
select @start='1/2/2007',@end='1/31/2007'
select datediff(dd,@start,@end)-count(*) as weekdays from weekendsandholidays where dayofweekdate between @start and @end

ex;u take two dates 1/2/2007,1/31/2007

note:actually diff between 1/2/2007 and 1/31/2007 is 31

now u run above query then we can get answer count of remaining dates as 29

but i dont want the count 29

I want remaining days list one after one

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 07:16:49

not clear.. please post some sample data and output u want

vmurali
Yak Posting Veteran

88 Posts

Posted - 2007-05-29 : 07:29:37

If there are 2 dates 1/2/2007,1/31/2007
The dates in holidays list are 1/5/2007,1/15/2007
Now actually diff between 1/2/2007 and 1/31/2007 is 31

Now when I run my query
select datediff(dd,@start,@end)-count(*) as weeksays from weekendsandholidays where
dayofweekdate between @start and @end

I get the count as 29.

But I want the result as
1/3/2007
1/4/2007
1/6/2007
.
.
.
.
1/31/2007
excluding the 1/5/2007,1/15/2007

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-29 : 07:36:38

quote:
Originally posted by vmurali

If there are 2 dates 1/2/2007,1/31/2007
The dates in holidays list are 1/5/2007,1/15/2007
Now actually diff between 1/2/2007 and 1/31/2007 is 31

Now when I run my query
select datediff(dd,@start,@end)-count(*) as weeksays from weekendsandholidays where
dayofweekdate between @start and @end

I get the count as 29.

But I want the result as
1/3/2007
1/4/2007
1/6/2007
.
.
.
.
1/31/2007
excluding the 1/5/2007,1/15/2007

select datediff(dd, '01/02/2007', '01/31/2007') as diff

o/p

diff
29

Mahesh

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-29 : 07:42:13

to exclude the holidays try

select datediff(dd,@start,@end)-count(*) as weeksays from weekendsandholidays where
dayofweekdate between @start and @end
and dayofweekdate not in (<holiday list>)

thanks,

Mahesh

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 07:45:55

this just a sample u change it however u want

declare @start datetime, @end datetime
declare @weekend table(dt datetime)
Insert @weekend
Select '01/05/2007' union all
select '01/15/2007'
Set @start = '01/02/2007'
Set @end = '01/31/2007'

Select * from
(select date = dateadd(day,number,@start) from master..spt_values
where name is null and type='P' and number > 0 and
number <= (datepart(day, @end) - datepart(day,@start))) as b
where date not in (select dt from @weekend)

vmurali
Yak Posting Veteran

88 Posts

Posted - 2007-05-29 : 08:26:39

Actually I want to exclude in final output of the query
My output should be like :

1/2/2007
1/3/2007
1/4/2007
1/6/2007
.
.
.
.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 08:29:46

did u try what i posted..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources