quote:

---

USE sum_stock_2013                       
begin
select itm_id
      ,itm_code
      ,itm_name
      ,price
      ,min_price
      ,open_balance
      ,end_open_balance
      ,value_cost
      ,doc_date
      ,suppliers
      ,reservation_qty
      ,order_qty
      ,sup_cat
      ,act_no
      ,act_name
      ,sanad
      ,type_namee
      ,type_no
      --,trn_date
      ,qty_out
      ,qty_in
      ,raseed
      ,storeno
from  (
      select
       distinct(i.itmid)     itm_id 
      ,i.code       itm_code       
      ,i.arbname    itm_name
      ,i.SelPrc1      price
      ,i.SelPrc1      min_price
      ,(select sum(itmo.StartBal) from itmonhand itmo where itmo.itmid= i.itmid)open_balance
      ,''end_open_balance
      ,ic.ItmCstPrc00 value_cost
      ,''doc_date
      ,''suppliers
      ,''reservation_qty
      ,''order_qty
      ,''sup_cat
      ,''act_no
      ,''act_name
      ,''sanad
      ,''type_namee
      ,''type_no
      --,trn_date
      ,''qty_out
      ,''qty_in
      ,''raseed
      ,''storeno
      
      from items as i
      left join itmonhand as itmo on (i.itmid = itmo.itmid)
      left join itmcost   as ic   on (i.itmid = ic.itmid)
union all

select td.itmid  itm_id
      ,''itm_code
      ,''itm_name
      ,''price
      ,''min_price
      ,''open_balance
      ,''end_open_balance
      ,''value_cost
      ,th.invdate doc_date
      ,''suppliers
      ,''reservation_qty
      ,''order_qty
      ,''sup_cat
      ,th.AcctID act_no
      ,th.InvCustName act_name
      ,th.InvNo sanad
      ,tt.arbname type_namee
      ,th.TrnID type_no
      --,trn_date
      ,case when td.invqty *-1 < 0 then 0 
       else td.invqty *-1
       end qty_out   --td.invqty *-1--(select sum(invqty)*-1 from trndet where itmid = i.itmid)
      ,case when td.invqty *-1 < 0 then td.invqty 
       else 0
       end qty_in

--################################################ MY Problem here

      ,(
select
rec
--,itm_no
--,qty

,(select SUM(QTY)
from (
select 1 rec
,itmo.itmid itm_no
,sum(itmo.StartBal) qty

from  itmonhand itmo
where itmid =33516
group by itmo.itmid
union 
select row_number() over (order by td.itmid) +1  rec
,td.itmid itm_no
,td.invqty qty

from  trndet td
where td.itmid =33516
)A

where REC<=MOV.REC --
)qty

from ( 
select         1 rec
,itmo.itmid itm_no
,sum(itmo.StartBal) qty

from  itmonhand itmo
where itmid =33516
group by itmo.itmid
union
select row_number() over (order by td.itmid)  +1 rec
,td.itmid itm_no
,td.invqty qty

from  trndet td
where td.itmid =33516)mov
 



) raseed


 --################# ##############################

      ,''storeno
      from trnhead th
           join trndet  td on (th.headid = td.headid)
      left join items   i  on (i.itmid   = td.itmid)
           join trntype tt on (tt.id     = th.trnid)
           
           
           

           
           
)as kk








where   itm_id in(select itmid from items where code = '2003012101002')
end;
--and     qty_out < 0
/*
where doc_date between convert(datetime,'01-01-2013',103)
               and     convert(datetime,'30-06-2013',103)
*/

---