question

To be precise and organized, I have divided my questions into three parts and desired result. Here is my detailed data in PART 1: PART 1: --Drop Table #partitionover create table #partitionover ( ProviderID varchar (4), ServiceUnitID varchar (5), ClientID varchar (2), ServiceDate date, ServiceHours smallint, ServiceBegTime float, ServiceEndTime float, ServiceType varchar(2), ServiceCPTCode varchar(5), ServiceGroupNo varchar(5), GroupServices char (100), ChgUnit float, AverageRate money , ) go insert into #partitionover ( ProviderID, ServiceUnitID, ClientID, ServiceDate, ServiceHours, ServiceBegTime, ServiceEndTime, ServiceType, ServiceCPTCode, ServiceGroupNo, GroupServices, ChgUnit, AverageRate) select 1220,11830,1 ,'4/1/2015' ,1 ,1600 ,1700 ,'K7' ,90853 ,29472 ,'Group Service',4 ,5.5 UNION ALL select 1220,11830,2 ,'4/1/2015' ,0.5 ,1500 ,1530 ,'K5' ,90832 ,0 ,'Indv.Services',1 ,49.5 UNION ALL select 1220,11830,3 ,'4/1/2015' ,1 ,1600 ,1700 ,'K7' ,90853 ,29472 ,'Group Service',4 ,5.5 UNION ALL select 1220,11831,4 ,'4/1/2015' ,1.5 ,1900 ,2030 ,'K7' ,90853 ,27053 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,5 ,'4/1/2015' ,1.5 ,1730 ,1900 ,'K7' ,90853 ,21645 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,5 ,'4/1/2015' ,1.5 ,1900 ,2030 ,'K7' ,90853 ,27053 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,6 ,'4/1/2015' ,1.5 ,1900 ,2030 ,'K7' ,90853 ,27053 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,7 ,'4/1/2015' ,1.5 ,1900 ,2030 ,'K7' ,90853 ,27053 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,8 ,'4/1/2015' ,2 ,1300 ,1500 ,'K2' ,90791 ,0 ,'Indv.Services',8 ,25.1 UNION ALL select 1220,11830,9 ,'4/1/2015' ,1 ,1600 ,1700 ,'K7' ,90853 ,29472 ,'Group Service',4 ,5.5 UNION ALL select 1220,11830,10, '4/1/2015', 1 ,1600 ,1700 ,'K7' ,90853 ,29472 ,'Group Service',4 ,5.5 UNION ALL select 1220,11831,11, '4/1/2015', 1.5 ,1900 ,2030 ,'K7' ,90853 ,27053 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,12, '4/1/2015', 1.5 ,1730 ,1900 ,'K7' ,90853 ,21645 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,13, '4/1/2015', 1.5 ,1900 ,2030 ,'K7' ,90853 ,27053 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,14, '4/1/2015', 1.5 ,1900 ,2030 ,'K7' ,90853 ,27053 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,15, '4/1/2015', 1.5 ,1900 ,2030 ,'K7' ,90853 ,27053 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,16, '4/1/2015', 1.5 ,1730 ,1900 ,'K7' ,90853 ,21645 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,16, '4/1/2015', 1.5 ,1900 ,2030 ,'K7' ,90853 ,27053 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,17, '4/1/2015', 1.5 ,1900 ,2030 ,'K7' ,90853 ,27053 ,'Group Service',6 ,5.5 UNION ALL select 1220,11831,18, '4/1/2015', 1.5 ,1730 ,1900 ,'K7' ,90853 ,21645 ,'Group Service',6 ,5.5 Go PART 2: In the second part, I am calculating group size and group rates based on several fields: ; with finalrate as (select distinct ProviderID, ServiceUnitID, ServiceDate,ServiceHours,ServiceBegTime,ServiceEndTime,ServiceType,ServiceCPTCode,ServiceGroupNo,GroupServices,ChgUnit,AverageRate ,AvgCPTRate50=case when ServiceGroupNo=0 then sum(AverageRate*.5) else 0 end ,ServiceGrpSize=case when ServiceGroupNo=0 Then 0 Else count(clientid) over (partition by providerid,servicedate,Servicetype,clientid,ServiceGroupNo,ServiceBegtime,ServiceEndtime) End ,GroupSvcRate=case when ServiceGroupNo<> 0 and servicetype='K7' and (count(clientid) over (partition by ServiceDate,providerid,ServiceGroupNo,servicetype,ServiceBegtime,ServiceEndtime)) <=4 then (25*ServiceHours) when ServiceGroupNo<> 0 and servicetype='K7' and (count(clientid) over (partition by ServiceDate,providerid,ServiceGroupNo,servicetype,ServiceBegtime,ServiceEndtime)) <= 8 and (count(clientid) over (partition by ServiceDate,providerid,ServiceGroupNo,servicetype,ServiceBegtime,ServiceEndtime)) >= 5 then (50*ServiceHours) when ServiceGroupNo<> 0 and servicetype='K7' and (count(clientid) over (partition by ServiceDate,providerid,ServiceGroupNo,servicetype,ServiceBegtime,ServiceEndtime)) >= 9 then (75*ServiceHours) when ServiceGroupNo<> 0 and servicetype='K25' and (count(clientid) over (partition by ServiceDate,providerid,ServiceGroupNo,servicetype,ServiceBegtime,ServiceEndtime)) <=4 then (20*ServiceHours) when ServiceGroupNo<> 0 and servicetype='K25' and (count(clientid) over (partition by ServiceDate,providerid,ServiceGroupNo,servicetype,ServiceBegtime,ServiceEndtime)) <= 8 and (count(clientid) over (partition by ServiceDate,providerid,ServiceGroupNo,servicetype,ServiceBegtime,ServiceEndtime)) >= 5 then (40*ServiceHours) when ServiceGroupNo<> 0 and servicetype='K25' and (count(clientid) over (partition by ServiceDate,providerid,ServiceGroupNo,servicetype,ServiceBegtime,ServiceEndtime)) <= 12 and (count(clientid) over (partition by ServiceDate,providerid,ServiceGroupNo,servicetype,ServiceBegtime,ServiceEndtime)) >= 9 then (60*ServiceHours) when ServiceGroupNo<> 0 and servicetype='K25' and (count(clientid) over (partition by ServiceDate,providerid,ServiceGroupNo,servicetype,ServiceBegtime,ServiceEndtime)) >= 13 then (80*ServiceHours) Else 0 End From #partitionover Group by ProviderID, ServiceUnitID,ClientID, ServiceDate,ServiceHours,ServiceBegTime,ServiceEndTime,ServiceType,ServiceCPTCode,ServiceGroupNo,GroupServices,ChgUnit,AverageRate ) PART 3: In the last part I am grouping my data. Please note that CLIENTID is included in PART 1 but not PART 2 (except in group by) and PART 3 select distinct ProviderID, sum(ServiceHours) as TotalHours,sum(AvgCPTRate50) as TotalRate, sum(ServiceGrpSize) as Groupsize,sum(GroupSvcRate) as GroupRate From finalrate Group by ProviderID RESULT: This is an accurate result at aggeregate level ProviderID TotalHours TotalRate Groupsize GroupRate 1220 5 37.3 3 125 Desired Results: I wan to add CLIENTID and other fields without changing group size, group rate, etc. When I add client id, then group size changes to 18 (number of clients). My question is there anyway to show all detailed data and add group size and group rate as separate fields and still get group size=3 and group rate=125 Thank You