0

Good Day Everyone..

i have two tables..how to read from one table1 to table2..
example..
Table1 with thousand over records various types of recurrance and due pattern.


where ID = History_ID

Table 1
ID Reccurance Duepattern
1 Monthly 10
2 Yearly 25
3 Monthly 15
4 Weekly
5 yearly 14


Table 2
History_ID Datedue
Null null
null null
null null
null null
null null


Required output in table2

History_ID Date_Due
1 10/1/2011
1 10/2/2011
1 10/3/2011
1 10/4/2011
1 10/5/2011
1 10/6/2011
1 10/7/2011
1 10/8/2011
1 10/9/2011
1 10/10/2011
1 10/11/2011
1 10/12/2011

2 25/1/2011
2 25/1/2012
2 25/1/2013
2 25/1/2014
2 25/1/2015
2 25/1/2016
2 25/1/2017
2 25/1/2018
2 25/1/2019
2 25/1/2020


3 15/1/2011
3 15/2/2011
3 15/3/2011
3 15/4/2011
3 15/5/2011
3 15/6/2011
3 15/7/2011
3 15/8/2011
3 15/9/2011
3 15/10/2011
3 15/11/2011
3 15/12/2011


Please guide me..really appreciate..
Thanks and regards.

4
Contributors
7
Replies
8
Views
6 Years
Discussion Span
Last Post by BitBlt
0

table2 should be based on recurrance and date_due from table 1.
ID = History_ID

0

Had to do this with a cursor, and I wasn't sure how you wanted to handle the "weekly" occurrence, so I guessed. And, please note that I'm from the US so I'm using mm/dd/yyyy format. That being said, here's what I came up with:

declare @id int
declare @recurrance varchar(10)
declare @duepattern int
declare @iterator int
declare @dateString varchar(10)
declare cursor1 cursor for
select id, recurrance, duepattern from dbo.Table_1

open cursor1
fetch next from cursor1 into @id, @recurrance, @duepattern 
while @@FETCH_STATUS = 0
begin
	if @recurrance = 'weekly'
	begin
		set @iterator = 1
		set @dateString = '01/01/' + cast(year(getdate()) as varchar(4))
		print @datestring
		insert into dbo.Table_2 (history_id, date_due)
		values (@id, @dateString)
		while @iterator < 52
		begin
			set @dateString = convert(varchar(10), DATEADD(dd, 7, cast(@datestring as datetime)), 101)
			insert into dbo.Table_2 (history_id, date_due)
			values (@id, @dateString)
			set @iterator = @iterator + 1
		end
	end	

	if @recurrance = 'monthly'
	begin
		set @iterator = 1
		while @iterator < 13
		begin
			set @dateString = ltrim(cast(@iterator as varchar(2))) + '/' + ltrim(cast(@duepattern as varchar(2))) + '/' + cast(year(getdate()) as varchar(4))
			insert into dbo.Table_2 (history_id, date_due)
			values (@id, @dateString)
			set @iterator = @iterator + 1
		end
	end	

	if @recurrance = 'yearly'
	begin
		set @iterator = 1
		while @iterator < 13
		begin
			set @dateString = '01/' + ltrim(cast(@duepattern as varchar(2))) + '/' + cast(year(getdate()) + @iterator - 1 as varchar(4))
			insert into dbo.Table_2 (history_id, date_due)
			values (@id, @dateString)
			set @iterator = @iterator + 1
		end
	end	

	fetch next from cursor1 into @id, @recurrance, @duepattern 
end

close cursor1
deallocate cursor1

select history_id, CONVERT(varchar(10), date_due, 101) as date from dbo.Table_2

Obviously, you'll have to tweak the "weekly" recurrence if you want to have it iterate for a specific day of the week.

I hope this helps!

0

what i mean was.. Table 2 records based on table 1 ID, Recurrance and Duepattern..

table 1

ID Recurrance DuePattern
1 Monthly 25
2 Yearly 10


table 2

History_ID Date Due
1 25/1/2011
1 25/2/2011
1 25/3/2011
1
1
1
1
1
1
1 25/12/2011

2 10/1/2011
2 10/1/2012
2 10/1/2013
2 10/1/2014

0

Hello Bitbit..

Thank you very much..
still finding ERROR..
Output was not as expected.

Msg 515, Level 16, State 2, Line 48
Cannot insert the value NULL into column 'ID', table 'system.dbo.preventiveRecord'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(5495 row(s) affected)

0

Is ID column is primary key or do you have constraint? Is the ID column is set to autoincrement?

0

How about you post the SQL that you're actually trying out when you get your error? The code I posted worked just fine for me, given the constraints and examples you originally listed.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.