CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Kevin K
Kevin K

Reputation: 35

SQL Server grouping by weeks but only capturing the max date

Some screenshots of what I'm trying to do:

What is returning from my current code

Claim No 456 with multiple service from dates

What is returning with my current code for claim No 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.

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.

Upvotes: 0

Views: 85

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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'.

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.

Upvotes: 4

Related Questions