Inventory tracking..result - 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)

Inventory tracking..result

Author

Topic

tran008
Starting Member

38 Posts

Posted - 2008-12-13 : 09:49:16

I have the table below, and would like to run a query display the detail of QTY IN AND OUT of inventory for report purposed.


tbl1:
ROWID	DATE1	      QTYIN	DATE2	      QTYOUT
1	12/1/2008	10000	12/4/2008	5000
2			      12/6/2008	1200
				
result of query:				
ROWID	DATE1	     QTYIN	DATE2	      QTYOUT
1	12/1/2008	5000	12/4/2008	5000
2	12/1/2008	1200	12/6/2008	1200
2	12/1/2008	3800		        0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 09:57:06

is there any field which designates which row details of table should be merged with which records?

tran008
Starting Member

38 Posts

Posted - 2008-12-13 : 10:11:47

Hi Visakh16,

there should not be any for now....

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 10:45:34

[code]
UPDATE t1
SET t1.DATE1=COALESCE(t1.DATE1,t2.DATEIN),
t1.QTYIN=COALESCE(NULLIF(t1.QTYIN,''),t1.QTYOUT)
FROM tbl1 t1
CROSS APPLY(SELECT TOP 1 ROWID,DATE1,QTYIN,DATE2,QTYOUT
FROM tbl1
WHERE ROWID<=t1.ROWID
AND QTYIN > ''
ORDER BY ROWID DESC)t2

INSERT INTO tbl1
SELECT (SELECT MAX(RowID) FROM tbl1 ),
DATE1,
QTYIN-(SELECT SUM(QTYOUT) FROM tbl1 WHERE QTYIN=QTYOUT),
NULL,
0
FROM tbl1
WHERE QTYIN<> QTYOUT

UPDATE tbl1
SET QTYIN= QTYOUT
WHERE QTYIN <> QTYOUT
AND QTYOUT<>0
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 11:03:49

[code]
DECLARE @ROWID int,@DATE1 datetime,@QTYIN int

SELECT TOP 1 @ROWID= ROWID,
@QTYIN= QTYIN,
@DATE1=DATE1,
@QTYOUT=QTYOUT
FROM tbl1
WHERE QTYIN>''

UPDATE tbl1
SET QTYIN= QTYOUT,
@QTYIN=@QTYIN-QTYOUT+CASE WHEN QTYIN>'' AND ROWID <>@ROWID THEN QTYIN ELSE 0 END,
@DATE1=DATEIN,
@ROWID=ROWID
FROM tbl1

INSERT INTO tbl1
SELECT @ROWID,
@DATE1,
@QTYIN,
NULL,
0
[/code]

hai
Yak Posting Veteran

84 Posts

Posted - 2008-12-13 : 11:21:45

Visakh16,
the result is wrong for the remain Item

*2 2008-12-01 00:00:00.000 3800 NULL 0
Not
*2 2008-12-01 00:00:00.000 8800 NULL 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 11:32:24

[code]
UPDATE t1
SET t1.DATE1=COALESCE(t1.DATE1,t2.DATEIN),
t1.QTYIN=COALESCE(NULLIF(t1.QTYIN,''),t1.QTYOUT)
FROM tbl1 t1
CROSS APPLY(SELECT TOP 1 ROWID,DATE1,QTYIN,DATE2,QTYOUT
FROM tbl1
WHERE ROWID<=t1.ROWID
AND QTYIN > ''
ORDER BY ROWID DESC)t2

INSERT INTO tbl1
SELECT (SELECT MAX(RowID) FROM tbl1 ),
DATE1,
QTYIN-(SELECT SUM(QTYOUT) FROM tbl1),
NULL,
0
FROM tbl1
WHERE QTYIN<> QTYOUT

UPDATE tbl1
SET QTYIN= QTYOUT
WHERE QTYIN <> QTYOUT
AND QTYOUT<>0
[/code]

tran008
Starting Member

38 Posts

Posted - 2008-12-13 : 11:37:36

Thank you very much....

tran008
Starting Member

38 Posts

Posted - 2008-12-13 : 12:20:45

Hi Visakh16,
Sorry to bother you again, but if I make a change to the table. What can I do to get the result below. I have play with you query, and make change, but it is really harder than I expected. Thank you again....

rowid date1 qtyin date2 qtyout
1 12/1/2008 10000 12/4/2008 5,000
2 12/6/2008 1000 12/6/2008 1,200
3 1/1/1900 0 12/8/2008 3,600

result display:
date1 qtyin date2 qtyout
12/01/2008 5000 12/04/2008 5,000
12/01/2008 1200 12/06/2008 1,200
12/01/2008 3600 12/08/2008 3,600
12/01/2008 0200 01/01/1900 0
12/06/2008 1000 01/01/1900 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources