SP to manager Control Lot and Expiratiion Date

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

SQL Server 2000 Forums

SQL Server Development (2000)

SP to manager Control Lot and Expiratiion Date

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2012-01-04 : 11:42:18

HI, i need some help whith this Stored Procedure i need to do.

TableName Lote

Dep Char(03)
Code Char(14)
Lot Char(15)
ExpDate SmalldateTime
Qty int

Sample Table "Lote"

Seq Dep Code Lot ExpDate Qty
1 001 123 AB 01/12/2012 5
2 001 123 AX 01/11/2013 3
3 001 123 BW 15/04/2014 4
4 001 123 TK 02/05/2015 8

How can do to do this?
The user type Dep, Code and Qty as Input.

Option A:

user type Qty = 5
I need to get

row 1 = 001 123 AB 01/12/2012 5

user type Qty = 2
I need to get

row 1 = 001 123 AB 01/12/2012 5

user type Qty = 7
I need to get

row 1 = 001 123 AB 01/12/2012 5
2 = 001 123 AX 01/11/2013 3

user type Qty = 9
I need to get

row 1 = 001 123 AB 01/12/2012 5
2 = 001 123 AX 01/11/2013 3
3 = 001 123 BW 15/04/2014 4

user type Qty = 25 (this is easy)

I need a Msg error I have not , i have Only 20

How can do i Script, SP, etc to manager this situation?

tks

Carlos Lages
Rio de Janeiro

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-17 : 16:17:30

Hello Clages1,

How about something like the following?


SELECT 
	l.* 
FROM 
	Lote l
WHERE 
	l.Seq <= 
(
	SELECT MIN(Seq) Seq
	FROM
	(
		SELECT 
			Seq, 
			Dep, 
			Code, 
			Lot, 
			ExpDate, 
			Qty, 
			(SELECT SUM(Qty) FROM Lote d WHERE d.Dep = a.Dep and d.Code = a.Code AND d.ExpDate <= a.ExpDate) AS TTLQty
		FROM Lote a
	) d
	WHERE d.TTLQty >= @Qty
)

Clages1
Yak Posting Veteran

69 Posts

Posted - 2012-01-30 : 15:26:43

tks, but there is a problem
the column SEQ doesnt exist, I put in the forun
just to clarify , to show what rows is to be returned

att

Carlos Lages

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources