count of patients, for specific hourly periods between start and end dates

  • Hi there

    I'm a newbie to SQL and would appreciate some help with the above please.

    I have a table called 'spells' which contains thousands of rows of patient data

    In this table I have a 'date_of_admisson' field and a 'date of discharge' field in this format - 2016-04-01 04:00:00.000

    What I need to do is count how many patients are present on the ward at 18:00, 19:00, 20:00, 21:00, 22:00 and 23:00 hours specifically each day

    Please bare in mind that the difference between 'date of admission' and 'date_of_admission' fields for each patient could be several hours or several days. Either way I would need a count of how many patients were present at these times during their period on the ward.

    Any help very much appreciated