I have the following code:
Select distinct Convert(date,op.Oppo_CreatedDate) as Created, Convert(date,o.Oppo_Opened) as Opened,
Sum(Case When op.Oppo_Stage In ('IQ','HQ','3Q') Then 1 Else 0 End) as [3Hour],
Sum(Case When op.Oppo_Stage In ('SDQ','24Q') Then 1 Else 0 End) as [24Hour]
From OpportunityProgress op Inner Join Opportunity o On o.Oppo_OpportunityId = op.Oppo_OpportunityId
Where o.Oppo_Deleted is Null and o.Oppo_ChannelId=7 and Convert(date,op.Oppo_CreatedDate) > DateAdd(Day, -30, Getdate())
Group By Convert(date,op.Oppo_CreatedDate), Convert(date,o.Oppo_Opened)
The values calculated in [3hour] and [24hour] are not accurate as the join between the tables have more than 1 record. Is it possible to a distinct sum calculation to eliminate the duplication.