SQL Server query for calculating daily census

Question

I'm having a problem with a query that populates the daily census(# of current inpatients) for a hospital unit. This previous post is where I found the query.

SELECT 
    [date], COUNT (DISTINCT                                                                           
                       CASE WHEN admit_date <= [date] AND discharge_date >= [date] THEN id END)) AS census
FROM 
    dbo.patients, dbo.census
GROUP BY 
    [date]
ORDER BY 
    [date] 

There are 2 tables:

• dbo.patients with id, admit_date, and discharge_date columns
• dbo.census has a date column with every date since 2017, and a census column, which is blank

The query populates the census column, but the census count diminishes toward the end of the dates to smaller numbers then it should. For example, there are 65 null values for discharge_date, so there should be a census count of 65 for today's date, but the query produces a count of 8.

Answer 1

Probably need to account for NULL discharge date

SELECT [date], COUNT (DISTINCT                                                                           
CASE WHEN admit_date <= [date] AND COALESCE(discharge_date, GETDATE()) >= [date] THEN id END))
AS census

FROM dbo.patients
CROSS JOIN dbo.census

GROUP BY [date]
ORDER BY [date] 

That is, assuming [date] is some sort of current date/time stamp. Also, as per Sean Lange's comment, if you really want a CROSS JOIN then you should specify that in the query.