Avatar of chokka
chokka
Flag for United States of America asked on

How to pick only distinct bill id's ..

I have some bills keep circulating in our BillWorkflow table. I have to pick only those Bills.

Almost every bill will hit this ProcessorId, ProcessorBinId and StateId.

But i want the bills which are keep circulating (occured more than 5 times) and it is on top 1 record with ProcessorId = 300, ProcessorBinid = 50 and StateId = 38.

select distinct BillId from Billworkflow
where ProcessorID = 300
and ProcessorBinID = 50
and StateID = 38
and TimeStmp >=  '08/30/2015'

How should i pick only those distinct bill id's
Microsoft SQL Server 2005Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
chokka

8/22/2022 - Mon
savic7uk

hi

why dont you try this to find which billid's appear more than once

select BillId,count(BillId) from Billworkflow
where ProcessorID = 300
and ProcessorBinID = 50
and StateID = 38
and TimeStmp >=  '08/30/2015'
and count(BillId)>1
Group By BillId
Mike Eghtebas

When you say "...  it is on top 1 record ...", Top requires order by. Do you want to order by TimeStmp?

Also, BillId is the same for all repeating bills. If not, how can we recognize they are repeat bills?
chokka

ASKER
Msg 147, Level 15, State 1, Line 6
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
chokka

ASKER
i mean to say .. this bill with these conditions has to be on recent record
chokka

ASKER
Query returns ...


An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
savic7uk

I am sorry

select BillId,count(BillId) from Billworkflow
where ProcessorID = 300
and ProcessorBinID = 50
and StateID = 38
and TimeStmp >=  '08/30/2015'
Group By BillId
Having count(BillId)>1
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
chokka

ASKER
Thank you Paul