创建日期表，其中包含一个季度中每天的处方金额

我正在尝试开发一个查询来确定一个人在一个季度中每天服用的药物量。在某些日子里，没有开药，对于其他日子，可能会有重叠，我需要总量(意思是，每天每个总和的力量(。药物的数量、剂量、日用品等可能会有所不同。以下是一些数据：

create table #MemberInfo 
(ProgramName varchar(255), 
DateFilled datetime, 
DaySupply integer, 
MemberID varchar(255), 
Strength integer, 
Tradename varchar(255));
insert into #MemberInfo
Values ('InsureCo', '20130612', 30, 'MEM001', 10, 'Sedative')
, ('InsureCo', '20130429', 30, 'MEM001', 20, 'Sedative')
, ('InsureCo', '20130401', 30, 'MEM001', 20, 'Sedative')
, ('InsureCo', '20130529', 30, 'MEM001', 30, 'Sedative')

我真的不知道将一个季度中某一天服用的药物量相加的最佳方法是什么。如果可以的话，我想避免使用游标。我正在考虑创建一个临时表，其中包含一个季度的所有天数，然后以某种方式将这些日期与服用药物的每一天(即，DateFill + 随后的每一天直到 DaySupply(。一旦我到达了一个季度中每种药物的日期和数量，我就可以按天分组并获得每天的力量总和。我还需要能够获得一个季度的平均金额。

附加要求：

我有一个开始日期和几天。我想创建一行对于每个成员，他们每天都有一个处方(并做他们所有的处方都一样(。然后我会总结力量每天的所有药物。如果有帮助，所有药物将属于同一级别，实力将相当剂量，这意味着我可以总结它们。

对于报告，我需要能够计算连续天数金额大于某个截止值(假设 100(。这就是为什么我是试图每天获得金额。

Desired output
MemberID    Date        SumStrength
MEM001     2013-04-29  40
MEM001     2013-04-30  40
MEM001     2013-05-01  20
ETC FOR EVERY DAY FOR THIS MEMBER
MEM002     2013-04-01  60
MEM002     2013-04-02  40
ETC FOR EVERY DAY FOR THIS MEMBER

我认为只是一个简单的小组。

create table #MemberInfo 
(ProgramName varchar(255), 
DateFilled datetime, 
DaySupply integer, 
MemberID varchar(255), 
Strength integer, 
Tradename varchar(255));
insert into #MemberInfo
Values ('InsureCo', '20130612', 30, 'MEM001', 10, 'Sedative')
, ('InsureCo', '20130429', 30, 'MEM001', 20, 'Sedative')
, ('InsureCo', '20130429', 30, 'MEM002', 25, 'Sedative')
, ('InsureCo', '20130515', 30, 'MEM002', 25, 'Sedative')
, ('InsureCo', '20130401', 30, 'MEM001', 20, 'Sedative')
, ('InsureCo', '20130529', 30, 'MEM001', 30, 'Sedative')
, ('InsureCo', '20130529', 30, 'MEM003', 35, 'Sedative')
, ('InsureCo', '20130529', 30, 'MEM003', 45, 'Sedative')
select memberid,datefilled,SUM(strength) as [Strength sum]
from #MemberInfo
where memberid = 'MEM003' -- or whatever, could be a parameter
group by memberid,DateFilled
order by Memberid,DateFilled
drop table #MemberInfo

下面是如何使用CTE构建日历并使用OVER(PARTION BY)进行聚合

的示例

查询：

-- Declare a Start and End Date required to build a calendar
DECLARE @StartDate DATETIME = '2013-01-01'
DECLARE @EndDate   DATETIME = '2015-01-01'
-- Build out a Day/Quarter Calendar
;WITH Calendar ([Date], [Quarter]) AS (
    SELECT @StartDate, 1
    UNION ALL
    SELECT [Date] + 1, (DATEDIFF(m, @StartDate, [Date] + 1) / 3) + 1
    FROM Calendar 
    WHERE [Date] + 1 < @EndDate
)
-- Build Result Set
SELECT  ProgramName, 
        DateFilled, 
        DaySupply, 
        MemberID, 
        Strength,
        Quarter,
        SUM(Strength) OVER(PARTITION BY ProgramName, DaySupply, MemberID, Quarter) AS QuarterlyTotal,
        AVG(Strength) OVER(PARTITION BY ProgramName, DaySupply, MemberID, Quarter) AS QuarterlyAverage
FROM #MemberInfo MI
JOIN Calendar C ON MI.DateFilled = C.[Date]
ORDER BY MemberID, DateFilled
OPTION (MAXRECURSION 0)

测试数据：

create table #MemberInfo 
(ProgramName varchar(255), 
DateFilled datetime, 
DaySupply integer, 
MemberID varchar(255), 
Strength integer, 
Tradename varchar(255));
INSERT INTO #MemberInfo
Values
    --MEM001
    --Q1
     ('InsureCo', '20130112', 30, 'MEM001', 10, 'Sedative')
    ,('InsureCo', '20130129', 30, 'MEM001', 20, 'Sedative')
    ,('InsureCo', '20130401', 30, 'MEM001', 20, 'Sedative')
    --Q2
    ,('InsureCo', '20130529', 30, 'MEM001', 30, 'Sedative')
    ,('InsureCo', '20130429', 30, 'MEM001', 20, 'Sedative')
    ,('InsureCo', '20130401', 30, 'MEM001', 20, 'Sedative')
    --Q3
    ,('InsureCo', '20130829', 30, 'MEM001', 30, 'Sedative')
    --MEM002
    --Q1
    ,('InsureCo', '20130112', 30, 'MEM002', 10, 'Sedative')
    ,('InsureCo', '20130129', 30, 'MEM002', 20, 'Sedative')
    ,('InsureCo', '20130401', 30, 'MEM002', 20, 'Sedative')
    --Q2
    ,('InsureCo', '20130529', 30, 'MEM002', 30, 'Sedative')
    ,('InsureCo', '20130429', 30, 'MEM002', 20, 'Sedative')
    ,('InsureCo', '20130401', 30, 'MEM002', 20, 'Sedative')
    --Q3
    ,('InsureCo', '20130829', 30, 'MEM002', 30, 'Sedative')
    --Q4
    ,('InsureCo', '20131129', 30, 'MEM002', 30, 'Sedative')

结果：

ProgramName DateFilled  DaySupply   MemberID    Strength    Quarter QuarterlyTotal  QuarterlyAverage
InsureCo    2013-01-12  30          MEM001      10          1       30              15
InsureCo    2013-01-29  30          MEM001      20          1       30              15
InsureCo    2013-04-01  30          MEM001      20          2       90              22
InsureCo    2013-04-01  30          MEM001      20          2       90              22
InsureCo    2013-04-29  30          MEM001      20          2       90              22
InsureCo    2013-05-29  30          MEM001      30          2       90              22
InsureCo    2013-08-29  30          MEM001      30          3       30              30
InsureCo    2013-01-12  30          MEM002      10          1       30              15
InsureCo    2013-01-29  30          MEM002      20          1       30              15
InsureCo    2013-04-01  30          MEM002      20          2       90              22
InsureCo    2013-04-01  30          MEM002      20          2       90              22
InsureCo    2013-04-29  30          MEM002      20          2       90              22
InsureCo    2013-05-29  30          MEM002      30          2       90              22
InsureCo    2013-08-29  30          MEM002      30          3       30              30
InsureCo    2013-11-29  30          MEM002      30          4       30              30

今晚我玩了一些，我更接近了：

一些数据：

create TABLE dateranges (drug VARCHAR(5), date_begin DATETIME, numdays integer,strength        integer)
INSERT into dateranges values ('DrugA', '2010-01-01', 5, 10);
INSERT into dateranges values ('DrugB', '2008-02-27', 10, 20);
INSERT into dateranges values ('DrugC', '2010-04-26', 3, 20);
INSERT into dateranges values ('DrugD', '2000-02-01', 5, 30);

A CTE：

WITH cte (id, d, s)
     AS (SELECT tbl.drug AS id
                ,tbl.date_begin AS d
                ,tbl.strength AS s
           FROM dateranges tbl
          WHERE DATEDIFF(DAY, tbl.date_begin, tbl.date_begin+numdays-1) <= 100
         UNION ALL
         SELECT tbl.drug AS id
                ,DATEADD(DAY, 1, cte.d) AS d
                ,tbl.strength as s
           FROM cte
                INNER JOIN dateranges tbl
                  ON cte.id = tbl.drug
          WHERE cte.d < tbl.date_begin+numdays-1)
SELECT id AS drug
       ,d AS dates
       ,s AS strength
  FROM cte
 ORDER BY id, d, s

结果：

DRUG    DATES   STRENGTH
DrugA   January, 01 2010 00:00:00+0000  10
DrugA   January, 02 2010 00:00:00+0000  10
DrugA   January, 03 2010 00:00:00+0000  10
DrugA   January, 04 2010 00:00:00+0000  10
DrugA   January, 05 2010 00:00:00+0000  10
DrugB   February, 27 2008 00:00:00+0000 20
DrugB   February, 28 2008 00:00:00+0000 20
DrugB   February, 29 2008 00:00:00+0000 20
DrugB   March, 01 2008 00:00:00+0000    20
DrugB   March, 02 2008 00:00:00+0000    20
DrugB   March, 03 2008 00:00:00+0000    20
DrugB   March, 04 2008 00:00:00+0000    20
DrugB   March, 05 2008 00:00:00+0000    20
DrugB   March, 06 2008 00:00:00+0000    20
DrugB   March, 07 2008 00:00:00+0000    20
DrugC   April, 26 2010 00:00:00+0000    20
DrugC   April, 27 2010 00:00:00+0000    20
DrugC   April, 28 2010 00:00:00+0000    20
DrugD   February, 01 2000 00:00:00+0000 30
DrugD   February, 02 2000 00:00:00+0000 30
DrugD   February, 03 2000 00:00:00+0000 30
DrugD   February, 04 2000 00:00:00+0000 30
DrugD   February, 05 2000 00:00:00+0000 30

从这里开始，我计划按药物、日期、强度(总和强度(分组。我应该能够将这些结果扔到临时表中，然后计算超过我提到的阈值的天数。

相关内容

最新更新

热门标签：