June 2, 2016 at 1:37 pm
Create table FinalResult
(Id int,
A int,
B int,
C int,
D int,
E int,
Validfrom date,
validto date
)
insert into FinalResult (Id,A,B,C,D,E,fromdate,todate)
values
(1, 1 ,2 ,6, 4, 9, 1900-01-01, 1950-12-31),
(1, 2, 3, 6, 8, 3, 1951-01-01 , 2000-12-31),
(1, 3, 6, 6, 1, 1, 2001-01-01, 9998-12-31)
Here A,B,C,D,E are attributes name and their values are stored in rows and which can be different between (1900-01-01 to 9998-12-31) intervals.
For eg.
here the attribute B is having value 2 from range 1900-01-01 to 1950-12-31 after that its value is changed to 3 between interval 1951-01-01 to 2000-12-31.
Now I got a new values for attribute B on different intervals which are as:
create table input
(Id int,
A int,
B int,
C int,
D int,
E int,
Validfrom date,
validto date
)
values
(1 ,B ,10, 1900-01-01, 1930-12-31),
(1 ,B, 4, 1931-01-01, 2050-12-31),
(1 ,B, 7, 2051-01-01 ,9998-12-31)
Now I need to update resultant table as below
Id A B C D E Fromdate todate
1 1 10 6 4 9 1900-01-01 1930-12-31
1 1 4 6 4 9 1931-01-01 1950-12-31
1 2 4 6 8 3 1951-01-01 2000-12-31
1 3 4 6 1 1 2001-01-01 2050-12-31
1 3 7 6 1 1 2051-01-01 9998-12-31
So the new values are inserted for B into the resultant table by breaking intervals as the value was changing between three intervals.
The input data will always have the range from 1900-01-01 to 9998-12-31 and values of attributes between intervals.
The remaining attributes for which we didn't get input will be portioned on the basis of the new intervals as there value exists in these intervals..
pls make tsql for this task.
June 2, 2016 at 1:41 pm
duplicate post, reply here: http://www.sqlservercentral.com/Forums/Topic1791754-3077-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply