[QUERY] Arranging records - 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

SQL Server 2005 Forums

Transact-SQL (2005)

[QUERY] Arranging records

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2008-12-12 : 04:27:17

Hi,

I'm currently working on query using first-in-first-out concept.

Please can anyone help to look at the query and suggest how to produce the desired result? Thanks in advanced.

CREATE TABLE [FIFO] (
ItemCode NVARCHAR(20) NOT NULL,
Warehouse NVARCHAR(8) NOT NULL,
TransNum INT NOT NULL,
DocDate datetime,
InQty numeric(19,6) ,
OutQty numeric(19,6),
OpenQty numeric(19,6),
CostPrice numeric(19,6))

insert into [fifo] values ('ItemA','WH01',1,'20081101','50', '0','0','10')
insert into [fifo] values ('ItemA','WH01',2,'20081105','50', '0','0', '8')
insert into [fifo] values ('ItemA','WH01',3,'20081110', '0','40','0','10')
insert into [fifo] values ('ItemA','WH01',4,'20081130', '0', '5','0','10')
insert into [fifo] values ('ItemA','WH01',5,'20081129','10', '0','0','10')
insert into [fifo] values ('ItemA','WH01',6,'20081130', '0', '3','0','10')
insert into [fifo] values ('ItemA','WH01',7,'20081130', '0','17','0','10')

--expected result:
--
--1. 'ItemA','WH01',1,'20081101','50', '0','2','10') -- IN (50 pcs of 1st batch)
--2. 'ItemA','WH01',3,'20081110', '0','40','0','10') -- OUT (40 pcs from 1st batch)
--3. 'ItemA','WH01',4,'20081130', '0', '5','0','10') -- OUT ( 5 pcs from 1st batch)
--4. 'ItemA','WH01',6,'20081130', '0', '3','0','10') -- OUT ( 3 pcs from 1st batch -> open qty for 1st batch = 2pcs)
--5. 'ItemA','WH01',2,'20081105','50', '0','33', '8') -- IN (50 pcs of 2nd batch)
--6. 'ItemA','WH01',7,'20081130', '0', 17','0','10') -- OUT (17 pcs from 2nd batch -> open qty for 2nd batch = 33pcs)
--7. 'ItemA','WH01',5,'20081129','10', '0','10','10') -- IN (10 pcs of 3rd batch -> open qty for 3rd batch = 10pcs)
--
--
--Note:
--Intentionally, record no.6 is taken from 2nd batch although 1st batch still has 2 pcs left.

cheers'
erwine

... sql is fun...

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-12 : 07:10:02

Can you clarify what is the difference between what you get and what you want?
The date in 7. is different but is this a typo? Is it just the order of a SELECT you want to change?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 09:31:29

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

erwine
Yak Posting Veteran

69 Posts

Posted - 2008-12-13 : 01:20:47

Hi there,

Thanks for replying. It's not a typo, below is the expected result based on FIFO concept with a little bit of modification (see record no.6, FIFO will get 2pcs from 1st batch, but I do not want that, I want to take all from 2nd batch unless if the purchased qty is 2).

--expected result:
--
--1. 'ItemA','WH01',1,'20081101','50', '0','2','10') -- IN (50 pcs of 1st batch)
--2. 'ItemA','WH01',3,'20081110', '0','40','0','10') -- OUT (40 pcs from 1st batch)
--3. 'ItemA','WH01',4,'20081130', '0', '5','0','10') -- OUT ( 5 pcs from 1st batch)
--4. 'ItemA','WH01',6,'20081130', '0', '3','0','10') -- OUT ( 3 pcs from 1st batch -> open qty for 1st batch = 2pcs)
--5. 'ItemA','WH01',2,'20081105','50', '0','33', '8') -- IN (50 pcs of 2nd batch)
--6. 'ItemA','WH01',7,'20081130', '0', 17','0','10') -- OUT (17 pcs from 2nd batch -> open qty for 2nd batch = 33pcs)
--7. 'ItemA','WH01',5,'20081129','10', '0','10','10') -- IN (10 pcs of 3rd batch -> open qty for 3rd batch = 10pcs)

