May 24, 2016 at 12:23 pm
I'd like to get a count of visits per patient before a specific patient visit date, particularly the date of their mortality. The query below gives me a list of each mortality and their mortality date. I need a count of visits going back one year from their a.DCDate. The data will come from the same table. Any help would be much appreciated.
SELECT a.PatientNumber,a.DCDate
FROM [PatientData] a
where a.DCDispCode = '20'
and a.DivisionCode in ('A','B','C')
and a.DCDate>='2015-01-01' and a.DCDate<'2016-01-01'
order by a.DCDate
Desired outcome
a.PatientNumber, a.DCDate, Count(a.PatientNumber) as PatientVisits
May 24, 2016 at 12:40 pm
assuming DCDate is the Deceased Date, and you can only die once(except in the movies)
here's my best guess:
SELECT
a.PatientNumber,
MAX(a.DCDate) As DCDate,
Count(a.PatientNumber) as PatientVisits
FROM [PatientData] a
where a.DCDispCode = '20'
and a.DivisionCode in ('A','B','C')
and a.DCDate>='2015-01-01' and a.DCDate<'2016-01-01'
GROUP BY a.PatientNumber
order by a.DCDate
Lowell
May 24, 2016 at 12:50 pm
thanks so much for your response Lowell. If, for example, a person who dies on Jan 1, 2015, I would need a count of visits from Jan 1, 2014 through Dec 31st, 2014. If they die on Sept 1, 2015, I would need a count of visits from Sept 1, 2014 through October 31, 2015.
May 24, 2016 at 1:04 pm
boehnc (5/24/2016)
thanks so much for your response Lowell. If, for example, a person who dies on Jan 1, 2015, I would need a count of visits from Jan 1, 2014 through Dec 31st, 2014. If they die on Sept 1, 2015, I would need a count of visits from Sept 1, 2014 through October 31, 2015.
which Column is the VisitDate vs The DeceasedDate?
you just need to use some date math:
AND VisitDate Between DATEADD(year,-1,DeceasedDate AND DeceasedDate
Lowell
May 24, 2016 at 1:53 pm
thank you..
DCDate is actually Discharge Date. All visits have discharge dates, but in the query I provided the DCDispCode = 20 is actually the code for diseased.
They could have multiple DCDispCodes with DC dates but obviously the "20" would be the last discharge disp code and visit date.
May 24, 2016 at 2:24 pm
in that case, your count, as you currently have it, would always be one, since the WHERE statement is limiting data to the same day the person died.
you have to join the table against itself, I think
SELECT
a.PatientNumber,
MAX(a.DCDate) As DCDate,
Count(a.PatientNumber) as PatientVisits
FROM [PatientData] a
INNER JOIN (SELECT x.PatientNumber,x.DCDate As DeceasedDate
FROM [PatientData] x
WHERE a.PatientNumber = x.PatientNumber
AND x.DCDispCode = '20'
AND x.DivisionCode in ('A','B','C')) Deceased
ON a.PatientNumber = Deceased.PatientNumber
where a.DivisionCode in ('A','B','C')
AND DCDate Between DATEADD(year,-1,Deceased.DeceasedDate) AND Deceased.DeceasedDate
GROUP BY a.PatientNumber
order by DCDate DESC
Lowell
May 24, 2016 at 3:37 pm
I believe the following will work in SQL2008. I don't have a SQL2008 test environment handy to test.
;
WITH pd AS (
SELECT
pd.PatientNumber,
pd.DCDispCode,
pd.DCDate,
MAX(CASE WHEN pd.DCDispCode = '20' THEN pd.DCDate END) OVER(PARTITION BY pd.PatientNumber) AS DeceasedDate
FROM PatientData pd
WHERE DivisionCode IN ('A', 'B', 'C')
)
SELECT
PatientNumber,
MAX(DeceasedDate) AS DeceasedDate,
COUNT(PatientNumber) AS PatientVisits
FROM pd
WHERE DCDate BETWEEN DATEADD(YEAR, -1, DeceasedDate) AND pd.DeceasedDate
GROUP BY PatientNumber
I think that this might perform better than the self-join.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 24, 2016 at 3:55 pm
You might want to consider adding a flitered index on deceased date including the PatientNumber and DivisionCode. Then you can do an easy self-join, which I think should be efficient as well.
SELECT d.DCDate, d.PatientNumber, p.* --,...
FROM [PatientData] d
INNER JOIN [PatientData] p ON p.PatientNumber = d.PatientNumber AND p.VisitDate BETWEEN DATEADD(YEAR, -1, d.DCDate) AND d.DCDate
WHERE d.DCDate IS NOT NULL AND d.DivisionCode IN ('A', 'B', 'C')
ORDER BY d.DCDate
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply