Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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