I'm having one holiday table based on that I need to return last working date.
Table_holiday
Id Date Text
1 2013-03-29 Good Friday
2 2013-05-01 Maharashtra day
3 2013-05-02 Holiday
When I execute my date_recursive function it should have to check and return me last_business date
For example
If I execute it on @date datetime = '2013-03-29' it should return me last working date '2013-03-28'as this is the last working date
any help m newbie in sql.
Please try:
DECLARE @Table_holiday as TABLE(id int, [date] datetime, [Text] nvarchar(50))
insert into @Table_holiday values(1, '2013-03-29', 'Good Friday')
insert into @Table_holiday values(2, '2013-05-01', 'Maharashtra day')
insert into @Table_holiday values(3, '2013-05-02', 'Holiday')
declare @date datetime
set @date ='2013-03-29'
;with T(dt) as
(
select @date union all
select T1.[date] from T inner join @Table_holiday T1 on T1.[date]=T.dt-1
)select min(dt)-1 from T
Assuming ANY date not in your holiday table is a working date, you can search backwards until you find a non-holiday.
;with cte(adate) as (
select @date
from table_holiday
where @date = Date
union all
select h.Date
from cte
join table_holiday h on dateadd(d,-1,cte.adate) = h.Date
)
select isnull((select dateadd(d,-1,min(adate)) from cte), @date);
T". His answer is technically better and starts from the day AFTER the last working day you're after. I assumed @date is the last working day, but goes backwards if it's a holiday. techdo assumes you want a working day before @date. select (case when DATEDIFF(day,GETDATE(),'2008-08-05')<2 Then "last
working date" Else "" END) from Table_holiday
Hope its helpful.