May 19, 2015 at 4:56 am
Kindly help to solve below issue
Create table #table (id int identity , from_country varchar(20) ,
to_country varchar(20),noofdays int, datetravel datetime )
insert into #table(from_country,to_country,noofdays,datetravel)
values
('Malaysia','India',2,getdate()-99),
('India','Singapore',4,getdate()-88),
('Singapore','China',5,getdate()-77),
('China','Japan',6,getdate()-66),
('Japan','USA',7,getdate()-55)
select * from #table
I want to insert data to another table based on "noofdays" columns .
If "noofdays" is 4 then 4 rows should inserted to new table with 1 day increment in "datetravel" column.
Ex :
#table
1MalaysiaIndia22015-02-09 02:04:09.247
2IndiaSingapore42015-02-20 02:04:09.247
#table_new
1MalaysiaIndia22015-02-09 02:04:09.247
2MalaysiaIndia22015-02-10 02:04:09.247
3IndiaSingapore42015-02-20 02:04:09.247
4IndiaSingapore42015-02-21 02:04:09.247
5IndiaSingapore42015-02-22 02:04:09.247
6IndiaSingapore42015-02-23 02:04:09.247
In #table , 1st row noofdays is 2 ,
so in new table #table_new first 2 rows should inserted with 1 day increment in "datetravel" column
Thanks in advance
May 19, 2015 at 6:37 am
Have a read of this article from this very site: http://www.sqlservercentral.com/articles/Cursor+vs+Recursive+CTE/99795/
It has the answers that you seek.
May 19, 2015 at 7:17 am
Use a Tally table (there is an article on this site by Jeff Moden)
SELECT ROW_NUMBER() OVER (ORDER BY a.id,t.N),
a.from_country,a.to_country,a.noofdays,DATEADD(day,t.N-1,a.datetravel)
FROM #table a
JOIN dbo.Tally t ON t.N BETWEEN 1 AND a.noofdays
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply