SQLTeam.com | Weblogs | Forums

Distinct counts or sums


#1

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.


#2
Select op.Created, Convert(date,o.Oppo_Opened) as Opened,
    Max([3Hour]) as [3Hour],
    Max([24Hour]) as [24Hour]
From (
    Select Oppo_OpportunityId, 
        Convert(date, Oppo_CreatedDate) as Created,
        Sum(Case When Oppo_Stage In ('IQ','HQ','3Q') Then 1 Else 0 End) as [3Hour],
        Sum(Case When Oppo_Stage In ('SDQ','24Q') Then 1 Else 0 End) as [24Hour]    
    From OpportunityProgress
    Where op.Oppo_CreatedDate > DateAdd(Day, -30, Convert(date, getdate()))
    Group By Oppo_OpportunityId, Convert(date, Oppo_CreatedDate)
) as op 
Inner Join Opportunity o On o.Oppo_OpportunityId = op.Oppo_OpportunityId
Where o.Oppo_Deleted is Null and o.Oppo_ChannelId=7