SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

abhi.87.jain
Starting Member

5 Posts

Posted - 2012-07-24 : 04:57:36

Following is my database

Sysdate Qty Rate
1-4-2011 10 3
1-6-2011 20 3
3-6-2011 5 11
4-8-2011 4 6
12-1-2012 2 3
10-4-2012 25 2

Current Stock is 32

How can i found from sql, about FIFO stock value of qty 32 which is remaining.....

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-07-24 : 06:04:34

You are going to have to give us a lot more information. Is this the stock table? The Sales table?

Do you keep your current stock in a seperate table?

What do you want your results to look like?

prasaditc55
Starting Member

8 Posts

Posted - 2012-07-24 : 06:07:06

Please Provide us information clearly

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-24 : 07:27:08

CREATE TABLE #STOCK
(
Sysdate DATETIME,
Qty INT,
Rate INT
)

INSERT INTO #STOCK
SELECT '1-4-2011', 10, 3
UNION ALL
SELECT '1-6-2011', 20, 3
UNION ALL
SELECT '3-6-2011', 5, 11
UNION ALL
SELECT '4-8-2011', 4, 6
UNION ALL
SELECT '12-1-2012', 2, 3
UNION ALL
SELECT '10-4-2012', 25, 2

Declare @CurrentStock INT
SET @CurrentStock = 32

IF ( SELECT SUM(QTY)
FROM ( SELECT Sysdate, Qty,
isnull(( SELECT SUM(Qty)
FROM #STOCK
WHERE Sysdate < OuterTable.Sysdate ) + QTY,
QTY) AS QTYsum
FROM #STOCK OuterTable ) ABC
WHERE QTYsum <= @CurrentStock ) < @CurrentStock
SELECT TOP ( 1 )
*
FROM ( SELECT Sysdate, Qty,
isnull(( SELECT SUM(Qty)
FROM #STOCK
WHERE Sysdate < OuterTable.Sysdate ) + QTY,
QTY) AS QTYsum
FROM #STOCK OuterTable ) ABC
WHERE QTYsum > @CurrentStock
UNION
SELECT *
FROM ( SELECT Sysdate, Qty,
isnull(( SELECT SUM(Qty)
FROM #STOCK
WHERE Sysdate < OuterTable.Sysdate ) + QTY,
QTY) AS QTYsum
FROM #STOCK OuterTable ) ABC
WHERE QTYsum <= @CurrentStock
ORDER BY Sysdate

ELSE
SELECT *
FROM ( SELECT Sysdate, Qty,
isnull(( SELECT SUM(Qty)
FROM #STOCK
WHERE Sysdate < OuterTable.Sysdate ) + QTY,
QTY) AS QTYsum
FROM #STOCK OuterTable ) ABC
WHERE QTYsum <= @CurrentStock
ORDER BY Sysdate
DROP TABLE #STOCK

--------------------------
http://connectsql.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 09:35:15

quote:
Originally posted by abhi.87.jain

Following is my database

Sysdate Qty Rate
1-4-2011 10 3
1-6-2011 20 3
3-6-2011 5 11
4-8-2011 4 6
12-1-2012 2 3
10-4-2012 25 2

Current Stock is 32

How can i found from sql, about FIFO stock value of qty 32 which is remaining.....

are you looking for running total?

like scenario 1

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhi.87.jain
Starting Member

5 Posts

Posted - 2012-07-27 : 09:17:15

It's a stock table and i want result Rate * Qty as per FIFO basis

abhi.87.jain
Starting Member

5 Posts

Posted - 2012-07-27 : 09:17:16

It's a stock table and i want result Rate * Qty as per FIFO basis

abhi.87.jain
Starting Member

5 Posts

Posted - 2012-07-27 : 09:19:25

No it's not running total...it's stock value...

abhi.87.jain
Starting Member

5 Posts

Posted - 2012-07-27 : 09:26:06

it's not working ...i want rate * qty result...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 10:05:15

quote:
Originally posted by abhi.87.jain

It's a stock table and i want result Rate * Qty as per FIFO basis

what do you mean by FIFO basis? do you've date column to determine order?
also can you explain how you got 32 from sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources