mysql - 如何修改此报告以针对每个月运行 sql

Question

我需要修改此报告以显示它显示每个月的这些计算。目前还不确定如何。我已经声明了每月的开始和结束日期，但这可能需要更改。

  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

Answer 1

没有能力测试它，它看起来基本上没问题。

但是，我永远不会依赖 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()) >= @start_date

这样可能会表现得更好:

AND(pa.TERMINATION_DATE >= @start_date 或 pa.TERMINATION_DATE 为空)

在 where 子句中的列上使用函数可以删除索引的使用，因此首选“sargable”谓词。