Hi,
I had tried ur doubt reg date Interval.
Follwing are the steps.
Step 1 : Create Table to Store Output,and Insert your First date that is start date.
Create Table #Test
(
Pk_Id Int Identity(1,1),
MyDate DateTime
)
Insert Into #Test
Values ('2012-11-01')
Step 2 : Create a Table For HolidayMaster Where you can Insert all the Dates Which has Holiday.
Create Table #HolidayMaster
(
Holiday DateTime
)
Insert Into #HolidayMaster
Values ('2012-11-05'),('2012-11-15'),('2012-11-23'),('2012-11-27')
Select * From #HolidayMaster
Step 3 : Just Execute the following command as a whole,you will get list of your required dates.
Assign the @StartDate And @EndDate
Declare @StartDate As Datetime = '2012-11-01'
Declare @EndDate As Datetime = '2012-11-30'
While (@StartDate < @EndDate)
Begin
If @StartDate = (Select Holiday From #HolidayMaster Where Holiday = @StartDate)
Begin
Set @StartDate = DateAdd(dd,1,@StartDate)
End
Else
Begin
Set @StartDate = DateAdd(dd,2,@StartDate)
End
If @StartDate = (Select Holiday From #HolidayMaster Where Holiday = @StartDate)
Begin
Select 'Holiday'
End
Else
Begin
Insert Into #Test
Select @StartDate
End
End
Step 4 : Check Table #Test for Output.
You can further modified it accordingly.