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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-10 : 09:17:52
|
mala writes "Hi,
For the following query I have to calculate sum(inv_tqty-inv_sqty) as opening for inv_Date < '2000-01-11' if exists, otherwise if inv_date is greater than specified date, it should just calculate sum(inv_tqty-inv_sqty) as opening [i.e. my opening and closing balance will be same]
How can I modify this query to get the result. Actually I am using 2 ADO record set to get the result. But I want to make it as 1 record set. Please let me how can I calculate the same.
Select t_inv_details.i_partno,inv_bu,inv_wh_from,inv_zone,inv_stkcatg,inv_date,
sum(inv_tqty-inv_sqty) as Opening,sum(inv_tqty)as Receipts,sum(inv_sqty ) as Issues,sum(inv_tqty-inv_sqty)as Closing
From t_inv_details,m_mat_stock,c_appl_options
Where inv_cancel = 0
And t_inv_details.inv_stkcatg = c_appl_options.c_option1 and inv_stkcatg in
(select c_option1 from c_appl_options where c_appl_options.n_option1 = '1'
and c_appl_options.n_option2 ='1' and options = 'STOCKCTG' and c_option1 between '001' and '009')
And t_inv_details.i_partno = m_mat_stock.i_partno
Group by t_inv_details.i_partno,inv_bu,inv_wh_from,inv_zone,inv_stkcatg,inv_date
Having Sum(inv_tqty) > 0 order by t_inv_Details.i_partno
Awaiting early reply.
Bye,
Mala" |
|
|
|
|
|
|
|
Joins