i have my query as following
select Max(Reimbursement_EBSUtilization.Id) as Id,ProviderReimbursementRequest.Contractor_Id,
Reimbursement_EBSUtilization.ServiceMonth,
fContractor.ContractorName,Reimbursement_EBSUtilization.SD_Id,
Max(StandardUnits) as StandardUnits, max(Rate) as Rate,sum(Reimbursement_EBSUtilization.UnitsDelivered) as UnitsDelivered ,
null as ReduceUnits, Cast(1 as BIT) as IsEbs,
Reimbursement_EBSUtilization.BHFormName,fExpenseType.ExpenseType,
case when Reimbursement_EBSUtilization.BHFormName is null then max(Rate) * sum(Reimbursement_EBSUtilization.UnitsDelivered) * ISNULL(max(Reimbursement_EBSUtilization.StandardUnits),0) else
(case when fExpenseType.ExpenseType = 'Payable' then sum(ISNULL(Reimbursement_BHForms.ReimburseAmount,0)) - sum(ISNULL(Reimbursement_BHForms.ReducedAmount,0))
else 0 end ) -
(case when fExpenseType.ExpenseType = 'Offset' then sum(ISNULL(Reimbursement_BHForms.ReimburseAmount,0)) - sum(ISNULL(Reimbursement_BHForms.ReducedAmount,0))
else 0 end) end as ReimbursementAmount
from ProviderReimbursementRequest
left join Reimbursement_EBSUtilization on ProviderReimbursementRequest.Id = Reimbursement_EBSUtilization.PRR_Id
left join Reimbursement_BHForms on Reimbursement_EBSUtilization.Id = Reimbursement_BHForms.REU_Id
left join fExpenseCategory on Reimbursement_BHForms.EC_Id = fExpenseCategory.ID
left join fExpenseType on fExpenseCategory.ExpenseType = fExpenseType.Id
left join fContractor on ProviderReimbursementRequest.Contractor_Id = fContractor.Id
where MRR_Id = @MrrId and Reimbursement_EBSUtilization.SD_Id = @ServiceDetailId
group by ProviderReimbursementRequest.Contractor_Id,
Reimbursement_EBSUtilization.ServiceMonth,
fContractor.ContractorName,Reimbursement_EBSUtilization.SD_Id,
Reimbursement_EBSUtilization.BHFormName,
fExpenseType.ExpenseType
on executing the result is
Id Contractor_Id ServiceMonth ContractorName SD_Id StandardUnits Rate UnitsDelivered ReduceUnits IsEbs BHFormName ExpenseType ReimbursementAmount
3976 845 2016-05-01 Payments SC1 2867 1.00 10.00 20 NULL 1 NULL NULL 200.00
3966 845 2016-07-31 Payments SC1 2867 1.00 10.00 NULL NULL 1 NULL NULL NULL
3974 846 2016-07-01 Payments SC2 2867 1.00 10.00 100 NULL 1 NULL NULL 1000.00
3970 846 2016-07-31 Payments SC2 2867 1.00 10.00 20 NULL 1 NULL NULL 200.00
3978 847 2016-07-31 Payments SC3 2867 1.00 10.00 30 NULL 1 NULL NULL 300.00
3983 847 2016-08-01 Payments SC3 2867 1.00 10.00 NULL NULL 1 NULL NULL NULL
if you observe the servicemonth column for contractor_id = 846 we can see 2 records with same month. i want the output to combine these columns as one is with 2016-07-01 and other is with 2016-07-31 as they both belongs to same month and year i want them to be combined. can any one help on this ?