Solved

SQL Server Query

Posted on 2013-12-12
6
373 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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. …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now