June 2, 2016 at 12:40 pm
SQLTeam.com Forums
If someone answers your question or provides a helpful response, please take a second to LIKE that reply. It's a nice way to say Thank You to someone that helped you out.
You do that by clicking on the little heart at the bottom of a post (either topic or reply).
And if you're looking at a thread and see a good answer, please feel free to LIKE that. Anyone can do it, not just the original author. And yes, you can like multiple threads in a post.
Create a query to update or insert between date ranges in a table
Transact-SQL
Arihant
8m
HelloTeam,
My table is having structure
Id A B C D E Fromdate todate
1 1 2 6 4 9 1900 1950
1 2 3 6 8 3 1951 2000
1 3 6 6 1 1 2001 9998
2.....
Here the A B C D E are attributes for a fund 1.
Date range defines their value between the intervals.
Now in the input i can get any attribute of fund with new value in a defined interval or may be the new interval. But i will also get the previous values also in the result set.
For eg i am getting the input data
Id Attribute Value fromdate todate
1 B 10 1900 1930
1 B 4 1931 2050
1 B 7 2051 9998
Now i need to update my final table as:
Id A B C D E Fromdate todate
1 1 10 6 4 9 1900 1930
1 1 4 6 4 9 1931 1950
1 2 4 6 8 3 1951 2000
1 3 4 6 1 1 2001 2050
1 3 7 6 1 1 2051 9998
So basically we are breaking the inetrvals based on value and updating values acc..
Always the input data will be from 1900-9998 range
And the table on which we are updating will also be from range 1900-9998.
Pls replay asap.
Thanks in advance.
Thanks,
Arihant Jain
June 2, 2016 at 12:54 pm
Please pay attention on how you post and don't simply copy and paste everything everywhere.
Read the links on my signature to get better help. Right now, I don't understand the logic to your problem.
June 2, 2016 at 1:04 pm
SQLTeam.com Forums
If someone answers your question or provides a helpful response, please take a second to LIKE that reply. It's a nice way to say Thank You to someone that helped you out.
You do that by clicking on the little heart at the bottom of a post (either topic or reply).
And if you're looking at a thread and see a good answer, please feel free to LIKE that. Anyone can do it, not just the original author. And yes, you can like multiple threads in a
HelloTeam,
My table is having structure
Id A B C D E Fromdate todate
1 1 2 6 4 9 1900 1950
1 2 3 6 8 3 1951 2000
1 3 6 6 1 1 2001 9998
Here the A B C D E are columns.
Date range defines their value for columns between the intervals.
Now in the input i can get any column with new value in between the defined interval in the table or may be the new interval.
For eg i am getting the input data
Id Attribute Value fromdate todate
1 B 1SQLTeam.com Forums
If someone answers your question or provides a helpful response, please take a second to LIKE that reply. It's a nice way to say Thank You to someone that helped you out.
You do that by clicking on the little heart at the bottom of a post (either topic or reply).
And if you're looking at a thread and see a good answer, please feel free to LIKE that. Anyone can do it, not just the original author. And yes, you can like multiple threads in a post.
Create a query to update or insert between date ranges in a table
Transact-SQL
Arihant
8m
HelloTeam,
My table is having structure
Id A B C D E Fromdate todate
1 1 2 6 4 9 1900 1950
1 2 3 6 8 3 1951 2000
1 3 6 6 1 1 2001 9998
2.....
Here the A B C D E are attributes for a fund 1.
Date range defines their value between the intervals.
Now in the input i can get any attribute of fund with new value in a defined interval or may be the new interval. But i will also get the previous values also in the result set.
For eg i am getting the input data
Id Attribute Value fromdate todate
1 B 10 1900 1930
1 B 4 1931 2050
1 B 7 2051 9998
Now i need to update my final table as:
Id A B C D E Fromdate todate
1 1 10 6 4 9 1900 1930
1 1 4 6 4 9 1931 1950
1 2 4 6 8 3 1951 2000
1 3 4 6 1 1 2001 2050
1 3 7 6 1 1 2051 9998
So basically we are breaking the inetrvals based on value and updating values acc..
Always the input data will be from 1900-9998 range
And the table on which we are updating will also be from range 1900-9998.
Pls replay asap.
Pls tell me what u didn't understand.
June 2, 2016 at 1:13 pm
Copying and pasting the same information won't help. As explained on the other threads, post DDL and insert statements to generate sample data.
Explain how you go from 3 rows to 5 rows and where do you get the additional columns from.
June 2, 2016 at 1:29 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 1930
1 1 4 6 4 9 1931 1950
1 2 4 6 8 3 1951 2000
1 3 4 6 1 1 2001 2050
1 3 7 6 1 1 2051 9998
June 2, 2016 at 1:36 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: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:57 pm
And before we do your work (or homework) for you, what have you tried to solve your problem?
June 2, 2016 at 2:59 pm
Your FinalResult table looks like it's the result of a pivot or crosstab. It would be easier to make the updates before the pivot/crosstab rather than after the fact. Especially if you add more data to the input that conflicts.
SELECT *
FROM (
VALUES
(1, 'A', 3, 1900, 1940)
,(1, 'A', 5, 1941, 1960)
,(1, 'A', 7, 1961, 9998)
,(1, 'B', 10, 1900, 1930)
,(1, 'B', 4, 1931, 2050)
,(1, 'B', 7, 2051, 9998)
)v(Id, Attribute, Value, fromdate, todate)
Also, it really helps if you use the IFCode shortcuts that are on the left side when you're creating a post, for example
[code="sql"]<your code here>[/code]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 2, 2016 at 6:51 pm
Do a web search for Upsert
See if that describes what you are trying to do.
Although your example is a bit different - it is only one column of the record being supplied.
June 2, 2016 at 9:38 pm
It is not the pivoted table.. Its a table with attributes stored in columns and in the input i can get any attribute to update data into the final table.
June 3, 2016 at 8:42 am
arihantjain121189 (6/2/2016)
It is not the pivoted table.. Its a table with attributes stored in columns and in the input i can get any attribute to update data into the final table.
Then why do you say that it is pivoted on this thread http://www.sqlservercentral.com/Forums/Topic1791861-3077-1.aspx.
Speaking of other threads. How many times are you going to create new threads with the exact same question? I count eight so far. The more threads you create, the less likely it is that you'll get the answer, mostly because it makes it harder to get a complete picture, but also because it will seriously tick off people.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 3, 2016 at 11:53 am
Did you take a few minutes to look at Upsert / Merge?
Normally I would be modeling with an Item and all the attributes current state, and not allow someone to update past history.
Here someone can rewrite history which might be a problem.
For example, if A is Color, B is Material, and C is Supplier.
An item could start out Red, then change to Blue at some point in time.
Material could be Mild Steel then change to Plastic.
And the Supplier could be XXX changing to YYY.
Report is run last year.
This year, after many updates, someone runs the report and questions why something changed.
Would you be able to recreate the original report to compare?
And be able to identify who changed what when?
It could be important, then maybe not.
June 3, 2016 at 8:43 pm
arihantjain121189 (6/2/2016)
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)
The input table above doesn't match the values you're inserting, also above. It's just not possible to understand what you're trying to do with such inaccuracies.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 4, 2016 at 12:22 pm
I am a little confused on your post(s)...but that maybe a language issue.
you speak about a "resultant" table...can you clarify is this the actual db table you require to be updated...or is the "resultant" table the end product (say after you have perfomed some form of pivot?)
I note that in your sample data, you always have contiguous start and end dates...is this always true?
what happens if you have overlapping dates / non contiguous?
...as an example....please provide your expected results for this set of data
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, Validfrom, validto)
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'),
(2, 9, 9, 9, 9, 9, '2001-01-01', '9998-12-31');
CREATE TABLE #input(
Id int NULL,
AttributeID varchar(1) NOT NULL,
AttributeValue int NULL,
ValidFrom date NULL,
ValidTo date NULL
)
INSERT INTO #input
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'),
(2 ,'C', 100, '2011-01-01' ,'2012-12-31');
SELECT * FROM #FinalResult
SELECT * FROM #input
DROP TABLE #FinalResult
DROP TABLE #input
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply