Calculation for FIFO

Hi Everybody,
I've provide a sample Purchase Order with some data. I need to maintain a FI
FO (first-in-first-out) for the the sale of item and at the end of the day,
will generate a report which are the Purchase Order with item still unsold.
Example, sold 150 items (80 from id=1, 23 from id=2, 43 from id=3 and 4 from
id=4) , so the query need to display id 4 (still remain with 26) and id 5 (
still remain with 60)
Can someone help me on this query?
Sample Purchase Order table
id date item_code quantity
-- -- -- --
1 2005-01-02 item_A 80
2 2005-01-15 item_A 23
3 2005-02-04 item_A 43
4 2005-02-23 item_A 30
5 2005-03-13 item_A 60
Thanks in advance,
KristeKriste
See, if this helps you
CREATE TABLE #Sales
(
SaleId INT NOT NULL PRIMARY KEY,
SaleDate DATETIME NOT NULL,
ItemCode CHAR(6) NOT NULL,
Quantity INT
)
INSERT INTO #Sales VALUES (1,'2005-01-02', 'item_A',80)
INSERT INTO #Sales VALUES (2,'2005-01-15', 'item_A',23)
INSERT INTO #Sales VALUES (3,'2005-02-04', 'item_A',43)
INSERT INTO #Sales VALUES (4,'2005-02-23', 'item_A',30)
INSERT INTO VALUES (5,'2005-03-13', 'item_A',60)
SELECT * FROM #Sales S
WHERE (
SELECT COUNT(*) FROM #Sales
WHERE ItemCode = S.ItemCode
AND SaleId > S.SaleId
) <= 1
ORDER BY SaleId ASC
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:%23zGe4WGVFHA.3076@.T
K2MSFTNGP12.phx.gbl...
Hi Everybody,
I've provide a sample Purchase Order with some data. I need to maintain a FI
FO (first-in-first-out) for the the sale of item and at the end of the day,
will generate a report which are the Purchase Order with item still unsold.
Example, sold 150 items (80 from id=1, 23 from id=2, 43 from id=3 and 4 from
id=4) , so the query need to display id 4 (still remain with 26) and id 5 (
still remain with 60)
Can someone help me on this query?
Sample Purchase Order table
id date item_code quantity
-- -- -- --
1 2005-01-02 item_A 80
2 2005-01-15 item_A 23
3 2005-02-04 item_A 43
4 2005-02-23 item_A 30
5 2005-03-13 item_A 60
Thanks in advance,
Kriste|||Can u explain how does this query works? I don't quite understand.
If now the sold quantity = 100, how will it return the id=2 to 5?
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OHfl5gGVFHA.4092@.TK2MS
FTNGP12.phx.gbl...
Kriste
See, if this helps you
CREATE TABLE #Sales
(
SaleId INT NOT NULL PRIMARY KEY,
SaleDate DATETIME NOT NULL,
ItemCode CHAR(6) NOT NULL,
Quantity INT
)
INSERT INTO #Sales VALUES (1,'2005-01-02', 'item_A',80)
INSERT INTO #Sales VALUES (2,'2005-01-15', 'item_A',23)
INSERT INTO #Sales VALUES (3,'2005-02-04', 'item_A',43)
INSERT INTO #Sales VALUES (4,'2005-02-23', 'item_A',30)
INSERT INTO VALUES (5,'2005-03-13', 'item_A',60)
SELECT * FROM #Sales S
WHERE (
SELECT COUNT(*) FROM #Sales
WHERE ItemCode = S.ItemCode
AND SaleId > S.SaleId
) <= 1
ORDER BY SaleId ASC
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:%23zGe4WGVFHA.3076@.T
K2MSFTNGP12.phx.gbl...
Hi Everybody,
I've provide a sample Purchase Order with some data. I need to maintain a FI
FO (first-in-first-out) for the the sale of item and at the end of the day,
will generate a report which are the Purchase Order with item still unsold.
Example, sold 150 items (80 from id=1, 23 from id=2, 43 from id=3 and 4 from
id=4) , so the query need to display id 4 (still remain with 26) and id 5 (
still remain with 60)
Can someone help me on this query?
Sample Purchase Order table
id date item_code quantity
-- -- -- --
1 2005-01-02 item_A 80
2 2005-01-15 item_A 23
3 2005-02-04 item_A 43
4 2005-02-23 item_A 30
5 2005-03-13 item_A 60
Thanks in advance,
Kriste|||Kriste
Can you explain if i add INSERT INTO #Sales VALUES (6,'2005-03-13', 'item_A'
,100) why do I should get back from 2 to 5?
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:eEYiF4HVFHA.1404@.TK2
MSFTNGP09.phx.gbl...
Can u explain how does this query works? I don't quite understand.
If now the sold quantity = 100, how will it return the id=2 to 5?
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OHfl5gGVFHA.4092@.TK2MS
FTNGP12.phx.gbl...
Kriste
See, if this helps you
CREATE TABLE #Sales
(
SaleId INT NOT NULL PRIMARY KEY,
SaleDate DATETIME NOT NULL,
ItemCode CHAR(6) NOT NULL,
Quantity INT
)
INSERT INTO #Sales VALUES (1,'2005-01-02', 'item_A',80)
INSERT INTO #Sales VALUES (2,'2005-01-15', 'item_A',23)
INSERT INTO #Sales VALUES (3,'2005-02-04', 'item_A',43)
INSERT INTO #Sales VALUES (4,'2005-02-23', 'item_A',30)
INSERT INTO VALUES (5,'2005-03-13', 'item_A',60)
SELECT * FROM #Sales S
WHERE (
SELECT COUNT(*) FROM #Sales
WHERE ItemCode = S.ItemCode
AND SaleId > S.SaleId
) <= 1
ORDER BY SaleId ASC
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:%23zGe4WGVFHA.3076@.T
K2MSFTNGP12.phx.gbl...
Hi Everybody,
I've provide a sample Purchase Order with some data. I need to maintain a FI
FO (first-in-first-out) for the the sale of item and at the end of the day,
will generate a report which are the Purchase Order with item still unsold.
Example, sold 150 items (80 from id=1, 23 from id=2, 43 from id=3 and 4 from
id=4) , so the query need to display id 4 (still remain with 26) and id 5 (
still remain with 60)
Can someone help me on this query?
Sample Purchase Order table
id date item_code quantity
-- -- -- --
1 2005-01-02 item_A 80
2 2005-01-15 item_A 23
3 2005-02-04 item_A 43
4 2005-02-23 item_A 30
5 2005-03-13 item_A 60
Thanks in advance,
Kriste|||Hi Uri,
I think you didn't get my question right. The data here is of Purchase Order
. I have a accumulating sales quantity.
As the sales quantity increase, I need to find out which Purchase Order quan
ity is still unsold.
Eg.
Sold sales quantity = 50, so display Purchase Order 1 to 5
Sold sales quantity = 100, so display Purchase Order 2 to 5
Sold sales quantity = 150, so display Purchase Order 4 to 5
Sample Purchase Order table
id date item_code quantity accumulated inventory
-- -- -- -- --
1 2005-01-02 item_A 80 + 80 = 80
2 2005-01-15 item_A 23 + 23 = 103
3 2005-02-04 item_A 43 + 43 = 146
4 2005-02-23 item_A 30 + 30 = 176
5 2005-03-13 item_A 60 + 60 = 236
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23rT3DHIVFHA.3176@.TK2
MSFTNGP12.phx.gbl...
Kriste
Can you explain if i add INSERT INTO #Sales VALUES (6,'2005-03-13', 'item_A'
,100) why do I should get back from 2 to 5?
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:eEYiF4HVFHA.1404@.TK2
MSFTNGP09.phx.gbl...
Can u explain how does this query works? I don't quite understand.
If now the sold quantity = 100, how will it return the id=2 to 5?
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OHfl5gGVFHA.4092@.TK2MS
FTNGP12.phx.gbl...
Kriste
See, if this helps you
CREATE TABLE #Sales
(
SaleId INT NOT NULL PRIMARY KEY,
SaleDate DATETIME NOT NULL,
ItemCode CHAR(6) NOT NULL,
Quantity INT
)
INSERT INTO #Sales VALUES (1,'2005-01-02', 'item_A',80)
INSERT INTO #Sales VALUES (2,'2005-01-15', 'item_A',23)
INSERT INTO #Sales VALUES (3,'2005-02-04', 'item_A',43)
INSERT INTO #Sales VALUES (4,'2005-02-23', 'item_A',30)
INSERT INTO VALUES (5,'2005-03-13', 'item_A',60)
SELECT * FROM #Sales S
WHERE (
SELECT COUNT(*) FROM #Sales
WHERE ItemCode = S.ItemCode
AND SaleId > S.SaleId
) <= 1
ORDER BY SaleId ASC
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:%23zGe4WGVFHA.3076@.T
K2MSFTNGP12.phx.gbl...
Hi Everybody,
I've provide a sample Purchase Order with some data. I need to maintain a FI
FO (first-in-first-out) for the the sale of item and at the end of the day,
will generate a report which are the Purchase Order with item still unsold.
Example, sold 150 items (80 from id=1, 23 from id=2, 43 from id=3 and 4 from
id=4) , so the query need to display id 4 (still remain with 26) and id 5 (
still remain with 60)
Can someone help me on this query?
Sample Purchase Order table
id date item_code quantity
-- -- -- --
1 2005-01-02 item_A 80
2 2005-01-15 item_A 23
3 2005-02-04 item_A 43
4 2005-02-23 item_A 30
5 2005-03-13 item_A 60
Thanks in advance,
Kriste|||Kriste
Now , you are explained the problem by posting DDL
CREATE TABLE #Sales
(
SaleId INT NOT NULL PRIMARY KEY,
SaleDate DATETIME NOT NULL,
ItemCode CHAR(6) NOT NULL,
Quantity INT
)
INSERT INTO #Sales VALUES (1,'2005-01-02', 'item_A',80)
INSERT INTO #Sales VALUES (2,'2005-01-15', 'item_A',23)
INSERT INTO #Sales VALUES (3,'2005-02-04', 'item_A',43)
INSERT INTO #Sales VALUES (4,'2005-02-23', 'item_A',30)
INSERT INTO #Sales VALUES (5,'2005-03-13', 'item_A',60)
Declare @.Q INT
SET @.Q=100
SELECT * FROM
(
SELECT Saleid,(SELECT SUM(Quantity) FROM #Sales S
WHERE #Sales.Saleid>=S.Saleid )AS Ac
FROM #Sales
) AS DER WHERE Ac>=@.q
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:%23gB%23ePIVFHA.3544
@.TK2MSFTNGP12.phx.gbl...
Hi Uri,
I think you didn't get my question right. The data here is of Purchase Order
. I have a accumulating sales quantity.
As the sales quantity increase, I need to find out which Purchase Order quan
ity is still unsold.
Eg.
Sold sales quantity = 50, so display Purchase Order 1 to 5
Sold sales quantity = 100, so display Purchase Order 2 to 5
Sold sales quantity = 150, so display Purchase Order 4 to 5
Sample Purchase Order table
id date item_code quantity accumulated inventory
-- -- -- -- --
1 2005-01-02 item_A 80 + 80 = 80
2 2005-01-15 item_A 23 + 23 = 103
3 2005-02-04 item_A 43 + 43 = 146
4 2005-02-23 item_A 30 + 30 = 176
5 2005-03-13 item_A 60 + 60 = 236
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23rT3DHIVFHA.3176@.TK2
MSFTNGP12.phx.gbl...
Kriste
Can you explain if i add INSERT INTO #Sales VALUES (6,'2005-03-13', 'item_A'
,100) why do I should get back from 2 to 5?
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:eEYiF4HVFHA.1404@.TK2
MSFTNGP09.phx.gbl...
Can u explain how does this query works? I don't quite understand.
If now the sold quantity = 100, how will it return the id=2 to 5?
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OHfl5gGVFHA.4092@.TK2MS
FTNGP12.phx.gbl...
Kriste
See, if this helps you
CREATE TABLE #Sales
(
SaleId INT NOT NULL PRIMARY KEY,
SaleDate DATETIME NOT NULL,
ItemCode CHAR(6) NOT NULL,
Quantity INT
)
INSERT INTO #Sales VALUES (1,'2005-01-02', 'item_A',80)
INSERT INTO #Sales VALUES (2,'2005-01-15', 'item_A',23)
INSERT INTO #Sales VALUES (3,'2005-02-04', 'item_A',43)
INSERT INTO #Sales VALUES (4,'2005-02-23', 'item_A',30)
INSERT INTO VALUES (5,'2005-03-13', 'item_A',60)
SELECT * FROM #Sales S
WHERE (
SELECT COUNT(*) FROM #Sales
WHERE ItemCode = S.ItemCode
AND SaleId > S.SaleId
) <= 1
ORDER BY SaleId ASC
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:%23zGe4WGVFHA.3076@.T
K2MSFTNGP12.phx.gbl...
Hi Everybody,
I've provide a sample Purchase Order with some data. I need to maintain a FI
FO (first-in-first-out) for the the sale of item and at the end of the day,
will generate a report which are the Purchase Order with item still unsold.
Example, sold 150 items (80 from id=1, 23 from id=2, 43 from id=3 and 4 from
id=4) , so the query need to display id 4 (still remain with 26) and id 5 (
still remain with 60)
Can someone help me on this query?
Sample Purchase Order table
id date item_code quantity
-- -- -- --
1 2005-01-02 item_A 80
2 2005-01-15 item_A 23
3 2005-02-04 item_A 43
4 2005-02-23 item_A 30
5 2005-03-13 item_A 60
Thanks in advance,
Kriste|||Thank Uri, it works.
Btw, the DDL has already been included in my 1st post.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OPJNzbIVFHA.1604@.TK2MS
FTNGP10.phx.gbl...
Kriste
Now , you are explained the problem by posting DDL
CREATE TABLE #Sales
(
SaleId INT NOT NULL PRIMARY KEY,
SaleDate DATETIME NOT NULL,
ItemCode CHAR(6) NOT NULL,
Quantity INT
)
INSERT INTO #Sales VALUES (1,'2005-01-02', 'item_A',80)
INSERT INTO #Sales VALUES (2,'2005-01-15', 'item_A',23)
INSERT INTO #Sales VALUES (3,'2005-02-04', 'item_A',43)
INSERT INTO #Sales VALUES (4,'2005-02-23', 'item_A',30)
INSERT INTO #Sales VALUES (5,'2005-03-13', 'item_A',60)
Declare @.Q INT
SET @.Q=100
SELECT * FROM
(
SELECT Saleid,(SELECT SUM(Quantity) FROM #Sales S
WHERE #Sales.Saleid>=S.Saleid )AS Ac
FROM #Sales
) AS DER WHERE Ac>=@.q
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:%23gB%23ePIVFHA.3544
@.TK2MSFTNGP12.phx.gbl...
Hi Uri,
I think you didn't get my question right. The data here is of Purchase Order
. I have a accumulating sales quantity.
As the sales quantity increase, I need to find out which Purchase Order quan
ity is still unsold.
Eg.
Sold sales quantity = 50, so display Purchase Order 1 to 5
Sold sales quantity = 100, so display Purchase Order 2 to 5
Sold sales quantity = 150, so display Purchase Order 4 to 5
Sample Purchase Order table
id date item_code quantity accumulated inventory
-- -- -- -- --
1 2005-01-02 item_A 80 + 80 = 80
2 2005-01-15 item_A 23 + 23 = 103
3 2005-02-04 item_A 43 + 43 = 146
4 2005-02-23 item_A 30 + 30 = 176
5 2005-03-13 item_A 60 + 60 = 236
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23rT3DHIVFHA.3176@.TK2
MSFTNGP12.phx.gbl...
Kriste
Can you explain if i add INSERT INTO #Sales VALUES (6,'2005-03-13', 'item_A'
,100) why do I should get back from 2 to 5?
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:eEYiF4HVFHA.1404@.TK2
MSFTNGP09.phx.gbl...
Can u explain how does this query works? I don't quite understand.
If now the sold quantity = 100, how will it return the id=2 to 5?
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OHfl5gGVFHA.4092@.TK2MS
FTNGP12.phx.gbl...
Kriste
See, if this helps you
CREATE TABLE #Sales
(
SaleId INT NOT NULL PRIMARY KEY,
SaleDate DATETIME NOT NULL,
ItemCode CHAR(6) NOT NULL,
Quantity INT
)
INSERT INTO #Sales VALUES (1,'2005-01-02', 'item_A',80)
INSERT INTO #Sales VALUES (2,'2005-01-15', 'item_A',23)
INSERT INTO #Sales VALUES (3,'2005-02-04', 'item_A',43)
INSERT INTO #Sales VALUES (4,'2005-02-23', 'item_A',30)
INSERT INTO VALUES (5,'2005-03-13', 'item_A',60)
SELECT * FROM #Sales S
WHERE (
SELECT COUNT(*) FROM #Sales
WHERE ItemCode = S.ItemCode
AND SaleId > S.SaleId
) <= 1
ORDER BY SaleId ASC
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:%23zGe4WGVFHA.3076@.T
K2MSFTNGP12.phx.gbl...
Hi Everybody,
I've provide a sample Purchase Order with some data. I need to maintain a FI
FO (first-in-first-out) for the the sale of item and at the end of the day,
will generate a report which are the Purchase Order with item still unsold.
Example, sold 150 items (80 from id=1, 23 from id=2, 43 from id=3 and 4 from
id=4) , so the query need to display id 4 (still remain with 26) and id 5 (
still remain with 60)
Can someone help me on this query?
Sample Purchase Order table
id date item_code quantity
-- -- -- --
1 2005-01-02 item_A 80
2 2005-01-15 item_A 23
3 2005-02-04 item_A 43
4 2005-02-23 item_A 30
5 2005-03-13 item_A 60
Thanks in advance,
Kriste|||Right ,but without accumulated column.
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:%2353RfjIVFHA.3312@.T
K2MSFTNGP09.phx.gbl...
Thank Uri, it works.
Btw, the DDL has already been included in my 1st post.
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OPJNzbIVFHA.1604@.TK2MS
FTNGP10.phx.gbl...
Kriste
Now , you are explained the problem by posting DDL
CREATE TABLE #Sales
(
SaleId INT NOT NULL PRIMARY KEY,
SaleDate DATETIME NOT NULL,
ItemCode CHAR(6) NOT NULL,
Quantity INT
)
INSERT INTO #Sales VALUES (1,'2005-01-02', 'item_A',80)
INSERT INTO #Sales VALUES (2,'2005-01-15', 'item_A',23)
INSERT INTO #Sales VALUES (3,'2005-02-04', 'item_A',43)
INSERT INTO #Sales VALUES (4,'2005-02-23', 'item_A',30)
INSERT INTO #Sales VALUES (5,'2005-03-13', 'item_A',60)
Declare @.Q INT
SET @.Q=100
SELECT * FROM
(
SELECT Saleid,(SELECT SUM(Quantity) FROM #Sales S
WHERE #Sales.Saleid>=S.Saleid )AS Ac
FROM #Sales
) AS DER WHERE Ac>=@.q
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:%23gB%23ePIVFHA.3544
@.TK2MSFTNGP12.phx.gbl...
Hi Uri,
I think you didn't get my question right. The data here is of Purchase Order
. I have a accumulating sales quantity.
As the sales quantity increase, I need to find out which Purchase Order quan
ity is still unsold.
Eg.
Sold sales quantity = 50, so display Purchase Order 1 to 5
Sold sales quantity = 100, so display Purchase Order 2 to 5
Sold sales quantity = 150, so display Purchase Order 4 to 5
Sample Purchase Order table
id date item_code quantity accumulated inventory
-- -- -- -- --
1 2005-01-02 item_A 80 + 80 = 80
2 2005-01-15 item_A 23 + 23 = 103
3 2005-02-04 item_A 43 + 43 = 146
4 2005-02-23 item_A 30 + 30 = 176
5 2005-03-13 item_A 60 + 60 = 236
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23rT3DHIVFHA.3176@.TK2
MSFTNGP12.phx.gbl...
Kriste
Can you explain if i add INSERT INTO #Sales VALUES (6,'2005-03-13', 'item_A'
,100) why do I should get back from 2 to 5?
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:eEYiF4HVFHA.1404@.TK2
MSFTNGP09.phx.gbl...
Can u explain how does this query works? I don't quite understand.
If now the sold quantity = 100, how will it return the id=2 to 5?
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OHfl5gGVFHA.4092@.TK2MS
FTNGP12.phx.gbl...
Kriste
See, if this helps you
CREATE TABLE #Sales
(
SaleId INT NOT NULL PRIMARY KEY,
SaleDate DATETIME NOT NULL,
ItemCode CHAR(6) NOT NULL,
Quantity INT
)
INSERT INTO #Sales VALUES (1,'2005-01-02', 'item_A',80)
INSERT INTO #Sales VALUES (2,'2005-01-15', 'item_A',23)
INSERT INTO #Sales VALUES (3,'2005-02-04', 'item_A',43)
INSERT INTO #Sales VALUES (4,'2005-02-23', 'item_A',30)
INSERT INTO VALUES (5,'2005-03-13', 'item_A',60)
SELECT * FROM #Sales S
WHERE (
SELECT COUNT(*) FROM #Sales
WHERE ItemCode = S.ItemCode
AND SaleId > S.SaleId
) <= 1
ORDER BY SaleId ASC
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:%23zGe4WGVFHA.3076@.T
K2MSFTNGP12.phx.gbl...
Hi Everybody,
I've provide a sample Purchase Order with some data. I need to maintain a FI
FO (first-in-first-out) for the the sale of item and at the end of the day,
will generate a report which are the Purchase Order with item still unsold.
Example, sold 150 items (80 from id=1, 23 from id=2, 43 from id=3 and 4 from
id=4) , so the query need to display id 4 (still remain with 26) and id 5 (
still remain with 60)
Can someone help me on this query?
Sample Purchase Order table
id date item_code quantity
-- -- -- --
1 2005-01-02 item_A 80
2 2005-01-15 item_A 23
3 2005-02-04 item_A 43
4 2005-02-23 item_A 30
5 2005-03-13 item_A 60
Thanks in advance,
Kriste|||hi
you can easly turn it to a sp
declare @.salessum int
declare @.id int,@.quantity int
declare unsold cursor for
select id, quantity
from porders
order by id
open unsold
declare @.sum int
set @.sum=0
set @.salessum=150
fetch next from unsold
into @.id,@.quantity
WHILE @.sum<=@.salessum
BEGIN
set @.sum=@.sum+@.quantity
FETCH NEXT FROM unsold
INTO @.id,@.quantity
END
CLOSE unsold
DEALLOCATE unsold
set @.sum=@.sum-@.salessum
set @.id=@.id-1
select id,date,item_code,@.sum as remain from porders where id=@.id
union all
select id,date,item_code,quantity as remain from porders where id>@.id
"Kriste L" wrote:

