Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Can count distinct value with condition?

Author  Topic  rhst11 Starting Member 3 Posts Posted - 2009-12-15 : 19:31:21 Can an expert help me with this query? I'm new to SQL I need to find the value in a calculation Number of booking (sum(case when T1.str_contact_type ='In person' then 1 else 0 end)) divide by number of distinct clients with a condition: T1.str_contact_type ='In person' Declare @startdate datetime Declare @enddate datetime set @startdate='2009-03-01 00:00:00' set @enddate='2009-09-30 23:59:59' select T1.id_organisation, T1.str_name, T1.id_contract, sum(case when T1.str_contact_type ='In person' then 1 else 0 end) / count(distinct T1.id_client)from T1 where T1.str_contact_type ='In person'from (select B.id_organisation, O.str_name, B.id_client, B.id_contract, C.str_billing_type, C.id_sap_service_id, C.mny_rate, C.mny_amount, B.str_contact_type, B.id_booking, B.str_session_notes, B.dtm_booking_datetime --This is a table that include all basic requirements from tbl_booking B join tbl_organisation O on B.id_organisation=O.id_organisation join tbl_contract C on B.id_contract=C.id_contract and ((C.dtm_start_date >=@startdate and C.dtm_start_date <=@enddate)or(C.dtm_end_date >=@startdate and C.dtm_start_date <=@enddate)) and B.dtm_booking_datetime between @startdate and @enddate and B.bln_active=1 and B.int_booking_type in (1, 3) and B.str_status in ('attended', 'no-show', 'not yet attended') and C.str_billing_type='Sessional' ) T1 group by T1.id_organisation, T1.str_name, T1.id_contract