sql server - SQL query distict count using inner join -

sql server - SQL query distict count using inner join -

need help ensuring below query doesn't homecoming inaccurate results.

select @billed = count(a.[counter]) [dbo].cxitems (nolock) inner bring together [dbo].cxitemhist b (nolock) on a.[counter] = b.cxlink b.[eventtype] in ('billed','rebilled') , b.[datetime] between @begdate , @enddate

the query "mostly" accurate is, there slight possibility cxitemhist table contain more 1 "billed" record given date range. need count item "billed" 1 time during given date range.

you can bring together on sub query limits 1 row each combination of fields used join:

select @billed = count(a.[counter]) [dbo].cxitems inner bring together ( select distinct cxlink [dbo].cxitemhist [eventtype] in ('billed','rebilled') , [datetime] between @begdate , @enddate ) b on a.[counter] = b.cxlink

you can utilize apply operator instead of bring together here, you'll have check against info see gives improve performance.

sql-server