select query problem

[narendrashete]

I have Two Tables

Tabel 1 is
Purchase

prodcode
billno
billdate
quantity

Tabel 2 is

Stock
prodcode
quantity

I m looking for the number of rows from purchase table which matches stock table with prodcode and quantity selected from purchases is less than or equal to quantity from stock table
e.g.

purchase
code billno billdate qty
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 100
prod2 1003 11/3/2007 200
prod2 1004 12/4/2007 100

stock
code quantity
prod1 130
prod2 200

then the result should be
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 10
prod2 1003 11/3/2007 200

Thanks and Regds
Narendra

[azimmer]

I have Two Tables

Tabel 1 is
Purchase

prodcode
billno
billdate
quantity

Tabel 2 is

Stock
prodcode
quantity

I m looking for the number of rows from purchase table which matches stock table with prodcode and quantity selected from purchases is less than or equal to quantity from stock table
e.g.

purchase
code billno billdate qty
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 100
prod2 1003 11/3/2007 200
prod2 1004 12/4/2007 100

stock
code quantity
prod1 130
prod2 200

then the result should be
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 10
prod2 1003 11/3/2007 200

Thanks and Regds
Narendra

Although I don't quite understand your example (it seems that for billno=1004 there's enough stock and still missing from the result table) but anyway join the tables with more than one condition like this:

SELECT p.code, p.billno, p.billdate, p.qty
FROM purchase p INNER JOIN stock s ON p.code=s.code AND p.qty<=s.quantity

[narendrashete]

Although I don't quite understand your example (it seems that for billno=1004 there's enough stock and still missing from the result table) but anyway join the tables with more than one condition like this:

SELECT p.code, p.billno, p.billdate, p.qty
FROM purchase p INNER JOIN stock s ON p.code=s.code AND p.qty<=s.quantity

I m sorry the result should be

purchase
code billno billdate qty
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 100
prod2 1003 11/3/2007 200
prod2 1004 12/4/2007 100

stock
code quantity
prod1 130
prod2 200

then the result should be
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 10
prod2 1003 11/3/2007 100
prod2 1004 11/3/2007 100

In short I would like to list all the last purchases corresponding to the stock lying with us.


Thanks and Regds
Narendra

[azimmer]

I m sorry the result should be

purchase
code billno billdate qty
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 100
prod2 1003 11/3/2007 200
prod2 1004 12/4/2007 100

stock
code quantity
prod1 130
prod2 200

then the result should be
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 10
prod2 1003 11/3/2007 100
prod2 1004 11/3/2007 100

In short I would like to list all the last purchases corresponding to the stock lying with us.


Thanks and Regds
Narendra

Sorry, Narendra, it's still not quite clear.
1001: OK, purchase 120, stock 130
1002: if purchase is 100, stock is 130, a result of 10 implies that you're looking for the 130 stock - 120 (stock used up by 1001) = 10 left as the result; or the result 10 is a typo (should be 100)
1003:purchase=2 00, stock=200, why isn't the result 200? Or is purchase a typo and should be 100.
1004:can be OK if 1002 & 1003 are clarified

Can you, please, clarify it?

[narendrashete]

Sorry, Narendra, it's still not quite clear.
1001: OK, purchase 120, stock 130
1002: if purchase is 100, stock is 130, a result of 10 implies that you're looking for the 130 stock - 120 (stock used up by 1001) = 10 left as the result; or the result 10 is a typo (should be 100)
1003:purchase=2 00, stock=200, why isn't the result 200? Or is purchase a typo and should be 100.
1004:can be OK if 1002 & 1003 are clarified

Can you, please, clarify it?

Hi again to simplify
purchase
code billno billdate qty
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 100
prod2 1003 11/3/2007 200
prod2 1004 12/4/2007 100

stock
code quantity
prod1 130
prod2 200

pl note
I would like to list all the last purchases corresponding to the stock lying with us i.e. by FIFO method.

then the result should be
code billno date purchase qty bal. qty
prod1 1001 01/01/2007 120 30
prod1 1002 10/2/2007 100 100
prod2 1003 11/3/2007 200 100
prod2 1004 11/3/2007 100 100




Thanks and Regds
Narendra

[narendrashete]

I have Two Tables

Tabel 1 is
Purchase

code
billno
billdate
quantity

Tabel 2 is
Stock

code
quantity

purchase
code billno billdate qty
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 100
prod2 1003 11/3/2007 200
prod2 1004 12/4/2007 100

stock
code quantity
prod1 130
prod2 200

pl note
I would like to list all the last purchases corresponding to the stock lying with us i.e. by FIFO method.

then the result should be
code billno date purqty balance
prod1 1001 01/01/2007 120 30
prod1 1002 10/2/2007 100 100
prod2 1003 11/3/2007 200 100
prod2 1004 11/3/2007 100 100

Thanks and Regds
Narendra

[azimmer]

I have Two Tables

Tabel 1 is
Purchase

code
billno
billdate
quantity

Tabel 2 is
Stock

code
quantity

purchase
code billno billdate qty
prod1 1001 01/01/2007 120
prod1 1002 10/2/2007 100
prod2 1003 11/3/2007 200
prod2 1004 12/4/2007 100

stock
code quantity
prod1 130
prod2 200

pl note
I would like to list all the last purchases corresponding to the stock lying with us i.e. by FIFO method.

then the result should be
code billno date purqty balance
prod1 1001 01/01/2007 120 30
prod1 1002 10/2/2007 100 100
prod2 1003 11/3/2007 200 100
prod2 1004 11/3/2007 100 100

Thanks and Regds
Narendra

Sorry, I still don't get it, maybe I'm impossible. I make one last attempt to get a grasp on it; if I still cannot I'll admit to be too dumb and let others have a go. So let's go step by step:

At 01/01/2007 (before the first transaction) stocks are: prod1=130, prod2=200. I assume that stocks do not grow during transactions (purchases).

Transaction: prod1 1001 01/01/2007 120
It should get listed because 120<130, with an after-transaction balance of prod1=10.
According to your results it gets listed with a balance of 30. Where does this balance come from?

Transaction: prod1 1002 10/2/2007 100
Does it get listed? Note, that 100>10 (10=balance), so it cannot be fulfilled.
According to your results it gets listed with a balance of 100. Where does this balance come from?

Transaction: prod2 1003 11/3/2007 200
It should get listed because 200=200, with an after-transaction balance of prod2=0.
According to your results it gets listed with a balance of 100. Where does this balance come from?

Transaction: prod2 1004 12/4/2007 100
Does it get listed? Note, that 100>0 (0=balance), so it cannot be fulfilled.
According to your results it gets listed with a balance of 100. Where does this balance come from? Also, I believe that the date listed is copied and not modified (should be 12/04/2007).