sql - 计算按日历月分组的逗留时间

Question

这是我正在处理的:我得到了一个表(访问)，其中包含以下架构和示例数据。

访问

VisitNo   Location  AdmissionDate  DischargeDate LengthOfStay
1         A         2012-04-28     2012-05-30     32
2         A         2012-04-20     2013-05-20     90
3         B         2012-04-01     2012-05-01     30
4         B         2012-05-01     2012-05-03     2
.....................................................

要求是返回的数据集是下面的结构。按日历月 (YYYYMM) 划分的每个位置的总 LengthOfStay。

CalendarMonth  TotalLengthOfStayEachMonth(AdmissionToDate)  Location
201204          xxx                                          x
201205          yyyy                                         y
201206          zzzz                                         z
.........      ...............

TotalLengthOfStayEachMonth 的计算有点棘手。天数是从入场日期(admissionToDate)开始计算的，而不是按字面上的每月 30 天计算。例如，VISIT 表中的第一条记录有；

• 201204 停留 2 天
• 2+30 = 201205 中的 32 天，依此类推...

提前感谢您的建议...仅供引用，我们有数百个地点、数千名访客和 5 年的数据。

Answer 1

虽然我更希望看到您想要的输出，但也许像这样的内容可以帮助您入门:

SELECT 
    CONVERT(char(6), AdmissionDate, 112)  as CalendarMonth  ,
    SUM(DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,AdmissionDate)+1,0)))-DAY(AdmissionDate)) as TotalLengthOfStayEachMonth,
    Location
FROM VISIT
GROUP BY CONVERT(char(6), AdmissionDate, 112), Location

添加位置可能会给您重复的 CalendarMonths，但我认为没关系。

编辑——

我更多地尝试了这个，因为我意识到如果 DischargeDate 比 AdmissionDate 大 1 个月，那么几个月会丢失日期，并想出了这个 - 它使用 spt_values 表:

select 
   CONVERT(char(6), AdmissionDate+v.number, 112)  as CalendarMonth,
   COUNT(*) Days,
   Location
from Visit t
inner join master..spt_values v
  on v.type='P' and v.number <= DATEDIFF(d, AdmissionDate, DischargeDate)
group by Location, CONVERT(char(6), AdmissionDate+v.number, 112)
order by CONVERT(char(6), AdmissionDate+v.number, 112), Location

这是 SQL Fiddle .

-- 另一个编辑

在查看另一个答案时，并没有真正了解您的情况(需要所需的输出)，如果您的第一条记录所需的输出是四月的 2 天，那么您只需将 v.number <> 0 添加到在上面加入——很简单，只是不确定你在请求什么。这应该是最简单的解决方案:

select 
   CONVERT(char(6), AdmissionDate+v.number, 112)  as CalendarMonth,
   COUNT(*) Days,
   Location
from Visit t
inner join master..spt_values v
  on v.type='P' and v.number <= DATEDIFF(d, AdmissionDate, DischargeDate) and v.number <> 0
group by Location, CONVERT(char(6), AdmissionDate+v.number, 112)
order by CONVERT(char(6), AdmissionDate+v.number, 112), Location

更多fiddle .

希望其中一些有所帮助。

祝你好运。