Styjun

What is this weird d12 for?

Why are lawsuits between the President and Congress not automatically sent to the Supreme Court

Does it matter what way the tires go if no directional arrow?

What do the "optional" resistor and capacitor do in this circuit?

How does Ctrl+c and Ctrl+v work?

How to not get blinded by an attack at dawn

My bread in my bread maker rises and then falls down just after cooking starts

How could it be that 80% of townspeople were farmers during the Edo period in Japan?

To whom did Varys write those letters in Game of Thrones S8E5?

UUID type for NEWID()

Is there an academic word that means "to split hairs over"?

Can anyone give me examples of the relative-determinative 'which'?

Can a tourist shoot a gun in the USA?

What was Varys trying to do at the beginning of S08E05?

Given 0s on Assignments with suspected and dismissed cheating?

What dog breeds survive the apocalypse for generations?

How do I know which cipher suites can be disabled?

Why is Drogon so much better in battle than Rhaegal and Viserion?

Holding rent money for my friend which amounts to over $10k?

Why did the metro bus stop at each railway crossing, despite no warning indicating a train was coming?

How to handle professionally if colleagues has referred his relative and asking to take easy while taking interview

How to describe a building set which is like LEGO without using the "LEGO" word?

Capital gains on stocks sold to take initial investment off the table

Can my American children re-enter the USA by International flight with a passport card? Being that their passport book has expired

Select date difference between two different rows

How can I remove duplicate rows?What is the difference between “INNER JOIN” and “OUTER JOIN”?Best way to get identity of inserted row?What is the difference between UNION and UNION ALL?How do I perform an IF…THEN in an SQL SELECT?How to return only the Date from a SQL Server DateTime datatypeInserting multiple rows in a single SQL query?How do I UPDATE from a SELECT in SQL Server?Select first row in each GROUP BY group?SQL select only rows with max value on a column

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I have a database of hospital visits and appointment visits in the same table. There are three relevant columns:

1. PAT_KEY, which is the patient key


2. HOSP_DISCHRG_DT, which is the date a patient was discharged from the hospital


3. APPT_CHECKIN_DT, which is the date a patient came in for a follow-up appointment

The issue I am coming across is that even though the patient key connects the two, the HOSP_DISCHRG_DT and APPT_CHECKIN_DT are never in the same row. So, if a patient came in for a hospital visit, and then came in a week later for a follow-up appointment, they are categorized as different visits and are thus in different rows. (Thus, it's not as simple as doing a DATEDIFF)

My goal:
I want to set up an indicator which is:

• 1 if the patient was seen for an appointment within 7 days of being discharged from the hospital visit.


• 0 if they were not seen for a follow-up appointment visit within 7 days.

Note: A patient who comes in at any time on the 7th day should be marked as a 1. For example, a patient who has a hospital visit on 2/6/2019 at 1AM who had a follow-up appt on 2/13/2019 at 3PM should be flagged as 1)

See below code for my attempt:

SELECT PAT_KEY, HOSP_DISCHRG_DT, APPT_CHECKIN_DT,

CASE 
 WHEN DATEDIFF(day, cast (APPT_CHECKIN_DT as datetime), cast (HOSP_DISCHRG_DT as datetime)) <= 7 THEN 1 
 ELSE 0 
 END AS diff

FROM dbo.visit


WHERE HOSP_ADMIT_DT != 'NA' AND HOSP_DISCHRG_DT != 'NA'
AND 
PAT_KEY IN (SELECT PAT_KEY FROM dbo.visit WHERE DICT_ENC_TYPE_KEY = 108)


ORDER BY PAT_KEY;

I expected to see a table with the patent key and when they came in for a visit, with another column 'diff' which was either a 1 or 0, and showed whether they had come in a week earlier for a visit. What I actually got was a table of zeros, unfortunately.

sql tsql

share|improve this question

edited Mar 23 at 17:50

a_horse_with_no_name

313k46475579

asked Mar 23 at 15:14

SKalafatSKalafat

1

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Sample data, desired results, and a db<>fiddle would all help.
  
  – Gordon Linoff
  Mar 23 at 15:43
  

  
  
  
  

add a comment | 

0

I have a database of hospital visits and appointment visits in the same table. There are three relevant columns:

1. PAT_KEY, which is the patient key


2. HOSP_DISCHRG_DT, which is the date a patient was discharged from the hospital


3. APPT_CHECKIN_DT, which is the date a patient came in for a follow-up appointment

The issue I am coming across is that even though the patient key connects the two, the HOSP_DISCHRG_DT and APPT_CHECKIN_DT are never in the same row. So, if a patient came in for a hospital visit, and then came in a week later for a follow-up appointment, they are categorized as different visits and are thus in different rows. (Thus, it's not as simple as doing a DATEDIFF)

My goal:
I want to set up an indicator which is:

• 1 if the patient was seen for an appointment within 7 days of being discharged from the hospital visit.


• 0 if they were not seen for a follow-up appointment visit within 7 days.

Note: A patient who comes in at any time on the 7th day should be marked as a 1. For example, a patient who has a hospital visit on 2/6/2019 at 1AM who had a follow-up appt on 2/13/2019 at 3PM should be flagged as 1)

See below code for my attempt:

SELECT PAT_KEY, HOSP_DISCHRG_DT, APPT_CHECKIN_DT,

CASE 
 WHEN DATEDIFF(day, cast (APPT_CHECKIN_DT as datetime), cast (HOSP_DISCHRG_DT as datetime)) <= 7 THEN 1 
 ELSE 0 
 END AS diff

