SQL - 2010 年仅进行过牙科检查的所有患者

发布于 2024-10-08 08:54:56 字数 706 浏览 5 评论 0原文

我有一个 T-SQL 查询，该查询为我提供了 2010 年最近一次就诊的信息，但不是牙科就诊。查询的相关部分是：

AND pv.PatientVisitId IN (                
    SELECT Max(pv1.PatientVisitID)            
        FROM PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName NOT LIKE '%Dental%'    
    )

现在，我想翻转一下，以获取仅进行过牙科预约的患者最近一次就诊的信息。不过，我一直在这里碰壁。谁能带我到另一边吗？ :-)

原文

I have a T-SQL query that's giving me the most recent office visit in 2010 that wasn't a dental visit. The relevant part of the query is:

AND pv.PatientVisitId IN (                
    SELECT Max(pv1.PatientVisitID)            
        FROM PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName NOT LIKE '%Dental%'    
    )

Now, I want to flip that around to get the most recent office visit for the patients who only had dental appointments. I keep hitting the wall here, though. Can anyone bust me through to the other side? :-)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

牵你的手，一向走下去 2024-10-15 08:54:56

线索是使用不存在的地方

AND pv.PatientVisitId IN (                
    SELECT Max(pv1.PatientVisitID)            
        FROM PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName LIKE '%Dental%'    
    ) 
and not exists ( Select 1 from   PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName NOT LIKE '%Dental%'    
    )

The clue is to use where not exists

AND pv.PatientVisitId IN (                
    SELECT Max(pv1.PatientVisitID)            
        FROM PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName LIKE '%Dental%'    
    ) 
and not exists ( Select 1 from   PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName NOT LIKE '%Dental%'    
    )

回复收藏 0 原文

情深缘浅 2024-10-15 08:54:56

可能有很多人这样做，但我脑海中浮现的第一种方法是将病人访问的计数（1）与病人访问的计数（1）进行比较，其中 df1.listname 就像'％Dental％'，如果它们相等，然后他们就只去看牙医了。

回复收藏 0 原文

圈圈圆圆圈圈 2024-10-15 08:54:56

如果您使用的是 SQL Server 2005 或更高版本，则可以使用 EXCEPT 从另一个查询中排除一组记录。

因此，基本上编写较大的查询（向我显示今年访问过的所有患者！），然后...

...
EXCEPT
SELECT pv1.PatientVisitID        
        FROM PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName NOT LIKE '%Dental%'

需要记住的是，您需要在两个查询中具有相同的列 （可能存在一些差异，但出于我们的目的，请确保第一个 SELECT 和 EXCEPT 查询返回相同的字段）。

If you are using SQL Server 2005 or later, you can use EXCEPT to exclude a group of records from another query.

So basically write your larger query (show me ALL the patients that had a visit this year!), then...

...
EXCEPT
SELECT pv1.PatientVisitID        
        FROM PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName NOT LIKE '%Dental%'

Something to bear in mind with this is you need to have the same columns in both queries (there can be SOME differences, but for our purposes make sure both the first SELECT and the EXCEPT query return the same fields).

回复收藏 0 原文

千笙结 2024-10-15 08:54:56

AND pv.PatientVisitId IN (              
  SELECT MAX(pv1.PatientVisitID) 
  FROM PatientVisit pv1                
    JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID        
    JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId        
    JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId       
  WHERE pv1.PatientProfileID = pp.PatientProfileID                
    AND pv1.Visit < '2011-01-01'            
  HAVING SUM(CASE WHEN df1.ListName LIKE '%Dental%' THEN 1 ELSE 0 END) = COUNT(*)
)

AND pv.PatientVisitId IN (              
  SELECT MAX(pv1.PatientVisitID) 
  FROM PatientVisit pv1                
    JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID        
    JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId        
    JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId       
  WHERE pv1.PatientProfileID = pp.PatientProfileID                
    AND pv1.Visit < '2011-01-01'            
  HAVING SUM(CASE WHEN df1.ListName LIKE '%Dental%' THEN 1 ELSE 0 END) = COUNT(*)
)

回复收藏 0 原文

~没有更多了~