1

I am trying to identify sum of quantity for each group if date ranges are overlapping. I am using postgresql to solve for it. For example

id group start_date end_date quantity
1 a 2020-09-11 2020-10-09 50
1 a 2020-09-11 2020-10-31 20
1 a 2020-11-01 2020-12-01 7
1 a 2020-11-15 2020-11-20 6
2 b 2020-10-06 2020-10-30 10
2 b 2020-10-09 2022-10-17 5
2 b 2020-10-15 2022-10-26 3

What I am trying to achieve is the following:
Expected Output: "edited"

id group start_date end_date quantity
1 a 2020-09-11 2020-10-31 70
1 a 2020-11-01 2020-12-01 13
2 b 2020-10-06 2020-10-30 18

would appreciate your help!

2 Answers 2

2

SOLUTION 1 : when all the date intervals within the same group are overlapping

SELECT id
     , group
     , min(start_date) AS start_date
     , max(end_date) AS end_date
     , Sum(quantity) AS quantity
  FROM mytable
GROUP BY id, group

SOLUTION 2 : when all the date intervals within the same group are not overlapping

Using a window function would be great but I don't see how to specify the window which corresponds to a subset of date ranges which overlap. So I come back to a basic solution based on a self-join which select for every row of table a the subset of rows of table b which overlap with that row :

SELECT DISTINCT ON (a.id, date_start, date_end)
       a.id
     , min(b.start_date) AS date_start
     , max(b.end_date) AS date_end
     , sum(b.quantity) AS quantity
  FROM mytable AS a
 INNER JOIN mytable AS b
    ON a.id = b.id
   AND a.start_date < b.end_date
   AND a.end_date > b.start_date
 GROUP BY a.id, a.start_date, a.end_date
 ORDER BY a.id, date_start

see the test result in dbfiddle

Sign up to request clarification or add additional context in comments.

3 Comments

Appreciate it! But I don't think this would work if each group had different non-overlapping dates since I want to capture each date range if dates are not overlapping.
Ok please update pour question with an illustration of non overlapping dates for thé same group
Just did that. Thanks for your prompt reply!
1

You may use the LAG function to check the consecutive overlapped date periods and create unique groups for them as the following:

WITH create_groups AS
(
  SELECT *, SUM(chk) OVER (PARTITION BY id, group_ ORDER BY start_date) grp
  FROM
   (
     SELECT *,
        CASE 
         WHEN start_date <= LAG(end_date, 1, end_date) OVER (PARTITION BY id, group_ ORDER BY start_date) AND
              start_date >=LAG(start_date, 1, start_date) OVER (PARTITION BY id, group_ ORDER BY start_date) 
         THEN 0 ELSE 1
        END AS chk
     FROM table_name
  ) T
)
SELECT id, group_, 
       MIN(start_date) start_date,
       MAX(end_date) start_date,
       SUM(quantity) quantity
FROM create_groups
GROUP BY id, group_, grp
ORDER BY id, group_, grp

See a demo.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.