Want to count certain distinct records

[azoe]

In one of the case statements below I enter a "Y" if it is a new patient. However, I really only want to count them one time per date (c.begin_date_of_service). I wasn't sure how to do that so I tried to do a distinct count in another case statement but it probably isn't allowed (I got an error - incorrect syntax near the keyword 'distinct').

I would either like to count the new patients in SQL or to mark only one per date (c.begin_date_of_service) with a Y or a 1 and I can count those later in Crystal.

Thanks -

Select  distinct c.charge_id as ChgID,
	per.person_id as PerID,  
	pe.enc_id as PEenc_id,
	c.source_id as ChgEnc_id,
	ep.payer_id as EncPayerID,
	pe.cob1_payer_id as EncPrimaryPayer,
	pe.enc_nbr as EncNbr,
	c.create_Timestamp as ChgCreateDate,
	c.begin_date_of_service as DOS,
	c.amt as ChgAmt,
	
		Case
		When per.create_timestamp between '20051212' and '20051213' then 'Y'
		Else 'N'
		End as NewPtY_or_N,
	
		Case 
		When EncPrimPayer.payer_name is not null then EncPrimPayer.payer_name --when there is an encounter_payer fill in the one with cob=1
		When pe.cob1_payer_id is null then 'Self'
		Else (
			select payer_name --need this part to fill in primary payer where pmts made from secondary payer
			from payer_mstr inner join encounter_payer on payer_mstr.payer_id = encounter_payer.payer_id 
			where encounter_payer.enc_id = ep.enc_id and cob = 1
			)
		End  as EncounterChgPayer,
	
		Case
		When pe.cob1_payer_id is Null then 'SP'
		When pe.cob1_payer_id is not Null then 
	 (
		SELECT	[mstr_list_item_desc]
		FROM	[mstr_lists] inner join payer_mstr on mstr_lists.mstr_list_item_id = payer_mstr.financial_class
			inner join encounter_payer on payer_mstr.payer_id = encounter_payer.payer_id
		WHERE	encounter_payer.enc_id = ep.enc_id and cob = 1 
	) 
		End As [ChargeFC],
		Case
		When per.create_timestamp between '20051212' and '20051213' then (distinct count(cast(per.person_id as varchar(50))) )
		End as PtCount

FROM    person per   
	left outer join patient_encounter pe on (pe.person_id = per.person_id and pe.practice_id = per.practice_id)
        left outer join charges c on (c.person_id = pe.person_id and c.source_id = pe.enc_id and pe.practice_id = c.practice_id)
	
	left outer join payer_mstr EncPrimPayer on EncPrimPayer.payer_id = pe.cob1_payer_id--(EncPrimPayer.payer_id = ep.payer_id and cob=1)
	left outer join encounter_payer ep on (pe.enc_id = ep.enc_id and pe.person_id = ep.person_id and EncPrimPayer.payer_id = ep.payer_id)
	left outer join mstr_lists ml on (ml.mstr_list_item_id = EncPrimPayer.financial_class)		

WHERE   c.begin_date_of_service between '20051212' and '20051213'

 

[NoCoolHandle]

To count distinct records the basic synatx is


Select count(distinct(column)) as [Distinct Count Of Column]
,column
From sometable
Where column2 = 'abc'
Group by column
order by 1 desc

 

[NoCoolHandle]

Sorry make that



Select count(distinct column) as [Distinct Count Of Column]

 

[vongrunt]

Wouldn't [Distinct Count Of Column] always be 1 that way?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

Just proves I need to run my code to make sure speed doesn't effect accuracy..

this might be a better example..

create database crap
go
Use crap
go
Create Table X
(c1 int,c2 int)
go
insert into x
select 2,3
union all
select 2,3
union all
select 2,3
union all
select 3,3
union all
select 3,3
union all
select 3,3
union all
select 5,2
union all
select 5,2
union all
select 7,2
union all
select 5,2
union all
select 7,1
go
select count(distinct c1),count(distinct c2) from x
go
--drop database crap

 

[NoCoolHandle]

Or maybe a better example (with the above data)



select count(distinct c1)as [Distinct Entrys In C1],c2 from x group by c2

 

[maswien]

I guess something wrong with the last part of the select list:

Your code:

Case When per.create_timestamp 
        between '20051212' and '20051213' 
  then (distinct count(cast(per.person_id as varchar(50))) )
        End as PtCount

following your logic, it should be:

count(distinct 
      ( case when per.create_timestamp 
               between '20051212' and '20051213' 
             then cast(per.person_id as varchar(50))
                      else 'x' end )) - 1  as PtCount

 

[azoe]

I guess because of the count I would need to do some grouping - I'm getting this message for all the columns in the select:
Server: Msg 8118, Level 16, State 1, Line 1
Column 'c.charge_id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

I was able to get the count I needed with Crystal reports but I may try to get this working to get some practice with grouping.

Thanks for your replies -