SQLTeam.com | Weblogs | Forums

Summation by Max

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

Please provide DDL and sample data and we can help