How to count number of days in this month that a patient has been admitted

Question

I need to produce an MS SQL Server query to count the number of days that a patient has been admitted for the current month however the issue I have is working out how to do this when a patient may have been admitted for months previous.

I've tried to use DATEDIFF to work out the number of days but can only get the total number of days from admission to discharge or the current date if they have not been discharged yet.

I have a single table that contains the admission details:

PatientID AdmissionDate DischargeDate WardID

Example:

Patient admitted on 01/01/2019
Patient discharged on 10/03/2019
Report run for Feb and would return that the patients was admitted 28 days through Feb.

update your question add that the related tables schema a proper data sample and the expected result — ScaisEdge, Feb 18 '19 at 16:26
https://stackoverflow.com/questions/11628376/sql-server-2005-arithmetic-with-dates — JGFMK, Feb 18 '19 at 16:28
Why can't you use DATEDIFF from the first of the month? It works the same. — Tab Alleman, Feb 18 '19 at 16:28

score 2 · Answer 1 · answered Feb 18 '19 at 16:46

You need to limit your DATEDIFF in a way that only takes into account days from the month you need. You can do this with a CASE.

DECLARE @PatientInfo TABLE (
    AdmissionDate DATE,
    DischargeDate DATE)

INSERT INTO @PatientInfo (
    AdmissionDate,
    DischargeDate)
VALUES
    ('2019-01-01', '2019-03-10'),
    ('2019-02-15', '2019-02-17'),
    ('2019-02-25', '2019-05-01'),
    ('2019-01-05', '2019-01-06')

DECLARE @YearFilter INT = 2019
DECLARE @MonthFilter INT = 2 -- 2: February

DECLARE @StartOfMonth DATE = DATEFROMPARTS(@YearFilter, @MonthFilter, 1)
DECLARE @EndOfMonth DATE = DATEADD(DAY, -1, DATEADD(MONTH, 1, @StartOfMonth))

SELECT
    P.*,
    FilteredMonthDays = 1 + DATEDIFF(
        DAY,
        CASE WHEN P.AdmissionDate < @StartOfMonth THEN @StartOfMonth ELSE P.AdmissionDate END,
        CASE WHEN P.DischargeDate > @EndOfMonth THEN @EndOfMonth ELSE P.DischargeDate END)
FROM
    @PatientInfo AS P
WHERE
    @MonthFilter BETWEEN MONTH(P.AdmissionDate) AND MONTH(P.DischargeDate)

Result:

AdmissionDate   DischargeDate   FilteredMonthDays
2019-01-01      2019-03-10      28
2019-02-15      2019-02-17      3
2019-02-25      2019-05-01      4

forpas · Accepted Answer · 2019-02-18T17:57:51.903

You need to take into account several conditions in order to find the difference in days, this is why I use nested case statements to check them.
I use dateadd(mm, datediff(mm, 0, getdate()), 0) to get the 1st of the current month.

select 
  p.patientid, 
  case 
    when discharged < dateadd(mm, datediff(mm, 0, getdate()), 0) then -1
    else datediff(day, 
      case month(admitted) 
        when month(getdate()) then admitted
        else dateadd(mm, datediff(mm, 0, getdate()), 0)
      end,
      case 
        when discharged is null or discharged > getdate() then getdate()
        else discharged
      end
     )  
  end + 1 daysincurmonth  
from patients p

See the demo

How to count number of days in this month that a patient has been admitted

2 Answers2