从日期范围返回单个患者的多次出现的行天数总计

I am extremely new at this but I have been able to work out how to return the total number of line days for a particular patient for an individual parameter. See Current Results Data Table.

我对此非常陌生，但我已经能够找出如何为特定患者返回单个参数的总行数。请参见当前结果数据表。

Each patient is only counted once, irrespective of how many lines they have in. The problem I have is that when line days overlap. I need to be able to return the total number of line days for a patient.

每个患者只计算一次，无论他们有多少行。我遇到的问题是当行天重叠时。我需要能够返回患者的总行数。

Final Column in Table has been manually added.

表中的最终列已手动添加。

Current Results:

目前的结果：

SELECT        
PendingRangeSignals.PendingOrderID, 
PendingOrders.PatientID, 
Parameters.ParameterName, 


(SELECT CONVERT(VARCHAR, PendingRangeSignals.StartTime, 103) + ' ' + CONVERT(VARCHAR, DATEPART(hh, PendingRangeSignals.StartTime)) + ':' + RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, PendingRangeSignals.StartTime)), 2) AS Date) as "Start Time Column",

(SELECT CONVERT(VARCHAR, RangeSignals.EndTime, 103) + ' ' + CONVERT(VARCHAR, DATEPART(hh, RangeSignals.EndTime)) + ':' + RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, RangeSignals.EndTime)), 2) AS Date) as "End Time Column",

iif(datepart(hh,PendingRangeSignals.StartTime)>= 0 OR 

CASE
    WHEN RangeSignals.EndTime IS NULL
    THEN datediff(hh,PendingRangeSignals.StartTime,isnull(RangeSignals.EndTime,getdate()))
    ELSE datediff(hh,PendingRangeSignals.StartTime,RangeSignals.EndTime)
END < 24,

datediff(d,iif(PendingRangeSignals.StartTime < DATEADD(month, DATEDIFF(month, 0, getdate()), 0), DATEADD(month, DATEDIFF(month, 0, getdate()), 0),PendingRangeSignals.StartTime)  , dateadd(day,1,isnull(RangeSignals.EndTime,getdate()))),
 datediff(d,iif(PendingRangeSignals.StartTime < DATEADD(month, DATEDIFF(month, 0, getdate()), 0), DATEADD(month, DATEDIFF(month, 0, getdate()), 0),PendingRangeSignals.StartTime)  , isnull(RangeSignals.EndTime,getdate()))) as "LineDaysinMonth"

FROM
PendingRangeSignals INNER JOIN
PendingOrders ON PendingRangeSignals.PendingOrderID = PendingOrders.PendingOrderID INNER JOIN
Parameters ON PendingRangeSignals.ParameterID = Parameters.ParameterID
LEFT JOIN RangeSignals ON RangeSignals.ParameterID = Parameters.ParameterID and RangeSignals.PatientID = PendingOrders.PatientID and PendingRangeSignals.StartTime = RangeSignals.StartTime

WHERE
(PendingOrders.PatientID = 2105) and pendingorders.Status = 5  and ((datepart(month,RangeSignals.EndTime) = datepart(month,getdate())) or RangeSignals.EndTime is null) and [Parameters].ParameterID IN (1046, 1372, 8546, 1051, 8532, 8538, 1375, 8531, 8888)

1 个解决方案

#1

From what I understand now, I would suggest:

根据我现在的理解，我建议：

1) create a table "days" with numbers 0-31: days(day)

1）创建一个表“天”，数字0-31：天（天）

2) create a subquery that returns the calendar days for the month you want, e.g., a simplified example for months with 31 days.

2）创建一个子查询，返回您想要的月份的日历天数，例如，31天的月份的简化示例。

(SELECT cast('2016-01-01' + interval `day` day as date) mydate
        FROM days) mymonth

3) join your patient line records with this subquery and use "distinct" to remove duplicate dates, e.g.

3）使用此子查询加入患者行记录，并使用“distinct”删除重复日期，例如

SELECT DISTINCT patientid, count(patientid) as `LineDays`
FROM   (SELECT cast('2016-01-01' + interval `day` day as date) mydate
        FROM days) mymonth
LEFT JOIN rangesignals on (mydate between startDate and endDate)
GROUP BY patientid;

I simplified time to date and ignored pendingsignals but I think you'll get the idea. I'm not totally sure that distinct will remove dupe dates before group by (I think not), but if so, you can do it with another subquery. And I think if you are using MS-SQL there might be another way to do a calendar. If you create the sqlfiddle then it is easy to test.

我简化了迄今为止的时间并忽略了pendingsignals，但我认为你会明白这一点。我不完全确定distinct会在分组之前删除欺骗日期（我认为不是），但如果是这样，你可以用另一个子查询来完成。我认为如果你使用的是MS-SQL，可能还有另一种方法来制作日历。如果您创建了sqlfiddle，那么很容易测试。

#1