Some screenshots of what I'm trying to do:

What is returning from my current code

从我当前的代码返回什么

Claim No 456 with multiple service from dates

从日期开始提供多项服务的第456号索赔

What is returning with my current code for claim No 456

我现在的第456号索赔代码返回了什么

This is the return I am hoping for

这是我希望的回报

Explanation:

I work with claims processing in the healthcare industry and I am trying to compile a report based on service from dates and received dates.

我在医疗保健行业处理索赔处理，我正在尝试根据日期和收到日期的服务编制报告。

I am counting claim numbers by service from dates vs date received and grouping the dates received and service from dates by weeks. However, for someone receiving physical therapy they may have multiple service dates within a month. So it is recounting the claim number every week.

我按服务日期与收到的日期计算索赔号，并按日期将收到的日期和服务分组。但是，对于接受物理治疗的人，他们可能在一个月内有多个服务日期。所以它每周都会重新计算索赔号。

Is it possible to only count the claim number once by its last service from date? I do not want claim No 456 to be counted three times, one for each separate week.

是否可以仅从最后一次服务开始计算一次索赔号？我不希望第456号索赔被计算三次，每个单独一周一次。

Here's what I have so far:

Select
    Dateadd(week, datediff(week, 0, service_fromdate),0) as dos,
    Count(distinct claim_no) as no_claims,
    Dateadd(week, datediff(week, 0, date_received),0) as rec
From
    Claims_table
Where
    CAST(CONVERT(VARCHAR, service_fromdate, 101) as date time) >= '07/01/2016'
Group by
    Dateadd(week, datediff(week, 0, service_fromdate),0),
    Dateadd(week, datediff(week, 0, date_received),0)

Thank you for your time.

感谢您的时间。

1 个解决方案

#1

First, write the query as:

首先，将查询写为：

Select dos, Count(distinct claim_no) as no_claims, rec
From Claims_table c cross apply
     (values (Dateadd(week, datediff(week, 0, service_fromdate),0),
              Dateadd(week, datediff(week, 0, date_received),0)
             )
     ) v(dos, rec)
where service_fromdate >= '2016-07-01'
Group by dos, rec;

The cross apply makes the query much easier to read. This also fixes the logic in the where clause. I really doubt that you want a string comparison with values larger than '07/01/2016'.

交叉应用使查询更容易阅读。这也修复了where子句中的逻辑。我真的怀疑你想要一个字符串比较大于'07 / 01/2016'的值。

Then, to do what you want, you need the first record. I think this is what you want:

然后，要做你想做的事，你需要第一条记录。我想这就是你想要的：

Select dos, Count(claim_no) as no_claims, rec
From (select c.*,
             row_number() over (partition by claim_no order by service_fromdate desc) as seqnum
      from Claims_table c
     ) c cross apply
     (values (Dateadd(week, datediff(week, 0, service_fromdate),0),
              Dateadd(week, datediff(week, 0, date_received),0)
             )
     ) v(dos, rec)
where seqnum = 1 and service_fromdate >= '2016-07-01'
Group by dos, rec;

Note that count(distinct) is no longer necessary, because the claims should only appear one time.

请注意，不再需要count（distinct），因为声明应该只出现一次。

#1