Solved

VB.Net - SQL Server Query Assistance

Posted on 2014-12-09
5
225 Views
Last Modified: 2014-12-15
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
0
Comment
Question by:Jimbo99999
  • 2
  • 2
5 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40489650
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40489651
<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
 

Author Comment

by:Jimbo99999
ID: 40489746
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40500687
Do you need further assistance?
0
 

Author Comment

by:Jimbo99999
ID: 40500700
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Convert int to military time 8 20
Sql query 34 22
Convert SP in a format for debugging 7 13
Vb.Net Date Formatting Assistance 4 17
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now