代码之家 › 专栏 › 技术社区 › LCJ

将“基于查询”设置为“替换循环”,以填充所有记录从给定日期开始的所有月末日期

set-based rdbms tsql sql-server sql

LCJ · 技术社区 · 7 年前

我有一张存储病人实验室测试结果的表格。白蛋白、钾、磷等多种测试都有可能产生结果。每类患者的第一次读数都存储在一个名为metricfirstgroupreading的表格中。

CREATE TABLE #MetricFirstGroupReading (Patient_Key INT, Metric_Group VARCHAR(100), 
                                       Observation_Date DATE)
ALTER TABLE #MetricFirstGroupReading 
ADD CONSTRAINT UQ_MetricFirst UNIQUE (Patient_Key, Metric_Group);

INSERT INTO #MetricFirstGroupReading
SELECT 1, 'Albumin', '2018-11-15' UNION
SELECT 1, 'Potassium', '2018-12-10' UNION
SELECT 2, 'Albumin', '2018-10-20' UNION
SELECT 2, 'Potassium', '2018-11-25'

现在,我需要为MetricFirstGroupReading表中的每个记录将截至当前月份的所有月末日期填充到一个新表中。以下是查询在2018年12月运行时的预期结果。

我知道如何使用while循环。在SQL Server 2016中,如何在不使用循环的情况下使用基于集合的SQL查询来实现这一点?

3 回复 | 直到 7 年前

LCJ 7 年前

后续工作。这是对 tsql: How to retrieve the last date of each month between given date range

查询

CREATE TABLE #AllMonthEnds (MonthEndDate DATE)
DECLARE @Start datetime
DECLARE @End datetime

SELECT @Start = '2000-01-01'
SELECT @End = DATEADD(MONTH,1,GETDATE())
;With CTE as
(
    SELECT @Start  as Date,Case When DatePart(mm,@Start)<>DatePart(mm,@Start+1) then 1 else 0 end as [Last]
    UNION ALL
    SELECT Date+1,Case When DatePart(mm,Date+1)<>DatePart(mm,Date+2) then 1 else 0 end from CTE
    WHERE Date<@End
)

INSERT INTO #AllMonthEnds
SELECT [Date]
FROM CTE
WHERE [Last]=1   
OPTION ( MAXRECURSION 0 )



SELECT  T.Patient_Key, T.Metric_Group, T.Observation_Date AS First_Observation_Date,
        DATEDIFF(MONTh,Observation_Date, MonthEndDate) AS MonthDiff, 
         A.MonthEndDate AS IterationDate
FROM #AllMonthEnds A
INNER JOIN
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Patient_Key, Metric_Group ORDER BY Observation_Date) AS RowVal
    FROM #MetricFirstGroupReading M
)T
    ON A.MonthEndDate >= T.Observation_Date
WHERE RowVal = 1
ORDER BY Patient_Key, Metric_Group, T.Observation_Date, A.MonthEndDate

Gene Stempel 7 年前

怎么样:

    select MetricFirstGroupReading.*, datediff(month, MetricFirstGroupReading.Observation_Date, months.monthendval) monthdiff, months.* 
    into allmonths
    from
    (
    SELECT 1 patientid, 'Albumin' test, '2018-11-15' Observation_Date UNION
    SELECT 1 patientid, 'Potassium' test, '2018-12-10' Observation_Date UNION
    SELECT 2 patientid, 'Albumin' test, '2018-10-20' Observation_Date UNION
    SELECT 2 patientid, 'Potassium' test, '2018-11-25' Observation_Date) MetricFirstGroupReading 
join 
    (
    select '2018-10-31' monthendval union
    select '2018-11-30' monthendval union
    select '2018-12-31' monthendval 
    ) months on MetricFirstGroupReading.Observation_Date< months.monthendval

将第一个select union替换为表,并从第二个inner select中添加或删除月底。

Parfait 7 年前

考虑构建一个包含所有12个月结束日期的临时表,然后按日期范围联接到主表。使用 DateDiff 月差异:

CREATE TABLE #MonthEndDates (Month_End_Value DATE)

INSERT INTO #MonthEndDates
VALUES ('2018-01-31'),
       ('2018-02-28'),
       ('2018-03-31'),
       ('2018-04-30'),
       ('2018-05-31'),
       ('2018-04-30'),
       ('2018-06-30'),
       ('2018-07-31'),
       ('2018-08-31'),
       ('2018-09-30'),
       ('2018-10-31'),
       ('2018-11-30'),
       ('2018-12-31')

SELECT m.Patient_Key, m.Metric_Group, m.Observation_Date, 
       DateDiff(month, m.Observation_Date, d.Month_End_Value) AS Month_Diff, 
       d.Month_End_Value

FROM #MetricFirstGroupReading m
INNER JOIN #MonthEndDates d
  ON m.Observation_Date < d.Month_End_Value

GO

Rextester Demo