Question

我的表格称为<编码>Visits:

ID(PKey,Int)
PatiendID(int),
DoctorID(int),
ExpectedDate(date),
ActualDate(date),

我怎么能写一个问,对于每个病人来说,重复访问的次数会回来。

如果访问少于前一年的一半,则认为访问会重复进行。

换言之,我需要每个病人在(<>上>访问-6个月)和最后一次访问(不包括最后一次访问)之间找到访问次数。能否在没有治疗者的情况下撰写询问?

And other question. I have Table Patients ID(int, PKey),Name,Pasport,State Is it possible for each client from here find the same information? Or it is better union the query below and all clients that are not in visits table?

Answer 1

In other words I need for each patient find number of visits between (last visit-6 months) and last visit excluding last visit.

WITH CTE AS
(
    SELECT PatientID, MAX(ActualDate) AS LastVisit
    FROM Visits
    GROUP BY PatientID
)
SELECT CTE.PatientID, COUNT(1)-1 AS RepeatVisits
FROM CTE INNER JOIN Visits ON CTE.PatientID = Visits.PatientID
WHERE ActualDate BETWEEN DATEADD(month, -6, LastVisit) AND LastVisit
GROUP BY CTE.PatientId

这将按病人分类,在上一次访问之前六个月内进行所有访问。它将给你(总数)减去(不包括最后一次访问)。

完整的实例:

第二阶段见:。

WITH CTE AS
(
    SELECT Patients.PatientID, Patients.Name, MAX(ActualDate) AS LastVisit
    FROM Patients LEFT JOIN Visits ON #patient.PatientID = Visits.PatientID
    GROUP BY Patients.PatientID, Patients.Name
)
SELECT CTE.PatientID, CTE.Name, COUNT(1)-1 AS RepeatVisits
FROM CTE LEFT JOIN Visits ON CTE.PatientID = Visits.PatientID
                          AND Visits.ActualDate BETWEEN DATEADD(month, -6, LastVisit) AND LastVisit
GROUP BY CTE.PatientId, CTE.Name
ORDER BY PatientID;

友情链接