Hospital readmission history in SQL Server
I have a table which holds hospital admission date, discharge date and the diagnosis:
Now I am trying to get count of readmission within last one year and the history for each encounter (Discharge date + Diagnosis). The outcome should looks something like this:
I am able to get the count but struggling with the history. This is my code:
SELECT
A.Encounter, A.Patient, A.AdmissionDt, A.DischargeDt,
(SELECT COUNT(*)
FROM MyTable B
WHERE A.Patient = B.Patient
AND B.AdmissionDt >= DATEADD(YY, -1, A.DischargeDt)
AND B.AdmissionDt < A.DischargeDt) AS Cnt
FROM
MyTable A
ORDER BY
AdmissionDt DESC
Can I get some help please?
sql
sql-server edited Dec 27 '18 at 15:22
marc_s
570k12811031251
asked Dec 27 '18 at 15:07
Monte
3914
add a comment |
I have a table which holds hospital admission date, discharge date and the diagnosis:
Now I am trying to get count of readmission within last one year and the history for each encounter (Discharge date + Diagnosis). The outcome should looks something like this:
I am able to get the count but struggling with the history. This is my code:
SELECT
A.Encounter, A.Patient, A.AdmissionDt, A.DischargeDt,
(SELECT COUNT(*)
FROM MyTable B
WHERE A.Patient = B.Patient
AND B.AdmissionDt >= DATEADD(YY, -1, A.DischargeDt)
AND B.AdmissionDt < A.DischargeDt) AS Cnt
FROM
MyTable A
ORDER BY
AdmissionDt DESC
Can I get some help please?
sql
sql-server edited Dec 27 '18 at 15:22
marc_s
570k12811031251
asked Dec 27 '18 at 15:07
Monte
3914
Can use STRING_AGG aggregate function to build the concatenated diagnosis history if you are using SQL Server 2017, otherwise will have to use a correlated subquery withFOR XMLoption.
– EzLo
Dec 27 '18 at 15:15
Are you trying to get history for display? If this is a display, you probably don't want to concat a string in SQL. Format the display of multiple records in your output code.
– Shawn
Dec 27 '18 at 15:23
What version of SQL?
– Shawn
Dec 27 '18 at 15:24
And will this be done for just one patient, or will you be looking at multiple patients?
– Shawn
Dec 27 '18 at 15:25
add a comment |
I have a table which holds hospital admission date, discharge date and the diagnosis:
Now I am trying to get count of readmission within last one year and the history for each encounter (Discharge date + Diagnosis). The outcome should looks something like this:
I am able to get the count but struggling with the history. This is my code:
SELECT
A.Encounter, A.Patient, A.AdmissionDt, A.DischargeDt,
(SELECT COUNT(*)
FROM MyTable B
WHERE A.Patient = B.Patient
AND B.AdmissionDt >= DATEADD(YY, -1, A.DischargeDt)
AND B.AdmissionDt < A.DischargeDt) AS Cnt
FROM
MyTable A
ORDER BY
AdmissionDt DESC
Can I get some help please?
sql
sql-server edited Dec 27 '18 at 15:22
marc_s
570k12811031251
asked Dec 27 '18 at 15:07
Monte
3914
I have a table which holds hospital admission date, discharge date and the diagnosis:
Now I am trying to get count of readmission within last one year and the history for each encounter (Discharge date + Diagnosis). The outcome should looks something like this:
I am able to get the count but struggling with the history. This is my code:
SELECT
A.Encounter, A.Patient, A.AdmissionDt, A.DischargeDt,
(SELECT COUNT(*)
FROM MyTable B
WHERE A.Patient = B.Patient
AND B.AdmissionDt >= DATEADD(YY, -1, A.DischargeDt)
AND B.AdmissionDt < A.DischargeDt) AS Cnt
FROM
MyTable A
ORDER BY
AdmissionDt DESC
Can I get some help please?
sql
sql-server sql
sql-server edited Dec 27 '18 at 15:22
marc_s
570k12811031251
asked Dec 27 '18 at 15:07
Monte
3914
edited Dec 27 '18 at 15:22
marc_s
570k12811031251
asked Dec 27 '18 at 15:07
Monte
3914
edited Dec 27 '18 at 15:22
marc_s
570k12811031251
edited Dec 27 '18 at 15:22
marc_s
570k12811031251
edited Dec 27 '18 at 15:22
marc_s
570k12811031251
570k12811031251
asked Dec 27 '18 at 15:07
Monte
3914
asked Dec 27 '18 at 15:07
Monte
3914
asked Dec 27 '18 at 15:07
Monte
3914
3914
Can use STRING_AGG aggregate function to build the concatenated diagnosis history if you are using SQL Server 2017, otherwise will have to use a correlated subquery withFOR XMLoption.
– EzLo
Dec 27 '18 at 15:15
Are you trying to get history for display? If this is a display, you probably don't want to concat a string in SQL. Format the display of multiple records in your output code.
– Shawn
Dec 27 '18 at 15:23
What version of SQL?
– Shawn
Dec 27 '18 at 15:24
And will this be done for just one patient, or will you be looking at multiple patients?
– Shawn
Dec 27 '18 at 15:25
add a comment |
Can use STRING_AGG aggregate function to build the concatenated diagnosis history if you are using SQL Server 2017, otherwise will have to use a correlated subquery withFOR XMLoption.
– EzLo
Dec 27 '18 at 15:15
Are you trying to get history for display? If this is a display, you probably don't want to concat a string in SQL. Format the display of multiple records in your output code.
– Shawn
Dec 27 '18 at 15:23
What version of SQL?
– Shawn
Dec 27 '18 at 15:24
And will this be done for just one patient, or will you be looking at multiple patients?
– Shawn
Dec 27 '18 at 15:25
Can use STRING_AGG aggregate function to build the concatenated diagnosis history if you are using SQL Server 2017, otherwise will have to use a correlated subquery with
FOR XML option.– EzLo
Dec 27 '18 at 15:15
Can use STRING_AGG aggregate function to build the concatenated diagnosis history if you are using SQL Server 2017, otherwise will have to use a correlated subquery with
FOR XML option.– EzLo
Dec 27 '18 at 15:15
Are you trying to get history for display? If this is a display, you probably don't want to concat a string in SQL. Format the display of multiple records in your output code.
– Shawn
Dec 27 '18 at 15:23
Are you trying to get history for display? If this is a display, you probably don't want to concat a string in SQL. Format the display of multiple records in your output code.
– Shawn
Dec 27 '18 at 15:23
What version of SQL?
– Shawn
Dec 27 '18 at 15:24
What version of SQL?
– Shawn
Dec 27 '18 at 15:24
And will this be done for just one patient, or will you be looking at multiple patients?
– Shawn
Dec 27 '18 at 15:25
And will this be done for just one patient, or will you be looking at multiple patients?
– Shawn
Dec 27 '18 at 15:25
add a comment |
2 Answers
2
active
oldest
votes
You can get both of your desired columns from correlated subqueries.
The count column would simply be a COUNT(*) of rows for the same patient before the current row but within the last year.
The History would be a FOR XML concatenation of the same set of rows as the count (before the current row but within the last year).
answered Dec 27 '18 at 15:14
Tab Alleman
25.7k52440
add a comment |
Can you try this query
SELECT A.encounter,
A.patient,
A.admissiondt,
A.dischargedt,
(SELECT Count(*)
FROM mytable B
WHERE A.patient = B.patient
AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt)
AND B.admissiondt < A.dischargedt) AS Cnt,
(SELECT convert (varchar,b.admissiondt) +' - ' + b.diagnosis +'
'
FROM mytable B
WHERE A.patient = B.patient
AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt)
AND B.admissiondt < A.dischargedt for xml path ('')) as history
FROM mytable A
ORDER BY admissiondt DESC
answered Dec 27 '18 at 15:20
Anson Aricatt
25313
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53947115%2fhospital-readmission-history-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can get both of your desired columns from correlated subqueries.
The count column would simply be a COUNT(*) of rows for the same patient before the current row but within the last year.
The History would be a FOR XML concatenation of the same set of rows as the count (before the current row but within the last year).
answered Dec 27 '18 at 15:14
Tab Alleman
25.7k52440
add a comment |
You can get both of your desired columns from correlated subqueries.
The count column would simply be a COUNT(*) of rows for the same patient before the current row but within the last year.
The History would be a FOR XML concatenation of the same set of rows as the count (before the current row but within the last year).
answered Dec 27 '18 at 15:14
Tab Alleman
25.7k52440
add a comment |
You can get both of your desired columns from correlated subqueries.
The count column would simply be a COUNT(*) of rows for the same patient before the current row but within the last year.
The History would be a FOR XML concatenation of the same set of rows as the count (before the current row but within the last year).
answered Dec 27 '18 at 15:14
Tab Alleman
25.7k52440
You can get both of your desired columns from correlated subqueries.
The count column would simply be a COUNT(*) of rows for the same patient before the current row but within the last year.
The History would be a FOR XML concatenation of the same set of rows as the count (before the current row but within the last year).
answered Dec 27 '18 at 15:14
Tab Alleman
25.7k52440
answered Dec 27 '18 at 15:14
Tab Alleman
25.7k52440
answered Dec 27 '18 at 15:14
Tab Alleman
25.7k52440
answered Dec 27 '18 at 15:14
Tab Alleman
25.7k52440
25.7k52440
add a comment |
add a comment |
Can you try this query
SELECT A.encounter,
A.patient,
A.admissiondt,
A.dischargedt,
(SELECT Count(*)
FROM mytable B
WHERE A.patient = B.patient
AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt)
AND B.admissiondt < A.dischargedt) AS Cnt,
(SELECT convert (varchar,b.admissiondt) +' - ' + b.diagnosis +'
'
FROM mytable B
WHERE A.patient = B.patient
AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt)
AND B.admissiondt < A.dischargedt for xml path ('')) as history
FROM mytable A
ORDER BY admissiondt DESC
answered Dec 27 '18 at 15:20
Anson Aricatt
25313
add a comment |
Can you try this query
SELECT A.encounter,
A.patient,
A.admissiondt,
A.dischargedt,
(SELECT Count(*)
FROM mytable B
WHERE A.patient = B.patient
AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt)
AND B.admissiondt < A.dischargedt) AS Cnt,
(SELECT convert (varchar,b.admissiondt) +' - ' + b.diagnosis +'
'
FROM mytable B
WHERE A.patient = B.patient
AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt)
AND B.admissiondt < A.dischargedt for xml path ('')) as history
FROM mytable A
ORDER BY admissiondt DESC
answered Dec 27 '18 at 15:20
Anson Aricatt
25313
add a comment |
Can you try this query
SELECT A.encounter,
A.patient,
A.admissiondt,
A.dischargedt,
(SELECT Count(*)
FROM mytable B
WHERE A.patient = B.patient
AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt)
AND B.admissiondt < A.dischargedt) AS Cnt,
(SELECT convert (varchar,b.admissiondt) +' - ' + b.diagnosis +'
'
FROM mytable B
WHERE A.patient = B.patient
AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt)
AND B.admissiondt < A.dischargedt for xml path ('')) as history
FROM mytable A
ORDER BY admissiondt DESC
answered Dec 27 '18 at 15:20
Anson Aricatt
25313
Can you try this query
SELECT A.encounter,
A.patient,
A.admissiondt,
A.dischargedt,
(SELECT Count(*)
FROM mytable B
WHERE A.patient = B.patient
AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt)
AND B.admissiondt < A.dischargedt) AS Cnt,
(SELECT convert (varchar,b.admissiondt) +' - ' + b.diagnosis +'
'
FROM mytable B
WHERE A.patient = B.patient
AND B.admissiondt >= Dateadd(yy, -1, A.dischargedt)
AND B.admissiondt < A.dischargedt for xml path ('')) as history
FROM mytable A
ORDER BY admissiondt DESC
answered Dec 27 '18 at 15:20
Anson Aricatt
25313
answered Dec 27 '18 at 15:20
Anson Aricatt
25313
answered Dec 27 '18 at 15:20
Anson Aricatt
25313
answered Dec 27 '18 at 15:20
Anson Aricatt
25313
25313
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53947115%2fhospital-readmission-history-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can use STRING_AGG aggregate function to build the concatenated diagnosis history if you are using SQL Server 2017, otherwise will have to use a correlated subquery with
FOR XMLoption.– EzLo
Dec 27 '18 at 15:15
Are you trying to get history for display? If this is a display, you probably don't want to concat a string in SQL. Format the display of multiple records in your output code.
– Shawn
Dec 27 '18 at 15:23
What version of SQL?
– Shawn
Dec 27 '18 at 15:24
And will this be done for just one patient, or will you be looking at multiple patients?
– Shawn
Dec 27 '18 at 15:25