quote:

---

Originally posted by steve4134

Hey Visakh

Thank you for your reply.Currently I am using the following stored procedure to create a temp table to come up with my dates used. Following is my code.

USE [Vision_Prod]
GO
/****** Object: StoredProcedure [dbo].[GetDateRange] Script Date: 10/19/2010 14:21:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetDateRange]
@StartDate datetime,
@EndDate datetime,
@Inclusive bit = 1
as

set nocount on;

declare @DayDiff smallint;
set @DayDiff = datediff(dd, @StartDate, @EndDate);
if @Inclusive = 0
set @DayDiff = @DayDiff - 1;

with cteRange (DateRange,Holiday)
as (
select dateadd(dd, datediff(dd, 0, @EndDate) - @DayDiff, 0),CASE WHEN DATENAME(dw,dateadd(dd, datediff(dd, 0, @EndDate) - @DayDiff, 0)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END
union all
select dateadd(dd, 1, DateRange),CASE WHEN DATENAME(dw,dateadd(dd, 1, DateRange)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END
from cteRange
where dateadd(dd, 1, DateRange) < (@EndDate + @Inclusive)
)
select DateRange,Holiday
INTO ##salesdays
from cteRange
option (maxrecursion 3660);

==================================================
I then run the following off the temp table I created to come up with my the data I have shown in the picture above.


SELECT DateRange FROM ##salesdays

EXCEPT
SELECT date FROM
(
SELECT date FROM dbo.csta_non_working_days
UNION
SELECT DateRange FROM ##salesdays WHERE Holiday=1
)t

=================================================================================================================================================


Is there any way to apply what you have shown me in your blog to my current stored procedure ? Thank you again.

Steve

---