This is an example copy of the data I have:
| ReportMonth | LOB | State | Period | DateRange | PaidThrough | SegmentProduct | ServiceType | ServiceDetail | inOutCD | NetworkName | Allowed | Paid | Admits | Days | Visits | Scripts | MemCnt |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 3/1/2021 | Broad | MO | R12 | 12/2019 - 11/2020 | 202101 | Large Group FINR | IP | IP Med/Surg | IN | MO Network | 629772.02 | 625911.16 | 5 | 67 | 0 | 0 | 686 |
| 3/1/2021 | Broad | MO | R12 | 12/2019 - 11/2020 | 202101 | Large Group FINR | IP | IP OB Dlvry/Well NB | IN | MO Network | 10186 | 5380.2 | 1 | 6 | 0 | 0 | 466 |
| 3/1/2021 | Broad | MO | R12 | 12/2019 - 11/2020 | 202101 | Large Group FINR | IP | IP Med/Surg | IN | MO Network | 338471.65 | 306877.48 | 19 | 106 | 0 | 0 | 493 |
| 3/1/2021 | Broad | MO | YTD | 01/2020 - 11/2020 | 202101 | Large Group FINR | IP | IP Med/Surg | IN | MO Network | 338471.65 | 306877.48 | 19 | 106 | 0 | 0 | 493 |
| 3/1/2021 | Broad | MO | YTD | 01/2020 - 11/2020 | 202101 | Large Group FINR | IP | IP OB Dlvry/Well NB | IN | MO Network | 10186 | 5380.2 | 1 | 6 | 0 | 0 | 466 |
This is the code I have to alter:
SELECT [ReportMonth]
,[LOB]
,[State]
,'R12' as [Period]
,'12/2019 - 11/2020' as DateRange
,[PaidThrough]
,SegmentProduct
,[ServiceType]
,'Total' as [ServiceDetail]
,[inOutCD]
--,[NetworkID]
,[NetworkName]
,sum(Allowed) as Allowed
,sum(Paid) as Paid
,sum(Admits) as Admits
,sum([Days]) as [Days]
,sum(Visits) as Visits
,sum(Scripts) as Scripts
,max(Expos) as MemCnt
FROM [table] with (nolock)
group by reportmonth, lob, state, paidthrough, segmentproduct,
servicetype, inoutcd, networkname
When I run this the memcnt is not right. I need to figure out how to take the max of the servicedetail columns memcnt. The outcome of the other summations are right but the memcnt for
period = r12
changing the servicedetail to total
inoutcd = in
should be 1645
Tried doing a sum(max and sum(distinct on the memcnt) just not sure what to do. Was trying to figure out a countd(servicedetail) and some sort of sum with memcnt
