January 2010 - Posts

Find alternate week day

My co-worker had a task of finding minimum alternate week day as of today for a given date and day

Consider these data

Date : 2009-12-22

day  : 5 (it denotes Thursday)

It means the event was scheduled on 2009-12-22 which should run on every alternate Thursday starting from 2009-12-24 (The first Thursday on or after the scheduled date).

The task is finding the minimum of current available day

Here is my solution

declare @date datetime, @day int

select @date='2009-12-22',@day=5

select
       
min(dateadd(day,14*(number),date))
from 
       
(
               
select dateadd(day,@day-datepart(weekday,@date),@date) as date
       
) as t,master..spt_values
where type='p' and dateadd(day,14*(number),date)>=getdate()

 

Posted by Madhivanan with no comments
Filed under: , ,

Replace data of one table with data of other table

One of the questions asked in a forum was about replacing data of one table by the data of another table
The solution that the questioner used was having cursor by looping through the source table and replace particular words by other words from another table by matching the words

Here is my alternate solution

declare @Datatable table(Field1 nvarchar(50))

insert into @Datatable (Field1)
select 'testing for this' union all
select
'testing for this to'

 

declare @MatchTable table(LookFor nvarchar(50),Replacewith nvarchar(50))
insert into @MatchTable (LookFor,Replacewith)
select 'for','with' union all
select
'to','no'

declare @replace varchar(8000),@with varchar(8000),@sql varchar(8000)

select @replace='',@with ='',@sql=''

select
        @replace
=@replace +'replace(',
        @with =@with +''''+Lookfor +''','''+Replacewith+'''),'
from 
        @MatchTable

select @replace=@replace +'''~!@#'',',@with =SUBSTRING(@with,1,len(@with)-1)

select @sql=@sql+REPLACE('select '+@replace+@with,'~!@#',Field1)+' union all ' from @Datatable

select @sql=SUBSTRING(@sql,1,len(@sql)-10)

select * from @Datatable

exec(@sql)

 

Posted by Madhivanan with no comments
Filed under: , ,