> Hi Everybody,
> I've provide a sample Purchase Order with some data. I need to maintain a
FIFO (first-in-first-out) for the the sale of item and at the end of the day
, will generate a report which are the Purchase Order with item still unsold
.
> Example, sold 150 items (80 from id=1, 23 from id=2, 43 from id=3 and 4 fr
om id=4) , so the query need to display id 4 (still remain with 26) and id 5
(still remain with 60)
> Can someone help me on this query?
> Sample Purchase Order table
> id date item_code quantity
> -- -- -- --
> 1 2005-01-02 item_A 80
> 2 2005-01-15 item_A 23
> 3 2005-02-04 item_A 43
> 4 2005-02-23 item_A 30
> 5 2005-03-13 item_A 60
> Thanks in advance,
> Kriste
>|||previous code i send may couse problems sorry:(
this is much better and safe:)
declare @.salessum int
declare @.id int,@.quantity int
declare unsold cursor for
select id, quantity
from porders
order by id
open unsold
declare @.sum int
set @.sum=0
set @.salessum=150
fetch next from unsold
into @.id,@.quantity
WHILE @.@.fetch_status=0
BEGIN
set @.sum=@.sum+@.quantity
if @.sum>@.salessum
begin
set @.sum=@.sum-@.salessum
select id,date,item_code,@.sum as remain from porders where id=@.id
union all
select id,date,item_code,quantity as remain from porders where id>@.id
end
else
FETCH NEXT FROM unsold
INTO @.id,@.quantity
END
CLOSE unsold
DEALLOCATE unsold
"POKEMON" wrote:
> hi
> you can easly turn it to a sp
> declare @.salessum int
> declare @.id int,@.quantity int
> declare unsold cursor for
> select id, quantity
> from porders
> order by id
> open unsold
> declare @.sum int
> set @.sum=0
> set @.salessum=150
> fetch next from unsold
> into @.id,@.quantity
> WHILE @.sum<=@.salessum
> BEGIN
> set @.sum=@.sum+@.quantity
> FETCH NEXT FROM unsold
> INTO @.id,@.quantity
> END
> CLOSE unsold
> DEALLOCATE unsold
>
> set @.sum=@.sum-@.salessum
> set @.id=@.id-1
> select id,date,item_code,@.sum as remain from porders where id=@.id
> union all
> select id,date,item_code,quantity as remain from porders where id>@.id
> "Kriste L" wrote:
>