Multiple sum subqueries for percentage

I need help with the following problem: I want to make a query that contains multiples sums and then takes those sums and uses them to get a percentage: percentage= s1/s1+s2. I have as input the following data: Orders shipping date, Nb of orders that have arrived late, Nb of orders that have arrived on time What I want as output: The percentage of orders that have arrived late and orders that have arrived on time. I want another column in the table that will have the percentage using SQL.

Concrete example:

*On 2022/01/04 **10:00 AM** I have 3 orders late and 4 order on time=> 7 orders in total. Percentage=3/7 (late), (4/7) on time



*At 2022/01/04 **11:00 AM** I have 5 orders late and 6 orders on time=>11 orders in total (but all this entry is summed with the previous entry so:) <=> 5+3 orders late, 4+6 orders on time, 18 orders in total => percentage= 8/18 late, 10 on time.

In order to sum previous entries order numbers with status “LATE” to current on time order number I wrote the following sql: (sum1=s1)

SELECT s1.EventDate, (
    SELECT SUM(s2.NbOfOrders) 
    FROM OrderShipmentStats s2 
    WHERE s2.EventDate <= s1.EventDate AND s2.Status='LATE' 
) AS cnt
FROM OrderShipmentStats s1
GROUP BY s1.EventDate, s1.Status

The same kind of sql was written for “On Time” and it works. But what I need to do now is get the values and add them together of the two sql queries and based on the status which is late or on time do s1/s1+s2 or s2/s2+s1. My problem is that I do not know how to do this formula in a single query using those 2 subqueries, any help would be great.

Picture with Table

Above there is the link with the picture containing how the table looks(I am new so I am not allowed to embed a photo). The percentage column is the one I will add and there are lines pointing towards how that is calculated.

Answer

I created the table based on your image and added a few rows to it. In the query you could see total orders count per hour, per status and the grand total as you mentioned in the image.

The query looks like:

create table OrderShipmentsStats
(
    EventDate datetime not null,
    Status varchar(10) not null,
    OrdersCount int not null
)

insert into OrderShipmentsStats
values
('2022-01-04T10:00:00','Late',3),
('2022-01-04T10:00:00','On Time',4),
('2022-01-04T11:00:00','Late',5),
('2022-01-04T11:00:00','On Time',6),
('2022-01-04T12:00:00','Late',1),
('2022-01-04T12:00:00','On Time',2)

SELECT
    EventDate, 
    Status, 
    OrdersCount, 
    TotalPerHour, 
    StatusTotal,
    GrandStatusTotal,
    -- at the line below, multiplying by 1.0 will convert the result and we would receive smth like 0.45, 0.123, some percentage
    -- but we want the actual percent like 15%, or 50%. to obtain it, just multiply by 100
    cast(1.0 * o.StatusTotal / o.GrandStatusTotal as decimal(5,3)) * 100 as Percentage
from 
    (
        select 
            EventDate, 
            Status, 
            OrdersCount, 
            TotalPerHour,
            StatusTotal,
            SUM(TotalPerHour) over (partition by Status order by EventDate asc) as GrandStatusTotal
        from
            (
                select 
                    EventDate,
                    Status,
                    OrdersCount, 
                    Sum(OrdersCount) over (partition by EventDate order by EventDate asc) as TotalPerHour, 
                    SUM(OrdersCount) over (partition by Status order by EventDate asc) as StatusTotal
                from OrderShipmentsStats
            ) as t
    ) as o
order by EventDate, Status