Solved

SQL Server Query

Posted on 2013-12-12
6
385 Views
Last Modified: 2013-12-13
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
Comment
Question by:rustypoot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 12

Accepted Solution

by:
Tony303 earned 500 total points
ID: 39714983
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
 

Author Comment

by:rustypoot
ID: 39715481
Thanks! Let me test this out and get back to you.
0
 

Author Comment

by:rustypoot
ID: 39715606
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 12

Expert Comment

by:Tony303
ID: 39715620
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
 

Author Comment

by:rustypoot
ID: 39717418
Hi There, I figured it out! Thank you so much for your assistance.

Thanks
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39717641
Great
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question