ahmed_source
Starting Member
1 Post |
Posted - 2013-06-16 : 15:23:44
|
Hello all,i think mt problem between ######please help mequote:
USE sum_stock_2013 beginselect 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 ,storenofrom ( 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 allselect 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 ,(selectrec--,itm_no--,qty,(select SUM(QTY)from (select 1 rec,itmo.itmid itm_no,sum(itmo.StartBal) qtyfrom itmonhand itmowhere itmid =33516group by itmo.itmidunion select row_number() over (order by td.itmid) +1 rec,td.itmid itm_no,td.invqty qtyfrom trndet tdwhere td.itmid =33516)Awhere REC<=MOV.REC --)qtyfrom ( select 1 rec,itmo.itmid itm_no,sum(itmo.StartBal) qtyfrom itmonhand itmowhere itmid =33516group by itmo.itmidunionselect row_number() over (order by td.itmid) +1 rec,td.itmid itm_no,td.invqty qtyfrom trndet tdwhere 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 kkwhere 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)*/
Thank's |
|