Search code examples
sqlsql-servercounting

Calculate the height of a row in this scheduler calendar?

I am looking for an SQL solution to pass onto my HTML javascript to alter the height of a row.

SCENARIO

I want to count the number of "rows" that will determine, how high (CSS height) this row should be to accommodate all the possible number of bars in this calendar.

For example, here we have 5 vessels in my calendar from the 5th Feb 2023, up to the 8th Feb 2023.

enter image description here

And here is the row data I want to "count" to give me '3' not '5'.

enter image description here

How do I count these records to find what's overlapped so I get the result of '3'? This I will use in javascript to adjust the height of my row to allow the number of bars to fit inside it.

Thanks

Solution

  • I think you are looking of maximum number of overlapping dates. You can use CROSS APPLY to unpivot the 2 dates and then perform a cumulative sum of the value (1 as Arrival, -1 as Departure). Finally MAX() will gives you the value that you want

    select  max(c)
from
(
    select  c = sum(e) over (order by dt)
    from    vessels
            cross apply
            (
                values
                (Arrival,    1), -- arrival
                (Departure, -1)  -- departure
            ) e (dt, e)
) t