如何从当前人口普查中获得没有录取通知书的人口[SQL SEVER 2008] [英] How to get the population from current census who do not have the admission order [SQL SEVER 2008]

问题描述

我能够从当前的人口普查中获得入学顺序的人口，并分别从下面的查询中获得总人口普查。有一次，我可以得到总人口，并通过入学顺序减去人口，以获得没有入学顺序的人口，但现在我需要安排这个报告每天运行，我尝试使用NOT IN，LEFT JOIN和NOT EXISTS但它不起作用。任何人都可以建议我这里做错了什么：



查询入院订单的患者：

I am able to get the population with admission order from current census and the total census from the queries below respectively. For one time I can get the total population and subtract the population with admission order to get the "population with no admission order" but now i need to schedule this report to run daily and I tried different ways using NOT IN, LEFT JOIN and NOT EXISTS but it is not working. Can any one please suggest me what I am doin wrong here:

Query for patient with admission order:

SELECT DISTINCT  a.PatientAccountID
,a.VisitStartDateTime


FROM PatientVisitInfo a with (nolock)
INNER JOIN HOrder  b with (nolock)
ON a.PatientVisit_oid=b.PatientVisit_oid


WHERE a.VisitTypeCode='IP'
AND a.VisitStartDateTime >= GETDATE()-1
AND(b.OrderAbbr ='A_AdmitObs'
or b.OrderAbbr ='A_AdmitInpt'
or b.OrderAbbr ='A_CngInpt'
or b.OrderAbbr ='A_CngObs')

ORDER BY  a.PatientAccountID

查询当前人口普查：

query for current census:

SELECT DISTINCT  a.PatientAccountID
,a.VisitStartDateTime


FROM PatientVisitInfo a with (nolock)
INNER JOIN HOrder  b with (nolock)
ON a.PatientVisit_oid=b.PatientVisit_oid

WHERE a.VisitTypeCode='IP'
AND a.VisitStartDateTime >= GETDATE()-1

ORDER BY  a.PatientAccountID

查询我试过没有入院许可的病人

Query I tried for patient with no admission order

SELECT DISTINCT  a.PatientAccountID
,a.VisitStartDateTime

FROM PatientVisitInfo a with (nolock)
INNER JOIN HOrder  b with (nolock)
ON a.PatientVisit_oid=b.PatientVisit_oid

WHERE a.VisitTypeCode='IP'
AND a.VisitStartDateTime >= GETDATE()-1
AND(b.OrderAbbr <>'A_AdmitObs'
or b.OrderAbbr <>'A_AdmitInpt'
or b.OrderAbbr <>'A_CngInpt'
or b.OrderAbbr <>'A_CngObs')

ORDER BY  a.PatientAccountID

但结果这个查询将给我一次总人口普查。



感谢您的帮助!!

BUT the results of this query will give me the total census.

Thank you for any help!!

推荐答案

当你说一个没有入院许可的病人时，你的Horder表中没有任何记录吗？如果是这种情况，你需要这样做，

Hi,

When you said a patient with no admission order, is it a patient without any records in your Horder table? if it is the case, you need to do this,

SELECT DISTINCT  a.PatientAccountID
,a.VisitStartDateTime

FROM PatientVisitInfo a with (nolock)
LEFT JOIN HOrder  b with (nolock)
ON a.PatientVisit_oid=b.PatientVisit_oid

WHERE a.VisitTypeCode='IP'
AND a.VisitStartDateTime >= GETDATE()-1
AND b.PatientVisit_oid IS NULL

ORDER BY  a.PatientAccountID

如果你想要患者没有b.OrderAbbr，使用子查询并离开加入。

Now if you want the patient not having b.OrderAbbr, use a sub query and left join.

SELECT DISTINCT  a.PatientAccountID
,a.VisitStartDateTime

FROM PatientVisitInfo a with (nolock)
LEFT JOIN (SELECt * FROM HOrder  with (nolock)
WHERE b.OrderAbbr = 'A_AdmitObs'
or b.OrderAbbr = 'A_AdmitInpt'
or b.OrderAbbr = 'A_CngInpt'
or b.OrderAbbr = 'A_CngObs') AS B
ON a.PatientVisit_oid=b.PatientVisit_oid

WHERE a.VisitTypeCode='IP'
AND a.VisitStartDateTime >= GETDATE()-1
AND b.PatientVisit_oid IS NULL

ORDER BY  a.PatientAccountID

让我知道哪一个适合你。

Let me know which one could work for you.

这篇关于如何从当前人口普查中获得没有录取通知书的人口[SQL SEVER 2008]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！