select statement....inventory break down.

Question

Post reply

select statement....inventory break down.

xponcall

Old Hand

Points: 312
More actions
January 29, 2009 at 12:11 pm

#196669

I have the following, and need to be convert and insert into a new table. This could be done in a some sort of reporting tool, but for now this what the client need.
------------------
table1
------
Acct Date Tran Qty
101 10/1/2008 A 500
101 10/2/2008 A 1000
101 10/3/2008 A 200
102 10/5/2008 A 500
102 10/6/2008 A 500
101 11/1/2008 S 200
101 11/2/2008 S 1000
101 11/3/2008 S 200
101 11/4/2008 S 400
102 11/5/2008 S 700
table2 - Need something like this: Base on the Date of Inventory A(Acquire), and S(Ship). Eg: Account 101 had 500 on 10/1/2008, but only 200 was ship on 11/1/2008 which result have 300. left. The 300 will be use to substract from the next ship date.
Acct bDate bTran bQty sDate sTran sQty
101 10/1/2008 A 200 11/1/2008 S 200
101 10/1/2008 A 300 11/2/2008 S 300
101 10/2/2008 A 700 11/2/2008 S 700
101 10/2/2008 A 200 11/3/2008 S 200
101 10/2/2008 A 100 11/4/2008 S 100
101 10/3/2008 A 200 11/4/2008 S 200
101 11/4/2008 S 100
102 10/5/2008 A 500 11/5/2008 S 500
102 10/6/2008 A 200 11/5/2008 S 200
102 10/6/2008 A 300
I been struggle with this all morning, and wonder if anyone have a pointer get this started.
thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ht-1024570 Valued Member Points: 51 More actions · Answer 1

Hi,

Did you come up with a solution? I have a very similar problem, and like to see how you solved it.

thanks

Lowell SSC Guru Points: 323518 More actions · Answer 2

i would simply create a view, rather than insert into a new table.

the view would always be correct, where the table would need to be updated every time table2 got new data.

something like

select table1.acct, (table1.QTY - MyALIAS.QTY)

from table1

left outer join (SELECT ACCT,SUM(QTY) AS QTY

from TABLE2

HROUP BY ACCT) MYALIAS ON TABLE1.ACCT = MYALIAS.ACCT

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ht-1024570 Valued Member Points: 51 More actions · Answer 3

Hi Lowell,

base on you suggestion this is what I came up with from the data posted...

SELECT

A.acct,

SUM(A.qty_buy) buy,

SUM(A.qty_sell) sell,

(SUM(A.qty_buy) - SUM(A.qty_sell)) stock,

MAX(A.[date]) tran_date

FROM

(

SELECT ACCT, qty qty_buy, 0 qty_sell, [date]

FROM #tmpInvTbl P

WHERE [TRAN] = 'A'

union all

SELECT ACCT, 0,qty qty_sell, ''

FROM #tmpInvTbl P

WHERE [TRAN] = 'S'

) A

GROUP BY

A.ACCT

however, I need to view the result of each transactions, similar to the above result, display in full detail because I need further to pull out result transaction base on the critized on the transactions.

thanks