count max channels used in given day / interval and point out this day-CodePudding

I have a lot of CDRs and whould like to report simultaneous channels for given day (day, by day).

So i simplyfiy, that during a night there is a 0 channels used (this is a fact, i filter out calls made after midnight) - this has very low impact on statistics and simplifies calculations, i use well-know cursor for this (it's a T-SQL but could be more generic aproach)

(
  SELECT  date_start AS ts,  1 AS type,
    ROW_NUMBER() OVER(ORDER BY date_start) AS start_ordinal
  FROM [cdrs] with (nolock)
  WHERE bill_id in ( 12345 )   and date_start between '2021-01-01' and '2022-03-14' 
  UNION ALL
  SELECT  date_end, -1, NULL
  FROM [cdrs] with (nolock)
  WHERE bill_id in ( 12345 )   and date_start between '2021-01-01' and '2021-03-14'
),
C2 AS
(
  SELECT *,
    ROW_NUMBER() OVER( ORDER BY ts, TYPE ) AS start_or_end_ordinal
  FROM C1
)
SELECT  MAX(2 * start_ordinal - start_or_end_ordinal) AS max_channels
FROM C2
WHERE type = 1

(also i use nolock for speed - simply day must have ended, all of calls must have ended, in order for this to work).

what i would like to achive:

have this - for every single day - in that period of time, simply using "group by year(), month(), day()" is not a solution (gives wrong answers) also next step - have this for every possible bill_id (it's an int in range 0..65535)

does some one have any nice approach as nexting select in select (cursor in cursor) or many views - looks like reinventing the wheel.

CodePudding user response：

It's hard to say for sure without seeing sample data and expected results, but it seems you want the maximum number of simultaneous events, per day.

So we can do the following steps:

Unpivot the start and end times into separate rows, adding in a quantity difference for each row: 1 for start times, -1 for end times.
Calculate a rolling sum of that difference, partitioned by day.
Group by the day and take the maximum of the rolling sum.

SELECT
  t.date,
  maxSimultaneous = MAX(t.rollingQty)
FROM (
    SELECT
      date = CAST(v.ts AS date),
      v.ts,
      v.diffQuantity,
      rollingQty = SUM(v.diffQuantity) OVER (PARTITION BY CAST(v.ts AS date) ORDER BY v.ts ROWS UNBOUNDED PRECEDING)
    FROM cdrs
    CROSS APPLY (VALUES
      (cdrs.date_start, 1),
      (cdrs.date_end,  -1)
    ) v(ts, diffQuantity)
    WHERE cdrs.bill_id = 12345
      AND cdrs.date_start >= '2021-01-01'
      AND cdrs.date_start <  '2021-03-15'
) t
GROUP BY
  t.date;

Note the use of a half-open interval >= AND < for dates, rather than BETWEEN.