Как да намерите диапазони от дати в записи с последователни дати и дублирани данни

Вероятно има някакво лесно решение за това, но не го виждам. Имам таблица с последователни дати и често дублирам свързани данни за няколко от тези последователни дати:

Date       Col1  Col2
5/13/2010  1     A
5/14/2010  1     A
5/15/2010  2     B
5/16/2010  1     A
5/17/2010  1     A
5/18/2010  3     C
5/19/2010  3     C
5/20/2010  3     C

Използвайки MS T-SQL, искам да намеря началната и крайната дата за всяко изпълнение на различни стойности Col1 и Col2:

StartDate  EndDate    Col1  Col2
5/13/2010  5/14/2010  1     A
5/15/2010  5/15/2010  2     B
5/16/2010  5/17/2010  1     A
5/18/2010  5/20/2010  3     C

Предположения: Никога няма липсващи дати. Col1 и Col2 не са нула. Някакви идеи - за предпочитане да не използват курсори? Много благодаря, -alan


database sql-server tsql sql-server-2000
person alan s    schedule 15.05.2010    source източник
comment
ако използвате Sql Server 2005+, бих отговорил на това stackoverflow.com/questions/1610599/   -  person Michael Buen    schedule 15.05.2010

Отговори (2)


arrow_upward
3
arrow_downward

За SQL 2005+ мисля, че по-долу трябва да работи

WITH DATES AS
(
   SELECT COL1, COL2, DATE,
      DATEADD(DAY, -1 * ROW_NUMBER() 
      OVER(PARTITION BY COL1, COL2 ORDER BY DATE), DATE) AS GRP
   FROM YOUR_TABLE
)
SELECT COL1, COL2, MIN(DATE) AS STARTDATE, MAX(DATE) AS ENDDATE
FROM DATES
GROUP BY COL1, COL2, GRP

Ако имате дублиращи се записи, използвайте DENSE_RANK() вместо ROW_NUMBER()

За SQL 2000 има включена подзаявка и свързана заявка.

SELECT COL1, COL2, MIN(DATE) AS STARTDATE, MAX(DATE) AS ENDDATE
FROM (SELECT COL1, COL2, DATE,
    (SELECT MIN(DATE)
     FROM YOUR_TABLE B
     WHERE B.DATE >= A.DATE AND B.COL1 = A.COL1 AND B.COL2 = A.COL2
           AND NOT EXISTS
           (SELECT *
            FROM YOUR_TABLE C
            WHERE C.COL1 = B.COL1 AND C.COL2 = B.COL2
            AND DATEDIFF(DAY, B.DATE, C.DATE) = 1)
    ) AS GRP
    FROM YOUR_TABLE A
)
GROUP BY COL1, COL2, GRP
person Chris Bednarski    schedule 15.05.2010

arrow_upward
1
arrow_downward

Ето един подход, използващ outer apply. Заменете @t с името на вашата таблица.

SELECT    head.date, last.date, head.col1, head.col2
FROM      @t head
OUTER APPLY (
          SELECT TOP 1 *
          FROM @t t
          WHERE t.date < head.date
          ORDER BY t.date desc
          ) prev
OUTER APPLY (
          SELECT TOP 1 *
          FROM @t t
          WHERE t.date > head.date
          AND (t.col1 <> head.col1 or t.col2 <> head.col2)
          ORDER BY t.date
          ) next
OUTER APPLY (
          SELECT TOP 1 *
          FROM @t t
          WHERE (t.date < next.date or next.date is null)
          AND (t.col1 = head.col1 and t.col2 = head.col2)
          ORDER BY t.date
          ) last
WHERE (prev.col1 is null or head.col1 <> prev.col1 or head.col2 <> prev.col2)

Заявката първо избира реда "head": редове, които започват нова група от col1, col2. Това се прави, като се търси "предишният" ред и се казва, че трябва да е различен в клаузата where.

След това търси края на групата col1, col2. Това е процес от две стъпки: първо потърсете първия ред от „следващата“ група, а редът преди това е „последният“ ред.

Date       Col1  Col2
...
5/15/2010  2     B      <-- "prev" row
5/16/2010  1     A      <-- "head" row
5/17/2010  1     A      <-- "last" row
5/18/2010  3     C      <-- "next" row
...

Резултатът от заявката съвпада с примерния резултат във вашия въпрос.

person Andomar    schedule 15.05.2010
comment
Хубаво решение. Трябваше да спомена, че това е за стара система sql server 2000. - person alan s; 15.05.2010
comment
Алън, трябва да отбележиш Андомар като решение, ако ти пасва. - person Irawan Soetomo; 20.05.2010