This is for an airport parking lot using SQL Server. All parkers prepay when they arrive for the duration of their stay (multiple days). Parking tickets are stored in a detail table with the PurchasedDate and ExpirationDate. I created a summary table that needs to include every calendar day and a count of the number of parking tickets valid for each day. I can get the count of vehicles with the following code, but am stuck on how to update my summary table.
SELECT P.Occupancy_Date, P.Parking_Lot, P.Peak_Occupancy, COUNT(*) AS Car_Count --(This is the value I want to use to update the Peak_Occupancy field)
FROM Parking_Peak_Occupancy P
INNER JOIN Parking_Transactions T ON CAST(T.PurchasedDate AS DATE)<=P.Occupancy_Date AND T.ExpirationDate>=P.Occupancy_Date
GROUP BY P.Occupancy_Date, P.Parking_Lot, P.Peak_Occupancy;