Trouble in Fetchichg Data in Sql Server

Question

0.00/5 (No votes)

See more:

I've Two Tables
1. PurchaseTable Contains Columns PID int Auto Increment, Quantity int
2. AllocationTable Contains Columns AID int Auto Increment,PID int,Quantity int

PID of PurchaseTable is refered in AllocationTable as PID

and Quantity of PurchaseTable is refered in AllocationTable as Quantity

I want Such Records From PurchaseTable Whose PID not Exists in AllocationTable
And
Those records whose PID exists in AllocationTable but sum(AllocationTable.Quantity) group by AllocationTable.PID is Less Than PurchaseTable.Quantity after Substracting AllocationTable.Quantity from PurchaseTable.Quantity

i.e. If PurchaseTable is Like

PID | Quantity
1 | 10
2 | 20
3 | 15

and AllocationTable is Like

AID|PID |Quantity
1 | 2 | 15
2 | 1 | 10

Den I want Result Set As

PID | Quantity
2 | 5
3 | 15

Thanks in Advance :-)

Posted 21-Jun-14 7:35am

prashantttt

Updated 21-Jun-14 7:40am

v2

Add a Solution

Comments

[no name] 21-Jun-14 13:48pm

Okay.... and what have you tried? What was the result from the query that you wrote? What was the problem with the query that you wrote?

3 solutions

Solution 3

SQL

select p.pid, (p.quantity - isnull(a.quantity, 0)) from
purchase p left join allocation a on p.pid = a.pid
where isnull(a.quantity, 0) < p.quantity

Posted 21-Jun-14 18:01pm

Peter Leow

Solution 2

Hi,

The following query should work for you. If not, please let me know about it.

Query:

SQL

SELECT PID,
       Quantity
FROM   (SELECT p.PID,
               ( ISNULL(p.Quantity, 0) - SUM(ISNULL(a.Quantity, 0)) ) AS Quantity,
               a.PID AS PIDInAllocation
        FROM   PurchaseTable AS p
               LEFT JOIN AllocationTable AS a
                      ON a.PID = p.PID
        GROUP  BY p.PID,
                  p.Quantity,
                  a.PID) AS s
WHERE  ( PIDInAllocation IS NULL )
        OR ( Quantity > 0 )

Posted 21-Jun-14 12:58pm

Andrius Leonavicius

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)

write2varun · Accepted Answer · 2014-06-21T10:34:00

Hi
you can do somting like this

SQL

SELECT * FROM PurchaseTable
SELECT * FROM AllocationTable

SELECT PT.PID,(ISNULL(PT.QUANTITY,0) - ISNULL(AT.QUANTITY,0))AS[QUANTITY] FROM PurchaseTable PT

LEFT OUTER JOIN AllocationTable AT ON AT.PID =PT.PID

WHERE CAST((ISNULL(PT.QUANTITY,0) - ISNULL(AT.QUANTITY,0)) AS NVARCHAR(5))<>'0'

here is a fidler link
Fiddler Link[^]
you can modify it according to your use

thanks
VRN