Create or update rows in a table on the basis of intervals.

• arihantjain121189

  SSC-Addicted

  Points: 407

  More actions

  June 2, 2016 at 12:39 pm

  #400231

  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

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  June 2, 2016 at 1:03 pm

  #1882917

  Your sample data, while nicely laid out, doesn't help us one bit in writing a query that hits a table and produces the desired output. Please convert your request to provide create table script(s) and INSERT statements as well as the expected output.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  June 2, 2016 at 1:42 pm

  #1882939

  duplicate post, reply here: http://www.sqlservercentral.com/Forums/Topic1791754-3077-1.aspx

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden