VB.Net - SQL Server Query Assistance

Good Day Experts!

I have been thinking about this query that I need to write before I can start on my new project but I cannot seem to come up with how to write it...it "feels" possible. Perhaps you can lend some insight. I need to look at my Invoice table and determine all the Invoice Numbers where the all the Orders for each Invoice have been filled. Here is some sample of what I would be looking at:

Invoice     Order     Filled
1234         A            1
1234         B            1
1234         C            1
5678         A            1
5678         B            1
5678         C            0
4321         A            0
4321         B            0
4321         C            0

I just can't seem to get started...can you help?

Thanks,
jimbo99999
Jimbo99999Asked:
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.

Kyle AbrahamsSenior .Net DeveloperCommented:
This will give you all invoice numbers that don't have a filled = 0.

It doesn't account for the condition where there is an order and the invoice hasn't been created yet.


select invoicenumber
from invoices
where not exists
(select invoicenumber from table where filled = 0)
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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Air code.  I also do my own stunts>

Since Filled is a 1/0, a sum would give you all orders filled, and a count would give you the count.  Then just subtract the two, and any non-zero value means there are orders not filled.  
SELECT Invoice, Sum(Filled) as sum_filled, Count(Filled) as count_filled
FROM YourTable
HAVING Sum(Filled) <> Count(Filled)

Open in new window

0
Jimbo99999Author Commented:
Hello, thanks for responding.  I tried both.  I actually did not realize the Filled field was defined as a bit so I received an error.  Kyle, I was able to get your version working.  We will be setting up a test table later today/early tomm so I can make the records to test with using the query.  So, if you don't mind I will leave the question open in case I have any questions.

Thanks,
jimbo99999
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Do you need further assistance?
0
Jimbo99999Author Commented:
Thanks for the help...I had a small "fire" with another project for the past couple of days.  I just did some final testing with this and it is working great!

Thanks and have a good day,
jimbo99999
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
Visual Basic.NET

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.