FROM dbo.visit


WHERE HOSP_ADMIT_DT != 'NA' AND HOSP_DISCHRG_DT != 'NA'
AND 
PAT_KEY IN (SELECT PAT_KEY FROM dbo.visit WHERE DICT_ENC_TYPE_KEY = 108)


ORDER BY PAT_KEY;

I expected to see a table with the patent key and when they came in for a visit, with another column 'diff' which was either a 1 or 0, and showed whether they had come in a week earlier for a visit. What I actually got was a table of zeros, unfortunately.

sql tsql

share|improve this question

edited Mar 23 at 17:50

a_horse_with_no_name

313k46475579

asked Mar 23 at 15:14

SKalafatSKalafat

1

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Sample data, desired results, and a db<>fiddle would all help.
  
  – Gordon Linoff
  Mar 23 at 15:43
  

  
  
  
  

add a comment | 

I have a database of hospital visits and appointment visits in the same table. There are three relevant columns:

1. PAT_KEY, which is the patient key


2. HOSP_DISCHRG_DT, which is the date a patient was discharged from the hospital


3. APPT_CHECKIN_DT, which is the date a patient came in for a follow-up appointment

The issue I am coming across is that even though the patient key connects the two, the HOSP_DISCHRG_DT and APPT_CHECKIN_DT are never in the same row. So, if a patient came in for a hospital visit, and then came in a week later for a follow-up appointment, they are categorized as different visits and are thus in different rows. (Thus, it's not as simple as doing a DATEDIFF)

My goal:
I want to set up an indicator which is:

• 1 if the patient was seen for an appointment within 7 days of being discharged from the hospital visit.


• 0 if they were not seen for a follow-up appointment visit within 7 days.

Note: A patient who comes in at any time on the 7th day should be marked as a 1. For example, a patient who has a hospital visit on 2/6/2019 at 1AM who had a follow-up appt on 2/13/2019 at 3PM should be flagged as 1)

See below code for my attempt:

SELECT PAT_KEY, HOSP_DISCHRG_DT, APPT_CHECKIN_DT,

CASE 
 WHEN DATEDIFF(day, cast (APPT_CHECKIN_DT as datetime), cast (HOSP_DISCHRG_DT as datetime)) <= 7 THEN 1 
 ELSE 0 
 END AS diff

FROM dbo.visit


WHERE HOSP_ADMIT_DT != 'NA' AND HOSP_DISCHRG_DT != 'NA'
AND 
PAT_KEY IN (SELECT PAT_KEY FROM dbo.visit WHERE DICT_ENC_TYPE_KEY = 108)


ORDER BY PAT_KEY;

I expected to see a table with the patent key and when they came in for a visit, with another column 'diff' which was either a 1 or 0, and showed whether they had come in a week earlier for a visit. What I actually got was a table of zeros, unfortunately.

sql tsql

share|improve this question

edited Mar 23 at 17:50

a_horse_with_no_name

313k46475579

asked Mar 23 at 15:14

SKalafatSKalafat

1

SELECT PAT_KEY, HOSP_DISCHRG_DT, APPT_CHECKIN_DT,

CASE 
 WHEN DATEDIFF(day, cast (APPT_CHECKIN_DT as datetime), cast (HOSP_DISCHRG_DT as datetime)) <= 7 THEN 1 
 ELSE 0 
 END AS diff

FROM dbo.visit


WHERE HOSP_ADMIT_DT != 'NA' AND HOSP_DISCHRG_DT != 'NA'
AND 
PAT_KEY IN (SELECT PAT_KEY FROM dbo.visit WHERE DICT_ENC_TYPE_KEY = 108)


ORDER BY PAT_KEY;

share|improve this question

edited Mar 23 at 17:50

a_horse_with_no_name

313k46475579

asked Mar 23 at 15:14

SKalafatSKalafat

1

share|improve this question

edited Mar 23 at 17:50

a_horse_with_no_name

313k46475579

asked Mar 23 at 15:14

SKalafatSKalafat

1

edited Mar 23 at 17:50

a_horse_with_no_name

313k46475579

edited Mar 23 at 17:50

a_horse_with_no_name

313k46475579

asked Mar 23 at 15:14

SKalafatSKalafat

1

asked Mar 23 at 15:14

SKalafatSKalafat

1

1 Answer
1

active

oldest

votes

0

Try using a sub query to get each row needed and datediff it to get the value you're looking for..

share|improve this answer

answered Mar 23 at 15:45

Donnie NosayabaDonnie Nosayaba

364

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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55315190%2fselect-date-difference-between-two-different-rows%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Try using a sub query to get each row needed and datediff it to get the value you're looking for..

share|improve this answer

answered Mar 23 at 15:45

Donnie NosayabaDonnie Nosayaba

364

add a comment | 

0

Try using a sub query to get each row needed and datediff it to get the value you're looking for..

share|improve this answer

answered Mar 23 at 15:45

Donnie NosayabaDonnie Nosayaba

364

add a comment | 

Try using a sub query to get each row needed and datediff it to get the value you're looking for..

share|improve this answer

answered Mar 23 at 15:45

Donnie NosayabaDonnie Nosayaba

364

share|improve this answer

answered Mar 23 at 15:45

Donnie NosayabaDonnie Nosayaba

364

answered Mar 23 at 15:45

Donnie NosayabaDonnie Nosayaba

364

answered Mar 23 at 15:45

Donnie NosayabaDonnie Nosayaba

364