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
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
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?
Also, BillId is the same for all repeating bills. If not, how can we recognize they are repeat bills?
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.
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.
ASKER
i mean to say .. this bill with these conditions has to be on recent record
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.
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 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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Paul
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