Per Color the max amount of bins used at the same time-CodePudding

I have the following dataset:

CREATE TABLE #boxColor
(Color varchar(15)
,BoxId int
,AddedOn datetime2
,DeactivatedOn datetime2)

INSERT INTO #boxColor(Color, BoxId, AddedOn, DeactivatedOn)
VALUES 
('Black',1,'4/11/19 2:02 PM','12/31/99 12:00 AM'),
('Black',6,'4/12/19 11:16 AM','12/31/99 12:00 AM'),
('Black',7,'4/12/19 12:25 PM','12/31/99 12:00 AM'),
('blue',21,'4/12/19 1:14 PM','12/31/99 12:00 AM'),
('blue',31,'6/5/19 2:03 PM','12/31/99 12:00 AM'),
('yellow',32,'6/5/19 2:15 PM','8/8/19 4:53 PM'),
('orange',3,'6/7/19 9:01 AM','6/7/19 10:28 AM'),
('blue',34,'6/7/19 10:26 AM','6/7/19 11:20 AM'),
('orange',35,'6/7/19 10:28 AM','6/7/19 11:42 AM'),
('yellow',36,'6/7/19 11:20 AM','12/31/99 12:00 AM')

I am trying to get a list each color bin, and the max amount of that color that was ever in use at once

I can't seem to wrap my brain around this.

Let me know if I can clarify in any way.

CodePudding user response：

You need to start by unpivoting the event dates (Added and Deactivated) into separate rows.

Then calculate a running sum of the difference in quantity between each event.

Then simply group it up and take the max.

SELECT
  bc.Color,
  PeakQty = MAX(bc.RunningSum)
FROM (
    SELECT *,
      RunningSum = SUM(v.DiffQty) OVER (PARTITION BY bc.Color
                   ORDER BY v.EventDate ROWS UNBOUNDED PRECEDING)
    FROM boxColor bc
    CROSS APPLY (VALUES
        (bc.AddedOn,        1),
        (bc.DeactivatedOn, -1)
    ) v(EventDate, DiffQty)
) bc
GROUP BY
  bc.Color;

db<>fiddle

To elaborate on how this works:

We take every AddedOn and DeactivatedOn date, and unpivot them into separate rows. For the former, we have a DiffQty of 1 and the latter -1.
Think of the rows now as events: an addition and a removal.
We then sort all those dates in order (partitoned by Color)....
... and we calculate a running sum of DiffQty, ordered by these unpivoted dates (and partitioned by Color). This means that each event either adds or removes 1 from the running sum.
Then we can simply group it up by Color, and take the highest RunningSum value that we have.