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
chokkaStudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

savic7ukCommented:
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
0
Mike EghtebasDatabase and Application DeveloperCommented:
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?
0
chokkaStudentAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
chokkaStudentAuthor Commented:
i mean to say .. this bill with these conditions has to be on recent record
0
chokkaStudentAuthor Commented:
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.
0
savic7ukCommented:
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
0
PortletPaulfreelancerCommented:
>>"How to pick only distinct bill id's

>>"... (occured more than 5 times) and it is on top 1 record with ProcessorId = 300, ProcessorBinid = 50 and StateId = 38."

I assume from this you want ONLY the bill id, you are able to use the having clause without also needing to include the aggregate calculation in the select clause. To correctly using TOP there should be an ORDER BY clause. 
SELECT TOP(1)
      BillId
FROM Billworkflow
WHERE ProcessorID = 300
    AND ProcessorBinID = 50
    AND StateID = 38
    AND TimeStmp >= '20150830'
HAVING COUNT(BillId) >= 5
ORDER BY
        TimeStmp DESC

Open in new window


Please also note that MM/DD/YYYY is NOT a safe date format, but YYYYMMDD is a safe date literal format in SQL Server
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
mmm, there are errors in my previous comment - sorry
This might work instead: 
SELECT TOP (1)
      BillId
FROM (
      SELECT
            BillId
          , TimeStmp
          , COUNT(*) OVER (PARTITION BY BillId) AS UsedTimes
      FROM Billworkflow
      WHERE ProcessorID = 300
            AND ProcessorBinID = 50
            AND StateID = 38
            AND TimeStmp >= '20150830'
      ) X
WHERE X.UsedTimes >= 5
ORDER BY X.TimeStmp DESC

Open in new window


details: 
    CREATE TABLE Billworkflow
        ([BillId] int, [ProcessorID] int, [ProcessorBinID] int, [StateID] int, [TimeStmp] datetime)
    ;
        
    INSERT INTO Billworkflow
        ([BillId], [ProcessorID], [ProcessorBinID], [StateID], [TimeStmp])
    VALUES
        (1, 300, 50, 38, '2015-08-30 00:00:00'),
        (1, 300, 50, 38, '2015-08-30 00:00:00'),
        (1, 300, 50, 38, '2015-08-30 00:00:00'),
        (1, 300, 50, 38, '2015-08-30 00:00:00'),
        (1, 300, 50, 38, '2015-08-30 00:00:00'),
        (1, 300, 50, 38, '2015-08-30 00:00:00'),
        (1, 300, 50, 38, '2015-08-30 00:00:00'),
        (2, 300, 50, 38, '2015-08-30 00:00:00'),
        (2, 300, 50, 38, '2015-08-30 00:00:00'),
        (2, 300, 50, 38, '2015-08-30 00:00:00')
    ;
    
**Query 1**:

    SELECT TOP (1)
          BillId
    FROM (
          SELECT
                BillId
              , TimeStmp
              , COUNT(*) OVER (PARTITION BY BillId) AS UsedTimes
          FROM Billworkflow
          WHERE ProcessorID = 300
                AND ProcessorBinID = 50
                AND StateID = 38
                AND TimeStmp >= '20150830'
          ) X
    WHERE X.UsedTimes >= 5
    ORDER BY X.TimeStmp DESC
    

**[Results][2]**:
    | BillId |
    |--------|
    |      1 |

  [1]: http://sqlfiddle.com/#!3/8cad2/2

Open in new window

0
chokkaStudentAuthor Commented:
Thank you Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.