asked on

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

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

<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

Jimbo99999

ASKER

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

Kyle Abrahams, PMP

Do you need further assistance?

Jimbo99999

ASKER

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