• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

SQL Server Query

Hi,
I have the SQL below. It gives the list of Accounts that are recurring.  

SELECT Distinct
D.INSTITUTION
,D.MRN
,D.ACCOUNT
,D.AdmitDateTime
,D.DischargeDateTIME
FROM  
dbo.VisitPT_All_DIS D WITH (NOLOCK)
      INNER JOIN dbo.VisitPT_All_DIS R WITH (NOLOCK)
      ON D.MRN = R.MRN
      INNER JOIN dbo.MedDCStatusVisitPt_All_DIS M WITH (NOLOCK)
      ON R.Account = M.Account
      AND M.DischargeAction IN ('CONV', 'NEW')
      LEFT OUTER JOIN dbo.PROCVISITPT_ALL_DIS P WITH (NOLOCK)
    ON D.Account = P.Account
WHERE
D.AccountStatus_PatientType IN ('DIS_IN', 'DIS_INO', 'DEP_ER' )
AND D.AdmitDateTime >= '01/01/2013'
AND (DATEDIFF(Day, R.AdmitDateTime, D.DischargeDateTime) BETWEEN 0 and 30)
AND R.AccountStatus_PatientType IN ('DIS_IN', 'DIS_INO', 'DEP_ER')
--AND D.MRN = 'LU00247168'
ORDER BY
D.INSTITUTION,
D.MRN,
D.Account

I need to get the list of above MRN, and get ALL the previous acitivity. I need to loop it back to dbo.VisitPT_All_DIS view and get all the Historical data for the MRN #'s returned from the above query. How do I do this?
Thanks
0
rustypoot
Asked:
rustypoot
  • 3
  • 3
1 Solution
 
Tony303Commented:
Firstly,

The distinct is a inefficient tool
It would be better to remove the word distinct  and put in a Group by Clause..


GROUP BY
D.INSTITUTION
,D.MRN
,D.ACCOUNT
,D.AdmitDateTime 
,D.DischargeDateTIME 

Open in new window



Secondly,

A nested query perhaps could be the way to go...
Essentially the same MRN's from your query as the filter for the dbo.VisitPT_All_DIS view

Cheers.
T

SELECT *
FROM dbo.VisitPT_All_DIS 
WHERE MRN in 
--NESTED Query
(
SELECT 
D.MRN

FROM  
dbo.VisitPT_All_DIS D WITH (NOLOCK)
      INNER JOIN dbo.VisitPT_All_DIS R WITH (NOLOCK) 
      ON D.MRN = R.MRN 
      INNER JOIN dbo.MedDCStatusVisitPt_All_DIS M WITH (NOLOCK)
      ON R.Account = M.Account
      AND M.DischargeAction IN ('CONV', 'NEW')
      LEFT OUTER JOIN dbo.PROCVISITPT_ALL_DIS P WITH (NOLOCK)
    ON D.Account = P.Account
WHERE 
D.AccountStatus_PatientType IN ('DIS_IN', 'DIS_INO', 'DEP_ER' )
AND D.AdmitDateTime >= '01/01/2013' 
AND (DATEDIFF(Day, R.AdmitDateTime, D.DischargeDateTime) BETWEEN 0 and 30)
AND R.AccountStatus_PatientType IN ('DIS_IN', 'DIS_INO', 'DEP_ER')
--AND D.MRN = 'LU00247168'
GROUP BY
D.MRN)

Open in new window

0
 
rustypootAuthor Commented:
Thanks! Let me test this out and get back to you.
0
 
rustypootAuthor Commented:
Hello,

The nested Query returns 3 records and works fine. But, the Historical is not working correctly as it should pull 26 rows but its only pulling 9 rows! If there is any records in Nested query, it should ALL history for that record.

SELECT
--Historical Data for Recurring
D.INSTITUTION
,D.MRN
,D.ACCOUNT
,D.AdmitDateTime AS ADMITDATE
,D.DischargeDateTIME AS DISCHARGEDATE


FROM
dbo.VisitPT_All_DIS D WITH (NOLOCK)
      --INNER JOIN azViews.dbo.VisitPT_All_DIS R WITH (NOLOCK)
      --ON D.MRN = R.MRN
      --LEFT OUTER JOIN dbo.MedDCStatusVisitPt_All_DIS M WITH (NOLOCK)
      --ON D.Account = M.Account
      --LEFT OUTER JOIN dbo.PROCVISITPT_ALL_DIS P WITH (NOLOCK)
 --   ON D.Account = P.Account

WHERE
D.INSTITUTION IN ('COV')
 AND D.AccountStatus_PatientType IN ('DIS_IN', 'DIS_INO', 'DEP_ER' )
 AND D.MRN IN
--NESTED Query for Recurring Only
(
SELECT
D.MRN
FROM  
dbo.VisitPT_All_DIS D WITH (NOLOCK)
      INNER JOIN dbo.VisitPT_All_DIS R WITH (NOLOCK)
      ON D.MRN = R.MRN
      AND R.PatientType = 'IN'
      AND (R.[ADMITDATETIME] -D.[DISCHARGEDATETIME]) BETWEEN 0 and 30
WHERE
D.AccountStatus_PatientType IN ('DIS_IN')
AND D.MRN = 'LU00247168'
GROUP BY
D.MRN
)
ORDER BY
D.INSTITUTION,
D.MRN,
D.Account,
D.AdmitDateTime
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Tony303Commented:
Does this return the 27 rows?
SELECT D.INSTITUTION
,D.MRN
,D.ACCOUNT
,D.AdmitDateTime AS ADMITDATE
,D.DischargeDateTIME AS DISCHARGEDATE


FROM dbo.VisitPT_All_DIS D

WHERE 
D.INSTITUTION IN ('COV') 
 AND D.AccountStatus_PatientType IN ('DIS_IN', 'DIS_INO', 'DEP_ER' )
 AND D.MRN = 'LU00247168'

Open in new window

0
 
rustypootAuthor Commented:
Hi There, I figured it out! Thank you so much for your assistance.

Thanks
0
 
Tony303Commented:
Great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now