Let’s take a look at this request from a detailed and summary angle.
SELECT
Lastname,
firstname,
id,
COALESCE(provcode,'None') AS "Provider",
Intakedate,dischargedate,
DateDiff(day,intakedate,dischargedate) AS "Days"
FROM
sos.patients a
JOIN sos.providers b ON a.providernum=b.providernum
WHERE
intakedate IS NOT NULL
and dischargedate IS NOT NULL
....will give you a list of patients, their intake and discharge dates, and the number of days between those dates. Extending that to give us a summary, with provider, average length of stay, and the number of patients from which the statistics are derived:
SELECT
COALESCE(provcode,'None') AS "Provider",
COUNT(*) AS "N",
AVG(DateDiff(day,intakedate,dischargedate)) AS "Days"
FROM
sos.patients a
JOIN sos.providers b ON a.providernum=b.providernum
WHERE
intakedate IS NOT NULL
and dischargedate IS NOT NULL
GROUP BY
"Provider"
ORDER BY
"Provider"
admin says:
Would that translate to clients who were discharged during that date range? If so, you can just change the dischargedate line in the WHERE clause to…
AND dischargedate BETWEEN ‘2011-12-01’ AND ‘2012-11-16’
Bart Rauluk says:
I would like to know the length of stay for active clients during the period 12/01/2011 through 11/16/2012. How can I modify this query to obtain the result?
Bart Rauluk
(412) 441-9786 x213