CODEWITHSUNDEEP
sqlsql-server
Guillaume Lhz
Guillaume Lhz

Reputation: 904

SQL : Get range date from multiple begin and end date

How to get the "max" range of dates from multiple dateBegin and dateEnd ? My question is not well explained (cause im not english) but the example below will show you what I expect.

My database :

enter image description here

The output I'd like :

id_master    beginDate    endDate
13           26/07/2014   30/08/2014
280          28/09/2013   01/10/2013
280          01/04/2014   11/04/2014

Explain : for distinct id_master, i would like to have the diferrent periods of dates composed of the minimum beginDate and the maximum endDate with all the days between those dates having a product (row in the table)

Current query :

SELECT DISTINCT campings.id_master, CAST(campings.dateBegin AS DATETIME) AS beginDate, CAST(campings.dateEnd AS DATETIME) AS endDate
FROM         campings 
ORDER BY id_master, beginDate, endDate

PS: date format is dd/mm/yyyy

Upvotes: 2

Views: 2616

Answers (3)

Lukáš Kříž
Lukáš Kříž

Reputation: 3

For others, here is the result of my current needs. On Informix 11.70 and above has the same results this construction :


SELECT id_master , MIN(beginDate) AS beginDate, endDate FROM
  LATERAL (
    SELECT id_master, beginDate, MAX(endDate) AS endDate FROM
      LATERAL (
        SELECT id_master, beginDate, CONNECT_BY_ROOT endDate FROM
          LATERAL (
            SELECT id_master, beginDate, endDate, ROW_NUMBER() OVER(PARTITION BY id_master ORDER BY beginDate, endDate) AS row_num FROM campings
          ) AS ordered
          CONNECT BY
            PRIOR id_master = id_master AND
            PRIOR row_num > row_num AND
            PRIOR endDate + 1 >= beginDate AND
            PRIOR beginDate - 1 <= endDate
      ) AS Adjacent
      GROUP BY id_master, beginDate
  ) AS resolvedEnd
  GROUP BY id_master, endDate

Upvotes: 0

Ceres
Ceres

Reputation: 3648

SELECT DISTINCT 
    id_master, 
    MIN (beginDate) OVER (PARTION BY id_master) beginDate, 
    MAX(endDate) OVER (PARTION BY id_master) endDate 
FROM campings

Upvotes: -1

lc.
lc.

Reputation: 116468

This is probably a lot more contrived than it has to be, and someone else can come up with a simpler answer, but you can try something like the following:

WITH ordered AS (
  SELECT a.id_master, a.beginDate, a.endDate, 
      ROW_NUMBER() OVER(PARTITION BY id_master ORDER BY beginDate, endDate) AS rn
  FROM Table1 a
), Adjacent AS (
  SELECT a.id_master, a.beginDate, a.endDate, a.rn
  FROM ordered a
  UNION ALL
  SELECT a.id_master, a.beginDate, b.endDate, b.rn
  FROM Adjacent a
  INNER JOIN ordered b ON a.id_master = b.id_master AND b.rn > a.rn 
      AND a.endDate >= b.beginDate
), resolvedEnd AS (
  SELECT a.id_master, a.beginDate, MAX(a.endDate) AS endDate
  FROM Adjacent a
  GROUP BY a.id_master, a.beginDate
)
SELECT a.id_master, MIN(beginDate) AS beginDate, endDate
FROM resolvedEnd a
GROUP BY a.id_master, a.endDate

SQL Fiddle example

What this does is first attach an ascending row number to each row to make sure we recurse only in the forward direction. Then it sets up a recursive CTE to associate overlapping rows (and overlapping rows of overlapping rows). It then resolves the largest end date for each begin date, and then resolves the earliest begin date for each end date.

Upvotes: 3

Related Questions