我有一个查询，要求提供者在特定时间段内从我们的病历系统中看到的患者人数。我想提取去年的每个月，包括当前的月份。
我可以进行查询以手动执行此操作，但是我希望它每个月自动调整一次，以便当前月是最后一个查询，而其他月是前11个月，而不必重做代码中的所有搜索。
理想情况下，我还希望将其与相应月份的相应标题一起输出到表中。例如，当输出该表时，本月将列出为9/15，而不是Month12，并且下个月将调整为10/15，以此类推。
关于如何完成这两项任务的任何建议？
这是我当前的代码，可以正常工作，但是每个月都必须手动对其进行调整：
BEGIN
INSERT INTO #Output (Provider, Date1, Date2, Date3, Date4, Date5, Date6, Date7, Date8, Date9, Date10, Date11, Date12)
OUTPUT inserted.Provider, inserted.Date1, inserted.Date2, inserted.Date3, inserted.Date4, inserted.Date5, inserted.Date6, inserted.Date7, inserted.Date8,
inserted.Date9, inserted.Date10,inserted.Date11, inserted.Date12
select pm.last_name,
SUM(case when enc_timestamp >= '20141001' and enc_timestamp < '20141101' then 1 else 0 end) as total_patients_Oct14,
SUM(case when enc_timestamp >= '20141101' and enc_timestamp < '20141201' then 1 else 0 end) as total_patients_Nov14,
SUM(case when enc_timestamp >= '20141201' and enc_timestamp < '20150101' then 1 else 0 end) as total_patients_Dec14,
SUM(case when enc_timestamp >= '20150101' and enc_timestamp < '20150201' then 1 else 0 end) as total_patients_Jan15,
SUM(case when enc_timestamp >= '20150201' and enc_timestamp < '20150301' then 1 else 0 end) as total_patients_Feb15,
SUM(case when enc_timestamp >= '20150301' and enc_timestamp < '20150401' then 1 else 0 end) as total_patients_Mar15,
SUM(case when enc_timestamp >= '20150401' and enc_timestamp < '20150501' then 1 else 0 end) as total_patients_April15,
SUM(case when enc_timestamp >= '20150501' and enc_timestamp < '20150601' then 1 else 0 end) as total_patients_May15,
SUM(case when enc_timestamp >= '20150601' and enc_timestamp < '20150701' then 1 else 0 end) as total_patients_June15,
SUM(case when enc_timestamp >= '20150701' and enc_timestamp < '20150801' then 1 else 0 end) as total_patients_July15,
SUM(case when enc_timestamp >= '20150801' and enc_timestamp < '20150901' then 1 else 0 end) as total_patients_Aug15,
SUM(case when enc_timestamp >= '20150901' and enc_timestamp < '20151001' then 1 else 0 end) as Total_Charge_Sep15
from patient_encounter pe
inner join provider_mstr pm ON pe.rendering_provider_id = pm.provider_id
where enc_timestamp >= '20140101' and enc_timestamp < '20160101' and billable_ind = 'Y' and checkin_datetime is not NULL
group by pm.last_name
END
方法DATEADD是您的朋友：
编辑：自动获取本月的第一天作为ReferenceDate：EDIT2：将参考日期设置为一年。
DECLARE @ReferenceDate DATETIME=CAST(YEAR(GETDATE())-1 AS VARCHAR(4))+REPLACE(STR(MONTH(GETDATE()),2),' ','0')+'01';
这是您改编的代码：
BEGIN
INSERT INTO #Output (Provider, Date1, Date2, Date3, Date4, Date5, Date6, Date7, Date8, Date9, Date10, Date11, Date12)
OUTPUT inserted.Provider, inserted.Date1, inserted.Date2, inserted.Date3, inserted.Date4, inserted.Date5, inserted.Date6, inserted.Date7, inserted.Date8,
inserted.Date9, inserted.Date10,inserted.Date11, inserted.Date12
select pm.last_name,
SUM(case when enc_timestamp >= @ReferenceDate and enc_timestamp < DATEADD(MONTH,1,@ReferenceDate) then 1 else 0 end) as total_patients_Oct14,
SUM(case when enc_timestamp >= DATEADD(MONTH,1,@ReferenceDate) and enc_timestamp < DATEADD(MONTH,2,@ReferenceDate) then 1 else 0 end) as total_patients_Nov14,
SUM(case when enc_timestamp >= DATEADD(MONTH,2,@ReferenceDate) and enc_timestamp < DATEADD(MONTH,3,@ReferenceDate) then 1 else 0 end) as total_patients_Dec14,
SUM(case when enc_timestamp >= DATEADD(MONTH,3,@ReferenceDate) and enc_timestamp < DATEADD(MONTH,4,@ReferenceDate) then 1 else 0 end) as total_patients_Jan15,
SUM(case when enc_timestamp >= DATEADD(MONTH,4,@ReferenceDate) and enc_timestamp < DATEADD(MONTH,5,@ReferenceDate) then 1 else 0 end) as total_patients_Feb15,
SUM(case when enc_timestamp >= DATEADD(MONTH,5,@ReferenceDate) and enc_timestamp < DATEADD(MONTH,6,@ReferenceDate) then 1 else 0 end) as total_patients_Mar15,
SUM(case when enc_timestamp >= DATEADD(MONTH,6,@ReferenceDate) and enc_timestamp < DATEADD(MONTH,7,@ReferenceDate) then 1 else 0 end) as total_patients_April15,
SUM(case when enc_timestamp >= DATEADD(MONTH,7,@ReferenceDate) and enc_timestamp < DATEADD(MONTH,8,@ReferenceDate) then 1 else 0 end) as total_patients_May15,
SUM(case when enc_timestamp >= DATEADD(MONTH,8,@ReferenceDate) and enc_timestamp < DATEADD(MONTH,9,@ReferenceDate) then 1 else 0 end) as total_patients_June15,
SUM(case when enc_timestamp >= DATEADD(MONTH,9,@ReferenceDate) and enc_timestamp < DATEADD(MONTH,10,@ReferenceDate) then 1 else 0 end) as total_patients_July15,
SUM(case when enc_timestamp >= DATEADD(MONTH,10,@ReferenceDate) and enc_timestamp < DATEADD(MONTH,11,@ReferenceDate) then 1 else 0 end) as total_patients_Aug15,
SUM(case when enc_timestamp >= DATEADD(MONTH,11,@ReferenceDate) and enc_timestamp < DATEADD(MONTH,12,@ReferenceDate) then 1 else 0 end) as Total_Charge_Sep15
from patient_encounter pe
inner join provider_mstr pm ON pe.rendering_provider_id = pm.provider_id
where enc_timestamp >= @ReferenceDate and enc_timestamp < DATEADD(YEAR,2,@ReferenceDate) and billable_ind = 'Y' and checkin_datetime is not NULL
group by pm.last_name
END
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句