Sql query to display invoice table where no products delivered

Question

1.00/5 (1 vote)

See more:

Hi all,

I have two tables as following :
INV (invoice header table) contains :
INV NUMBER , NAME
1 , Micheal
2 , Josh

Invoice items tables :
INV NUMBER , ITEM , QUANTITY , DELIVERED QUANTITY
1 , PEN , 10 , 0
1 , PENCIL , 10 , 1
2 , PEN , 10 , 0

Now what i need is display the invoices (invoice header) where there are no delivered products .
If there are one item delivered within the invoice , i don't want to see that invoice .

Only invoices where all items were not delivered .

The query for the above tables shall give me the :
2 , Josh

How to write the SQL query for that .

Thanks for helping .

Hadi

What I have tried:

A lot , but wasn't able to find the solution

Posted 22-Mar-18 1:21am

hadi_753

Updated 22-Mar-18 1:58am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-03-22T01:38:00

Solution 1

Try:

SQL

SELECT a.[Inv Number], a.[Name] FROM INV a
JOIN (SELECT [INV NUMBER], SUM([Delivered Quantity]) AS SumDQ 
      FROM Items 
      GROUP BY [INV NUMBER]) b 
ON a.[Inv Number] = b.[Inv Number]
WHERE b.SumDQ = 0

Posted 22-Mar-18 1:38am

OriginalGriff

Comments

#realJSOP 22-Mar-18 9:11am

5! :)

#realJSOP 22-Mar-18 9:59am

BTW, you only beat me to the punch because I was busy over-engineering my answer. :)

OriginalGriff 22-Mar-18 10:58am

:laugh: You did a little, I noticed...

#realJSOP · Answer 2 · 2018-03-22T02:00:00

SQL

-- drop the temp tables if they already exist
IF OBJECT_ID('tempdb..#InvHeader') IS NOT NULL
    DROP TABLE #InvHeader
IF OBJECT_ID('tempdb..#InvItems') IS NOT NULL
    DROP TABLE #InvItems

-- create the temp tables
CREATE TABLE #InvHeader
(
    InvNumber INT,
    [Name] NVARCHAR(50)
)

CREATE TABLE #InvItems
(
    InvNumber INT,
    Item NVARCHAR(50),
    Quantity INT,
    Delivered INT
)

-- populate the temp tables with your sample data
INSERT INTO #InvHeader ( InvNumber, [Name])
VALUES (1, 'Michael'), (2, 'Josh')
INSERT INTO #InvItems (InvNumber,Item,Quantity,Delivered)
VALUES (1, 'Pen', 10, 0), (1, 'Pencil', 10, 1), (2, 'Pen', 10, 0)

-- everything  above this comment will not be needed in your query. It is 
-- simply included here so you could test what I did

-- here's the code you're interested in. If you're happy with it, substitute 
-- your table and column names where appropriate.

-- create a common table expression to group the items so that each invoice 
-- number has exactly one delivered value that represents the total items 
-- delivered
;WITH cte AS
(
    SELECT invnumber, SUM(Delivered) AS totalDelivered
    FROM #InvItems
    GROUP BY invnumber
)
-- select the invoice number and name from the header table 
SELECT h.InvNumber,
       h.[Name]
FROM #InvHeader AS h 
-- and join tpo the common table expression
JOIN cte AS i 
-- but only in the invoice number matches and the total delivered value is 0
ON i.invnumber = h.InvNumber AND i.TotalDelivered = 0