select statement....inventory break down.

Question

Post reply

select statement....inventory break down.

xponcall

SSC Veteran

Points: 252
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

ht-1024570

Valued Member

Points: 51

February 23, 2009 at 9:02 am

#948675

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: 323482 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