How to insert new record and update old one with enddate = new recored startDate - 1?

Question

One of the database tables from our ERP system contains only the effictive date for pricing like this :

Partnumber ||Effective Date  || Price
----------------------------------------------
abc             || 2012-01-01    ||  $ 1,00
abc             || 2012-02-01    ||  $ 1,10   
xyz             || 2012-01-01    ||  $ 1,00

My Dimension Table looks like :

Partnumber  |Start Date       ||  EndDate        || Price
-----------------------------------------------------------------
abc             || 2012-01-01    ||  2012-01-31   ||$ 1,00
abc             || 2012-02-01    ||  <NULL>        ||$ 1,00
xyz             || 2012-01-01    ||  <NULL>        ||$ 1,00

This enable me find the right price based up the orderdate between StartDate and EndDate (or EndDate IS NULL)

Finaly my question : How can I update the EndDate enddate = new recored startDate - 1 once there are new records for that partnumber with a new EffectiveDate.

what is operations table schema, what is staging table used for? how is the table t related? — radar, Oct 17 '14 at 01:58
@RADAR see the update? the Operations table is same as staging but have ID Filed and UQ constraint on name,startDate,EndDate — , Oct 17 '14 at 02:11
If the end of one interval is always going to be one day prior to the start of the next, why store the end at all? It would be easy to calculate the end of the previous interval (= start of next one minus 1 day). If you're on SQL 2012 or later, you can use the LEAD() function for this. — Ben Thul, Oct 17 '14 at 02:20
@BenThul that is the problem we's using SQLserver 2008 not 2012 — , Oct 17 '14 at 02:22
You want to insert a new record in which table when you say `I want to write TSQL to insert new record` ? — Ram, Oct 17 '14 at 02:34
Can you mentioned the desired output based on the staging table entries? — Ram, Oct 17 '14 at 02:43
you changed the whole question? where is staging table and operations table? — Ram, Oct 17 '14 at 02:51
How are you populating your dimension table now? This will need to be a change to existing code, so will need to see the existing code. Is it in SSIS or in T-SQL? Once of the goals of using dimension tables is performance, so yes you should write the end date explicitly into the dimension table, not work it out on the fly — Nick.Mc, Oct 17 '14 at 02:55

score 0 · Accepted Answer · answered Oct 17 '14 at 03:19

The startdate in your tables is defined as varchar(20) & end date as int. Is this a mistake? I am uncertain exactly what you're hoping to set end date (as an int) to, given it is to be 1 less than the new startdate.

My code would do the job were both startdates and enddates of datetime or smalldatetime datatype. If your tables are actually in the structure you've given you'll have to adjust the logic accordingly.

This logic should probably be put into a stored procedure.

declare stageCursor cursor scroll for 
    select name, startdate, enddate from staging;

declare @name varchar(20), 
        @startdate datetime, 
        @enddate datetime;

open stageCursor
fetch next from stageCursor into @name, @startdate, @enddate
while @@fetch_status = 0 begin
    update operations set o.enddate = dateadd(d, -1, s.startdate) 
    where name = @name --does this need to be restricted to just the latest operations row somehow?

    insert operations (name, startdate, enddate)
    values (@name, @startdate, @enddate);

    fetch next from stageCursor into @name, @startdate, @enddate
end

close stageCursor
deallocate stageCursor

truncate table staging   --I presume after the data has been used to update the
                         --operations table, it isnt going to wanted anymore

can achieve this logic without using cursor – Oct 17 '14 at 03:29 — , Oct 17 '14 at 03:29

score 0 · Answer 2 · edited May 23 '17 at 11:44

Try this:

with t as
(
select 
    row_number() over (partition by Partnumber order by [Effective Date]) as row_id,
    *
from your_table
)
select
    t_start.Partnumber,
    t_start.[Effective Date] as start_dt,
    dateadd(day,-1,t_end.[Effective Date]) as end_dt,
    t_start.Price
from 
    t as t_start
left join 
    t as t_end
on 
    t_start.row_id+1 = t_end.row_id and t_start.Partnumber=t_end.Partnumber

UPDATE

If you want update the end_date in your_table, you could:

update your_table
set end_dt = (select dateadd(day,-1,min(t.start_dt)) from your_table t 
              where t.Partnumber=your_table.Partnumber 
              and t.start_dt>your_table.start_dt);

The result may have duplicate prices for two continuous periods, here's the approach to solve this problem.

You could user this SQL FIDDLE DEMO to test it.

How to insert new record and update old one with enddate = new recored startDate - 1?

2 Answers2