我需要修改此报告，以显示每个月的这些计算。目前不确定如何。我已宣布开始日期和结束日期为每月，但可能需要更改。
declare @dataset_name varchar(36), @start_date datetime, @end_date date @report_date int, @start_date2 datetime, @end_date2 datetime, @report_date2 int
--set @dataset_name = 'Atlanta OPHC' -- This can be used to search individual business units --
set @start_date = '7/1/14' -- ENTER REPORT START DATE--
set @end_date = '7/31/14' -- ENTER REPORT END DATE---
set @start_date2 = '6/1/14' -- ENTER REPORT START DATE--
set @end_date2 = '6/30/14' -- ENTER REPORT END DATE---
--- Do Not Change anything below---
set @report_date = DATEDIFF(DD,@start_date , @end_date + 1)
set @report_date2 = DATEDIFF(DD,@start_date2 , @end_date2 + 1)
select tot.DATASET_NAME, SUM(tot.LENGTH_OF_STAY) as TOTAL_LOS, SUM(tot.LENGTH_OF_STAY)/ @report_date as Avg_daily_census, @start_date, @end_date,
COUNT(length_of_stay) as clients_servered,
AVG(tot.LENGTH_OF_STAY) as avg_los,
Count(admit_count) as Total_admission,
Count(discharge_date) as Total_discharge,
COUNT(active_patients)as Active_patients,
SUM(tot.LENGTH_OF_STAY2) as TOTAL_LOS2, SUM(tot.LENGTH_OF_STAY2)/ @report_date2 as Avg_daily_census2, @start_date2, @end_date2,
COUNT(length_of_stay2) as clients_servered2,
AVG(tot.LENGTH_OF_STAY2) as avg_los2,
Count(admit_count2) as Total_admission2,
Count(discharge_date2) as Total_discharge2,
COUNT(active_patients2)as Active_patients2
from (select od.DATASET_NAME,
DATEDIFF(DD,
case when pa.ADMIT_DATE < @start_date then @start_date else pa.ADMIT_DATE end,
case when ISNULL(pa.TERMINATION_DATE,GETDATE()) > @end_date then @end_date else ISNULL(pa.TERMINATION_DATE,GETDATE()) end)
+ 1 as LENGTH_OF_STAY,
case when pa.ADMIT_DATE between @start_date and @end_date then 1 end as admit_count,
case when pa.TERMINATION_DATE between @start_date and @end_date then 1 end as discharge_date,
case when pa.ADMIT_DATE <= @start_date or pa.ADMIT_DATE <= @end_date and TERMINATION_DATE IS NULL then 1 end as active_patients,
DATEDIFF(DD,
case when pa.ADMIT_DATE < @start_date2 then @start_date2 else pa.ADMIT_DATE end,
case when ISNULL(pa.TERMINATION_DATE,GETDATE()) > @end_date2 then @end_date2 else ISNULL(pa.TERMINATION_DATE,GETDATE()) end)
+ 1 as LENGTH_OF_STAY2,
case when pa.ADMIT_DATE between @start_date2 and @end_date2 then 1 end as admit_count2,
case when pa.TERMINATION_DATE between @start_date2 and @end_date2 then 1 end as discharge_date2,
case when pa.ADMIT_DATE <= @start_date2 or pa.ADMIT_DATE <= @end_date2 and TERMINATION_DATE IS NULL then 1 end as active_patients2
from PT_ADMISSION pa
join PT_BASIC pb on pa.PATIENT_ID = pb.PATIENT_ID
join O_DATASET od on pb.DATASET_ID = od.DATASET_ID
where (od.DATASET_NAME = @dataset_name or DATASET_NAME NOT IN ('Atlanta Training','Initial Configuration Home Health','Initial Configuration Hospice','Initial Configuration Private Duty','Penetration Testing'))
and pa.ADMIT_DATE <= @end_date
and ISNULL(pa.TERMINATION_DATE,GETDATE()) >= @start_date) tot
group by tot.DATASET_NAME
最佳答案
没有测试的能力，它看起来基本上还可以。
但是，在这些日期中，我永远不会依赖d / m / yy。 SQL Server中日期文字的最安全格式是YYYYMMDD，其次是YYYY-MM-DD
我可能已经调用了set @report_date和set @ report_date2（它们不存储日期）
@report_duration和@ report_duration2
@end_date日期之后的第一行中缺少逗号
我重新格式化以读取查询，它也可能对其他人有所帮助：
DECLARE @dataset_name varchar(36)
, @start_date datetime
, @end_date date
, @report_date int
, @start_date2 datetime
, @end_date2 datetime
, @report_date2 int
--set @dataset_name = 'Atlanta OPHC' -- This can be used to search individual business units --
SET @start_date = '20140701' -- ENTER REPORT START DATE--
SET @end_date = '20140731' -- ENTER REPORT END DATE---
SET @start_date2 = '20140601' -- ENTER REPORT START DATE--
SET @end_date2 = '20140630' -- ENTER REPORT END DATE---
--- Do Not Change anything below---
SET @report_date = DATEDIFF(DD, @start_date, @end_date + 1)
SET @report_date2 = DATEDIFF(DD, @start_date2, @end_date2 + 1)
SELECT
tot.DATASET_NAME
, SUM(tot.LENGTH_OF_STAY) AS TOTAL_LOS
, SUM(tot.LENGTH_OF_STAY) / @report_date AS Avg_daily_census
, @start_date
, @end_date
, COUNT(length_of_stay) AS clients_servered
, AVG(tot.LENGTH_OF_STAY) AS avg_los
, COUNT(admit_count) AS Total_admission
, COUNT(discharge_date) AS Total_discharge
, COUNT(active_patients) AS Active_patients
, SUM(tot.LENGTH_OF_STAY2) AS TOTAL_LOS2
, SUM(tot.LENGTH_OF_STAY2) / @report_date2 AS Avg_daily_census2
, @start_date2
, @end_date2
, COUNT(length_of_stay2) AS clients_servered2
, AVG(tot.LENGTH_OF_STAY2) AS avg_los2
, COUNT(admit_count2) AS Total_admission2
, COUNT(discharge_date2) AS Total_discharge2
, COUNT(active_patients2) AS Active_patients2
FROM (
SELECT
od.DATASET_NAME
, DATEDIFF(DD,
CASE
WHEN pa.ADMIT_DATE < @start_date THEN @start_date
ELSE pa.ADMIT_DATE END,
CASE
WHEN ISNULL(pa.TERMINATION_DATE, GETDATE()) > @end_date THEN @end_date
ELSE ISNULL(pa.TERMINATION_DATE, GETDATE()) END)
+ 1 AS LENGTH_OF_STAY
, CASE
WHEN pa.ADMIT_DATE BETWEEN @start_date AND @end_date THEN 1 END AS admit_count
, CASE
WHEN pa.TERMINATION_DATE BETWEEN @start_date AND @end_date THEN 1 END AS discharge_date
, CASE
WHEN pa.ADMIT_DATE <= @start_date OR
pa.ADMIT_DATE <= @end_date AND
TERMINATION_DATE IS NULL THEN 1 END AS active_patients
, DATEDIFF(DD,
CASE
WHEN pa.ADMIT_DATE < @start_date2 THEN @start_date2
ELSE pa.ADMIT_DATE END,
CASE
WHEN ISNULL(pa.TERMINATION_DATE, GETDATE()) > @end_date2 THEN @end_date2
ELSE ISNULL(pa.TERMINATION_DATE, GETDATE()) END)
+ 1 AS LENGTH_OF_STAY2
, CASE
WHEN pa.ADMIT_DATE BETWEEN @start_date2 AND @end_date2 THEN 1 END AS admit_count2
, CASE
WHEN pa.TERMINATION_DATE BETWEEN @start_date2 AND @end_date2 THEN 1 END AS discharge_date2
, CASE
WHEN pa.ADMIT_DATE <= @start_date2 OR
pa.ADMIT_DATE <= @end_date2 AND
TERMINATION_DATE IS NULL THEN 1 END AS active_patients2
FROM PT_ADMISSION pa
JOIN PT_BASIC pb
ON pa.PATIENT_ID = pb.PATIENT_ID
JOIN O_DATASET od
ON pb.DATASET_ID = od.DATASET_ID
WHERE (od.DATASET_NAME = @dataset_name
OR DATASET_NAME NOT IN ( 'Atlanta Training'
, 'Initial Configuration Home Health'
, 'Initial Configuration Hospice'
, 'Initial Configuration Private Duty'
, 'Penetration Testing')
)
AND pa.ADMIT_DATE <= @end_date
AND ISNULL(pa.TERMINATION_DATE, GETDATE()) >= @start_date
) tot
GROUP BY
tot.DATASET_NAME
谓词
AND ISNULL（pa.TERMINATION_DATE，GETDATE（））> = @开始日期
可能会像这样表现更好：
AND（pa.TERMINATION_DATE> = @start_date或pa.TERMINATION_DATE为NULL）
在where子句中对列使用函数可以删除索引的使用，因此首选“可精化”谓词。
关于mysql - 如何修改此报表以每月运行sql，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25287350/