sql - Add Null Values into Table, where there is a missing spot in a row of values

Question

I have a table with how much time it took for a person to wait for the train from the moment they arrived until the train came. I want to say how many persons had to wait from 0 to 10 ms, 10 - 20 ms, and so on. I have a table with all these data. My problem is, if there is no person who had to wait between 0 and 10 ms i would want it to still be there in the table with the value 0, but I want to write a query in which this is done automatically, to detect where is a gap and create a time interval and fill it. I have written an SQL query for it, to get the information I want, but some time intervals are missing. It looks like this:

SELECT (p2.TimeMs - p1.TimeMs)/(1000*10)+1 AS TimeBucket, COUNT(p2.Passenger - p1.Passenger) AS Separated
FROM Passenger p1 
JOIN Passenger p2 ON p1.passenger = p2.passenger
WHERE p1.Event= 0 AND p2.Event= 1 //0 is for arriving at the station and 1 is for the ending of the waiting time
GROUP BY TimeBucket ORDER BY TimeBucket;

question from:https://stackoverflow.com/questions/65829931/add-null-values-into-table-where-there-is-a-missing-spot-in-a-row-of-values

Answer 1

Maybe try something like this I think. You would need to replace the temp table declaration with a the SQL Lite equivalent. Also you may need to cast your values to decimals where for instance if p2.TimeMs - p1.TimeMs < 10000 you would get zero returned if the data is stored as integers.

declare @buckets table 
(
BucketID int
TimeBucket int,
BucketDesc varchar(50)
)

insert @buckets(bucket) 
values(1,10,'0-10'), 
--....all values - could populate other values using distinct set of buckets from below 

select A.TimeBucket, B.* 
from @buckets B 
left join 
(
    SELECT (p2.TimeMs - p1.TimeMs)/(1000*10)+1 AS TimeBucket, COUNT(p2.Passenger - p1.Passenger) AS Separated
    FROM Passenger p1 
    JOIN Passenger p2 ON p1.passenger = p2.passenger
    WHERE p1.Event= 0 AND p2.Event= 1 //0 is for arriving at the station and 1 is for the ending of the waiting time
    GROUP BY TimeBucket
) D
on B.TimeBucket = D.TimeBucket