CODEWITHSUNDEEP
sqlsql-server-2008
Angelo Badellino
Angelo Badellino

Reputation: 2201

UPDATE field based on another row filed value

I have a table stored on a SQL Server 2008, that associate a value to a date range.

DateFrom      DateTo         Value
2012-01-01    2012-02-01      10
2012-02-02    2012-02-15      15

The application that deal with this table, can insert a new range between the existings. For example, If i insert

DateFrom      DateTo         Value
2012-02-07    2012-02-10      12

The result must be

DateFrom      DateTo         Value
2012-01-01    2012-02-01      10
2012-02-02    2012-02-06      15
2012-02-07    2012-02-10      12
2012-02-11    2012-02-15      15

I can do that programmatically from the application, but I wonder if there is some fast SQL statement that make me able to set the data values by referencing other row's field and performing data operation on it.

A MUST requirement is that the date range must represent a time sequence, two range cannot span each other.

Upvotes: 2

Views: 1233

Answers (3)

GarethD
GarethD

Reputation: 69809

I've had similar problems in the past, and found that if the range needs to be continuous the best approach is to do away with the End Date of the range, and calculate this as the Next start date. Then if needs be create a view as follows:

SELECT  FromDate,
        (   SELECT  DATEADD(DAY, -1, MIN(DateFrom))
            FROM    YourTable b
            WHERE   b.FromDate > a.FromDate
        ) [ToDate],
        Value
FROM    YourTable a

This ensures that 2 ranges can never cross, however does not necessarily ensure no work is required upon insert to get the desired result, but it should be more maintainable and have less scope for error than storing both the start and end date.

ADDENDUM

Once I had written out all of the below I realised it does not improve maintainability that much to do away with the DateTo Field, it still requires a fair amount of code for the validation, but here's how I would do it anyway.

DECLARE  @T table (DateFrom DATE, Value INT)
INSERT INTO @T VALUES ('20120101', 10), ('20120202', 15), ('20120207', 12), ('20120211', 15)

DECLARE @NewFrom DATE = '20120209',
        @NewTo DATE = '20120210',
        @NewValue INT = 8

-- SHOW INITIAL VALUES FOR DEMONSTATIVE PURPOSES -- 
SELECT  DateFrom,
        ISNULL((   SELECT  DATEADD(DAY, -1, MIN(DateFrom))
                    FROM    @t b
                    WHERE   b.DateFrom > a.DateFrom
                ), CAST(GETDATE() AS DATE)) [DateTo],
        Value
FROM    @t a
ORDER BY DateFrom

;WITH CTE AS
(   SELECT  DateFrom,
            (   SELECT  DATEADD(DAY, -1, MIN(DateFrom))
                FROM    @t b
                WHERE   b.DateFrom > a.DateFrom
            ) [DateTo],
            Value
    FROM    @t a    
), 
MergeCTE AS
(   SELECT  @NewFrom [DateFrom], @NewValue [Value], 'INSERT' [RowAction]
    WHERE   @NewFrom < @NewTo   -- ENSURE A VALID RANGE IS ENTERED
    UNION ALL 
    -- INSERT A ROW WHERE THE NEW DATE TO SLICES AN EXISTING PERIOD
    SELECT  DATEADD(DAY, 1, @NewTo), Value, 'INSERT'
    FROM    CTE
    WHERE   @NewTo BETWEEN DateFrom AND DateTo
    UNION ALL 
    -- DELETE ALL ENTRIES STARTING WITHIN THE DEFINED PERIOD
    SELECT  DateFrom, Value, 'DELETE'
    FROM    CTE
    WHERE   DateFrom BETWEEN @NewFrom AND @NewTo
)
MERGE INTO @t t USING MergeCTE c ON t.DateFrom = c.DateFrom AND t.Value = c.Value
WHEN MATCHED AND RowAction = 'DELETE' THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (c.DateFrom, c.Value);

SELECT  DateFrom,
        ISNULL((   SELECT  DATEADD(DAY, -1, MIN(DateFrom))
                    FROM    @t b
                    WHERE   b.DateFrom > a.DateFrom
                ), CAST(GETDATE() AS DATE)) [DateTo],
        Value
FROM    @t a
ORDER BY DateFrom

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

I've written an example based on the example I gave you in a comment, it may do what you want. Since, in general terms, there might be multiple rows to insert/delete, it's best to define them all separately, then use a MERGE to perform the overall change.

I've also assumed that it's okay to delete/insert to achieve the splitting - you can't update and produce 2 rows from 1, so you'd always have to do an insert, and the symmetry is cleaner if I do both:

declare @T table (DateFrom datetime2, DateTo datetime2,Value int)
insert into @T(DateFrom   ,   DateTo    ,     Value) VALUES
('20120101',    '20120201',      10),
('20120202',    '20120206',      15),
('20120207',    '20120210',      12),
('20120211',    '20120215',      15)

select * from @t order by DateFrom

declare @NewFrom datetime2 = '20120205'
declare @NewTo datetime2 = '20120208'
declare @NewValue int = 8

--We need to identify a) rows to delete, b) new sliced rows to create, and c) the new row itself
;With AlteredRows as (
    select @NewFrom as DateFrom,@NewTo as DateTo,@NewValue as Value,1 as toInsert
    union all
    select DateFrom,DATEADD(day,-1,@NewFrom),Value,1 from @t where @NewFrom between DATEADD(day,1,DateFrom) and DateTo
    union all
    select DATEADD(day,1,@NewTo),DateTo,Value,1 from @t where @NewTo between DateFrom and DATEADD(day,-1,DateTo)
    union all
    select DateFrom,DateTo,0,0 from @t where DateTo > @NewFrom and DateFrom < @NewTo
)
merge into @t t using AlteredRows ar on t.DateFrom = ar.DateFrom and t.DateTo = ar.DateTo
when matched and toInsert=0 then delete
when not matched then insert (DateFrom,DateTo,Value) values (ar.DateFrom,ar.DateTo,ar.Value);

select * from @t order by DateFrom

It may be possible to re-write the CTE so that it's a single scan of @t - but I only think it's worth doing that if performance is critical.

Upvotes: 2

aF.
aF.

Reputation: 66737

You can use a cursor to get each row from the table at a time and aftwerwards do the necessary calculations.

If NewDateFrom >= RowDateFrom and NewDateFrom <= RowDateTo ...

Check this article to see how to make a cursor.

Upvotes: 0

Related Questions