Solved

SQL Server Query

Posted on 2013-12-12
6
388 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

623 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