complex querie - 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 2000 Forums

Transact-SQL (2000)

complex querie

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-12-18 : 08:25:29

fereja writes "Realy bad problem

Table1
GoodReceive
------------
Date Recive_no Item Qnt Item_Cost
1/1/2006 R-001 Soap 10 40
1/2/2006 R-002 Soap 15 45
1/2/2006 R-003 shampo 45 55
1/3/2006 R-004 soap 35 50

Table 2
GoodSold
--------------
Date Sold_no Item Qnt
1/2/2006 S-001 Soap 8
1/4/2006 S-002 Soap 10
1/5/2006 S-003 Samop 30

I need a qurey like
--------------------------------
Date Item Recive_No QntRecive Itemcost Total_Cost Sold_No Qnt_Sold SoldValue Remain_Qnt Remain_Value
1/1/2006 soap R-001 10 40 400 - - - 10 400
1/2/2006 soap R-002 15 45 675 - - - 25 1075
1/2/2006 soap - S-001 8 344 17 731
1/3/2006 soap R-004 35 50 1750 - - - 52 2481
1/4/2006 soap - - - - S-002 30 1431.35 22 1049.65

N.B
SoldValue = previous remain_value/Previous remain_qnt"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 08:30:44

Where are the values {1431.35, 1049.65} fetched from?

Peter Larsson
Helsingborg, Sweden

fereja
Starting Member

2 Posts

Posted - 2006-12-19 : 07:54:54

the value 1431.35 the previous row which is (2481/52) * 30
the value 1049.65 comes from the previous record 1049.65 - 1431.35

if is recive value it will added if is sold value it will subtruct

N.B
pleas refer Item costing report on peachtree and pleas help me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources