SQLTeam.com | Weblogs | Forums

Create a query to update or insert between date ranges in a table


#1

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


#2

The following might get you in the right direction, but you have to update/delete/insert the rows into your table yourself:

create table #yourtable1(
   id int
  ,a int
  ,b int
  ,c int
  ,d int
  ,e int
  ,fromdate int
  ,todate int
);

insert into #yourtable1 values
/*****
 * Your sample data untouched
 */
   (1,1,2,6,4,9,1900,1950)
  ,(1,2,3,6,8,3,1951,2000)
  ,(1,3,6,6,1,1,2001,9998)
/*****
 * Your sample data untouched
 */
  ,(2,1,2,6,4,9,1900,1950)
  ,(2,2,3,6,8,3,1951,2000)
  ,(2,3,6,6,1,1,2001,9998)
/*****
 * Modified sample data with gap
 */
  ,(3,1,2,6,4,9,1900,1920)
  ,(3,2,3,6,8,3,1951,2000)
  ,(3,3,6,6,1,1,2001,9998)
;

create table #yourtable2(
   id int
  ,attribute char(1)
  ,value int
  ,fromdate int
  ,todate int
);

insert into #yourtable2 values
/*****
 * Your sample data untouched
 */
   (1,'B',10,1900,1930)
  ,(1,'B',4,1931,2050)
  ,(1,'B',7,2051,9998)
/*****
 * Modified sample data to test other combinations, amongst modifying multiple columns
 */
  ,(2,'B',10,1900,1930)
  ,(2,'B',4,1931,2050)
  ,(2,'B',7,2051,2070)
  ,(2,'B',7,2101,9998)
  ,(2,'C',10,1900,1930)
/*****
 * Your sample data untouched to be applied on "gap" data
 */
  ,(3,'B',10,1900,1930)
  ,(3,'B',4,1931,2050)
  ,(3,'B',7,2051,9998)
;

with cte_tally(n)
  as (select * from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t(n))
/*****
 * Split every record into atoms (one row per year)
 */
    ,cte_split(id,a,b,c,d,e,y)
  as (select a.id
            ,max(case
                when d.attribute is not null 
                 and d.attribute='A'
                then d.value
                else isnull(c.a,-999999)
             end) as a
            ,max(case
                when d.attribute is not null 
                 and d.attribute='B'
                then d.value
                else isnull(c.b,-999999)
             end) as b
            ,max(case
                when d.attribute is not null 
                 and d.attribute='C'
                then d.value
                else isnull(c.c,-999999)
             end) as c
            ,max(case
                when d.attribute is not null 
                 and d.attribute='D'
                then d.value
                else isnull(c.d,-999999)
             end) as d
            ,max(case
                when d.attribute is not null 
                 and d.attribute='E'
                then d.value
                else isnull(c.e,-999999)
             end) as e
            ,b.y
        from (select id
                    ,min(fromdate) as fromdate
                    ,max(todate) as todate
                from #yourtable1
               group by id
             ) as a
             inner join (select row_number() over(order by (select null)) as y
                           from cte_tally as a
                                cross apply cte_tally as b
                                cross apply cte_tally as c
                                cross apply cte_tally as d
                        ) as b
                     on b.y>=a.fromdate
                    and b.y<=a.todate
             left outer join #yourtable1 as c
                          on c.id=a.id
                         and c.fromdate<=b.y
                         and c.todate>=b.y
             left outer join #yourtable2 as d
                          on d.id=a.id
                         and d.fromdate<=b.y
                         and d.todate>=b.y
       group by a.id
               ,b.y
     )
select *
  into #temptemp
  from cte_split
;

/*****
 * Collect/find fromdate(s)
 */
 with cte_collect_from(id,a,b,c,d,e,fromdate,rn)
  as (select a.id
            ,a.a
            ,a.b
            ,a.c
            ,a.d
            ,a.e
            ,a.y as fromdate
            ,row_number() over(partition by a.id order by a.y) as rn
        from #temptemp as a
             left outer join #temptemp as b
                          on b.id=a.id
                         and b.a=a.a
                         and b.b=a.b
                         and b.c=a.c
                         and b.d=a.d
                         and b.e=a.e
                         and b.y=a.y-1
       where b.id is null
     )
/*****
 * Collect/find todate(s)
 */
    ,cte_collect_to(id,a,b,c,d,e,todate,rn)
  as (select a.id
            ,a.a
            ,a.b
            ,a.c
            ,a.d
            ,a.e
            ,a.y as todate
            ,row_number() over(partition by a.id order by a.y) as rn
        from #temptemp as a
             left outer join #temptemp as b
                          on b.id=a.id
                         and b.a=a.a
                         and b.b=a.b
                         and b.c=a.c
                         and b.d=a.d
                         and b.e=a.e
                         and b.y=a.y+1
       where b.id is null
     )
/*****
 * Combine fromdate(s) and todate(s)
 */
select a.id
      ,a.a
      ,a.b
      ,a.c
      ,a.d
      ,a.e
      ,a.fromdate
      ,b.todate
  from cte_collect_from as a
       left outer join cte_collect_to as b
                    on b.id=a.id
                   and b.rn=a.rn
 order by a.id
         ,a.fromdate
;

drop table #temptemp;
drop table #yourtable2;
drop table #yourtable1;