Mam tabelę przechowywaną na serwerze SQL Server 2008, która kojarzy wartość z zakresem dat.

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

Aplikacja, która zajmuje się tą tabelą, może wstawić nowy zakres pomiędzy istniejącymi. Na przykład, jeśli wstawiam

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

Wynik musi być

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

Mogę to zrobić programowo z poziomu aplikacji, ale zastanawiam się, czy istnieje jakaś szybka instrukcja SQL, która umożliwiłaby mi ustawienie wartości danych poprzez odwołanie się do pola innego wiersza i wykonanie na nim operacji na danych.

Wymaganiem MUSI jest to, że zakres dat musi reprezentować sekwencję czasową, dwa zakresy nie mogą obejmować siebie nawzajem.

2
AngeloBad 27 luty 2012, 14:50

3 odpowiedzi

Najlepsza odpowiedź

W przeszłości miałem podobne problemy i stwierdziłem, że jeśli zakres musi być ciągły, najlepszym rozwiązaniem jest usunięcie daty końcowej zakresu i obliczenie jej jako następnej daty początkowej. Następnie w razie potrzeby utwórz widok w następujący sposób:

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

Gwarantuje to, że 2 zakresy nigdy nie mogą się przecinać, ale niekoniecznie gwarantuje, że po wstawieniu nie jest wymagana żadna praca, aby uzyskać pożądany wynik, ale powinno być łatwiejsze w utrzymaniu i mieć mniejszy zakres błędów niż przechowywanie zarówno daty początkowej, jak i końcowej.

addendum

Kiedy już napisałem wszystkie poniższe informacje, zdałem sobie sprawę, że usunięcie pola DateTo nie poprawia tak bardzo łatwości konserwacji, nadal wymaga sporej ilości kodu do walidacji, ale i tak zrobiłbym to w następujący sposób .

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
1
GarethD 28 luty 2012, 17:30

Napisałem przykład na podstawie przykładu, który podałem w komentarzu, może zrobić to, co chcesz. Ponieważ, ogólnie rzecz biorąc, może być wiele wierszy do wstawienia/usunięcia, najlepiej zdefiniować je wszystkie osobno, a następnie użyć MERGE, aby dokonać ogólnej zmiany.

Założyłem również, że można usunąć/wstawić, aby uzyskać podział — nie można zaktualizować i utworzyć 2 wierszy z 1, więc zawsze trzeba wykonać wstawienie i symetrię jest czystszy, jeśli zrobię obie:

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

Możliwe jest przepisanie CTE tak, aby był to pojedynczy skan @t - ale myślę, że warto to zrobić tylko wtedy, gdy wydajność jest krytyczna.

2
Andriy M 27 luty 2012, 22:53

Możesz użyć kursora, aby pobrać każdy wiersz z tabeli na raz, a następnie wykonać niezbędne obliczenia.

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

Przeczytaj ten artykuł, aby dowiedzieć się, jak utworzyć kursor.

0
aF. 27 luty 2012, 15:14