question

helal avatar image
helal asked

How can I combine detailed and aggreagte data in one table?

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
sql-server-2012aggregatespartitioning
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Alvin Ramard avatar image
Alvin Ramard answered
How can you combine detailed and aggregated data in one table? Best practices say detailed and aggregated data do not belong in the same table.
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Alvin, But that's the report requirement. I came up with UNION ALL to get both detailed and aggregate in one table.
0 Likes 0 ·
You asked about combining them in a table. It's ok to do it in a report, but not in a database table.
0 Likes 0 ·
Shouldn't this be a comment?
0 Likes 0 ·
Yes, you are right. My bad
0 Likes 0 ·
I am developing this report in SSRS and Report requirements are: each provider gets a separate report with: detailed data and aggregate report with the former at the top with all fields and the latter at the bottom with fewer fields. To get both reports separately in SSRS is fine. The issue is how to combine both in one report for each providers. I my trying sub report but doesn't look it's the solution. Thinking linked report. Any thoughts/suggestions?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.