How can I count the number of items in inventory available on any given day?

I have a table that contains records for items that are available. Each of the records contains a field for the date it was added to the inventory, the date it was removed from inventory (otherwise NULL) and a unique serial number (separate from the primary key). There are also other fields giving metadata about the item, like brand, model, height, weight, etc.

Using that table I want to come up with a new table that tells me item availability for every day, from the first item availability date until present, for all items. , ie I want to convert the item availability data from a sparse to dense representation.

I know this involves doing a join to a table of dates, but I don’t know how to do that, or even how to describe more formally what I am trying to do. My mental model for this would be to create an X rows by Y columns, with each row a given serial number and column for date, but I don’t know how to do this in sql and I think it will make unmanageable large tables. I think a better way would be to have a long narrow table, with one column for date and another for serial number and a third indicating it’s presence or absence. But I don’t know if this is feasible at all.

Currently I am trying to accomplish this in MySQL, but I can do it either in Postgres or MS Sql/SQL Server if needed.

Thanks

Answer

When I understand correctly this DBFIDDLE does do what you want:

WITH RECURSIVE dates AS(
    SELECT 
      min(dateadded) AS d, 
      0 as x
    FROM items
    
    UNION ALL
    SELECT DATE_ADD(d,INTERVAL 1 DAY),x+1
    FROM dates 
    WHERE d<=(SELECT MAX(dateadded) FROM items)
)
SELECT 
   d,
   serialnumber
FROM dates
LEFT JOIN items ON d BETWEEN items.dateadded 
                         AND COALESCE(items.dateremoved,'2099-12-31')
ORDER BY 1,2

• The dates part is created containing all available dates, from the first dateadded to the last dateadded+1.
• The query will only select all available serialnumbers per date.

It should not be hard to change this to add also a column available to show if the serialnumber is available, or not, but this is left as an exercise to the reader 😉.