tsql - SQL Server grouping by weeks but only capturing the max date -

some screenshots of i'm trying do:

what returning current code

claim no 456 multiple service dates

what returning current code claim no 456

this return hoping for

explanation:

i work claims processing in healthcare industry , trying compile report based on service dates , received dates.

i counting claim numbers service dates vs date received , grouping dates received , service dates weeks. however, receiving physical therapy may have multiple service dates within month. recounting claim number every week.

is possible count claim number once last service date? not want claim no 456 counted 3 times, 1 each separate week.

here's have far:

select     dateadd(week, datediff(week, 0, service_fromdate),0) dos,     count(distinct claim_no) no_claims,     dateadd(week, datediff(week, 0, date_received),0) rec     claims_table     cast(convert(varchar, service_fromdate, 101) date time) >= '07/01/2016' group     dateadd(week, datediff(week, 0, service_fromdate),0),     dateadd(week, datediff(week, 0, date_received),0) 

---

thank time.

first, write query as:

select dos, count(distinct claim_no) no_claims, rec 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) service_fromdate >= '2016-07-01' group dos, rec; 

the cross apply makes query easier read. fixes logic in where clause. doubt want string comparison values larger '07/01/2016'.

then, want, need first record. think want:

select dos, count(claim_no) no_claims, rec (select c.*,              row_number() on (partition claim_no order service_fromdate desc) seqnum       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) seqnum = 1 , service_fromdate >= '2016-07-01' group dos, rec; 

note count(distinct) no longer necessary, because claims should appear 1 time.