Solved

# using DISTINCT in a complex query

Posted on 2013-06-18

Hi

Im trying to get distinct only customerids for the part of the query below after

-- want distinct iCustomerids only

Using distinct in the select doesnt work and I just want 1 row returned for a customer without it messing up the total in the first part

Is this possible without tmp tables etc?

select vState,count(DISTINCT icustomerid) as [Total Pharmacies],

sum(case when iMonth = 1 then 1 else 0 end) "Jan",

sum(case when iMonth = 2 then 1 else 0 end) "Feb",

sum(case when iMonth = 3 then 1 else 0 end) "Mar",

sum(case when iMonth = 4 then 1 else 0 end) "Apr",

sum(case when iMonth = 5 then 1 else 0 end) "May",

sum(case when iMonth = 6 then 1 else 0 end) "Jun",

sum(case when iMonth = 7 then 1 else 0 end) "Jul",

sum(case when iMonth = 8 then 1 else 0 end) "Aug",

sum(case when iMonth = 9 then 1 else 0 end) "Sep",

sum(case when iMonth = 10 then 1 else 0 end) "Oct",

sum(case when iMonth = 11 then 1 else 0 end) "Nov",

sum(case when iMonth = 12 then 1 else 0 end) "Dec"

from (

-- want distinct iCustomerids only

select vState,iSurveyResponseId, dbo.tbCustomer.iCustomerid,

datepart(month, tbSurveyResponseFeedback.dtResponse) as iMonth

from

dbo.tbCustomer left join tbSurveyResponseFeedback on tbSurveyResponseFeedback.iCustomerid = dbo.tbCustomer.iCustomerId

and (dtResponse BETWEEN CONVERT(DATETIME, @dtYearStart, 102) AND GETDATE()) AND (iSurveyQuestionId = @iSurveyQuestionId)

where vState in ('NSW','ACT','QLD','SA','VIC','TAS','WA','NT') and dbo.tbCustomer.iClientid = @iClientid

and dbo.tbCustomer.iCustomerid in(select iCustomerid from tbCustomergroup where ((iGroupid = @iTopGroupid and dtinactive is null) or @iTopGroupid = 0))

)

) data

group by vState

ORDER BY VSTATE