rustypoot
asked on
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_PatientTyp e 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_PatientTyp e 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
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
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_PatientTyp
AND D.AdmitDateTime >= '01/01/2013'
AND (DATEDIFF(Day, R.AdmitDateTime, D.DischargeDateTime) BETWEEN 0 and 30)
AND R.AccountStatus_PatientTyp
--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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_DI S 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_PatientTyp e 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_PatientTyp e IN ('DIS_IN')
AND D.MRN = 'LU00247168'
GROUP BY
D.MRN
)
ORDER BY
D.INSTITUTION,
D.MRN,
D.Account,
D.AdmitDateTime
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_DI
--ON D.MRN = R.MRN
--LEFT OUTER JOIN dbo.MedDCStatusVisitPt_All
--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_PatientTyp
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_PatientTyp
AND D.MRN = 'LU00247168'
GROUP BY
D.MRN
)
ORDER BY
D.INSTITUTION,
D.MRN,
D.Account,
D.AdmitDateTime
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'
ASKER
Hi There, I figured it out! Thank you so much for your assistance.
Thanks
Thanks
Great
ASKER