... sql is fun...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 02:06:37

i still cant understand what basis you select OpenQty

erwine
Yak Posting Veteran

69 Posts

Posted - 2008-12-15 : 22:38:20

for records with outqty > 0, it should consume the first batch of items came in. if outqty is larger than the open qty of the consumed batch, it will jump to the second received batch.

... sql is fun...

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-17 : 05:02:35

start with this


DECLARE		@Fifo TABLE  
(		
			ItemCode NVARCHAR(20) NOT NULL, Warehouse NVARCHAR(8) NOT NULL, 
			TransNum INT NOT NULL, DocDate DATETIME, InQty NUMERIC(19,6),
			OutQty NUMERIC(19,6), OpenQty NUMERIC(19,6), CostPrice NUMERIC(19,6)
)

DECLARE		@T TABLE  
(		
			ItemCode NVARCHAR(20) NOT NULL, Warehouse NVARCHAR(8) NOT NULL, 
			TransNum INT NOT NULL, DocDate DATETIME, InQty NUMERIC(19,6),
			OutQty NUMERIC(19,6), OpenQty NUMERIC(19,6), CostPrice NUMERIC(19,6),
			Tot NUMERIC(19, 6), Source INT
)


INSERT INTO @Fifo VALUES ('ItemA', 'WH01', 1, '20081101', '50', '0', '0', '10')
INSERT INTO @Fifo VALUES ('ItemA', 'WH01', 2, '20081105', '23', '0', '0', '8')
INSERT INTO @Fifo VALUES ('ItemA', 'WH01', 3, '20081110', '0', '40', '0', '10')
INSERT INTO @Fifo VALUES ('ItemA', 'WH01', 4, '20081130', '0', '5', '0', '10')
INSERT INTO @Fifo VALUES ('ItemA', 'WH01', 5, '20081129', '10', '0', '0', '10')
INSERT INTO @Fifo VALUES ('ItemA', 'WH01', 6, '20081130', '0', '3', '0', '10')
INSERT INTO @Fifo VALUES ('ItemA', 'WH01', 7, '20081130', '0', '17', '0', '10')


DECLARE		@TransNum INT
SELECT		@TransNum = 1

WHILE @TransNum IS NOT NULL
BEGIN
		DECLARE 	@InQty NUMERIC(19, 6)
		SELECT		@InQty = InQty 
		FROM		@Fifo 
		WHERE		TransNum = @TransNum
		
		INSERT INTO @T
		SELECT		*, NULL, NULL 
		FROM		@Fifo 
		WHERE		TransNum = @TransNum
		
		INSERT INTO @T
		SELECT		F.*, CT.*, @TransNum AS 'Source'				
		FROM @Fifo	F
		CROSS APPLY (
							SELECT	SUM(OutQty) AS 'Tot' 
							FROM	@Fifo F2
							WHERE	TransNum <= F.TransNum
									AND TransNum NOT IN ( SELECT TransNum FROM @T )) CT
		WHERE		CT.Tot <= @InQty
					AND F.OutQty <> 0
					AND TransNum NOT IN ( SELECT TransNum FROM @T)
				
		SELECT		@TransNum = MIN(TransNum) 
		FROM		@Fifo 
		WHERE		InQty <> 0 
					AND TransNum > @TransNum
END

UPDATE	@T
SET		OpenQty = ISNULL(F.InQty - T.Tot, 0)
FROM		@T F
INNER JOIN ( 
					SELECT	*, ROW_NUMBER() OVER (PARTITION BY Source ORDER BY TransNum DESC) AS 'Seq' 
					FROM	@T
					WHERE	Source IS NOT NULL
			) T ON T.Source = F.TransNum
WHERE		T.Seq = 1


SELECT	ItemCode, Warehouse, TransNum, DocDate, InQty, OutQty, OpenQty, CostPrice
FROM		@T

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources