on 05-11-2009 8:02 AM
Hi guys, I have the following SQL code
--/* SELECT FROM [dbo].[@EPARAMTABLE] T0 */
--declare @FromDate as DATETIME
--declare @ToDate as DATETIME
--/* WHERE */
--set @fromDate = /* T0.U_fromdate */ '[%0]'
--set @toDate = /* T0.U_todate */ '[%1]'
select
t0.U_eTier as 'Tier'
, t0.itemcode as 'Item Code'
, t0.itemname as 'Item Name'
, t0.sww as 'Item SUN Ref.'
, t3.onhand as 'Stock in 0MAIN01'
, t4.onhand as 'Stock in 1POSBKR'
, t5.onhand as 'Stock in 1POSBUS'
, t6.onhand as 'Stock in 1POSGOZ'
, t7.onhand as 'Stock in 1POSMOB'
, t8.onhand as 'Stock in 1POSPLA'
, t9.onhand as 'Stock in 1POSREP'
, t10.onhand as 'Stock in 1POSTRN'
, t11.onhand as 'Stock in 1POSVLT'
, t12.onhand as 'Stock in 9CONWST'
, t0.onhand as 'Total Stock'
, t0.onorder
, sum(t1.quantity) as 'Quantity Sold'
from
oitm t0 left outer join inv1 t1 on t0.itemcode = t1.itemcode/* and T1.DocDate between @FromDate and @ToDate*/
join oitb t2 on t2.itmsgrpcod = t0.itmsgrpcod
join oitw t3 on t3.itemcode = t0.itemcode and t3.whscode = '0MAIN01'
join oitw t4 on t4.itemcode = t0.itemcode and t4.whscode = '1POSBKR'
join oitw t5 on t5.itemcode = t0.itemcode and t5.whscode = '1POSBUS'
join oitw t6 on t6.itemcode = t0.itemcode and t6.whscode = '1POSGOZ'
join oitw t7 on t7.itemcode = t0.itemcode and t7.whscode = '1POSMOB'
join oitw t8 on t8.itemcode = t0.itemcode and t8.whscode = '1POSPLA'
join oitw t9 on t9.itemcode = t0.itemcode and t9.whscode = '1POSREP'
join oitw t10 on t10.itemcode = t0.itemcode and t10.whscode = '1POSTRN'
join oitw t11 on t11.itemcode = t0.itemcode and t11.whscode = '1POSVLT'
join oitw t12 on t12.itemcode = t0.itemcode and t12.whscode = '9CONWST'
where
t0.QryGroup5 = 'Y' AND t3.whscode in ('0MAIN01','1POSBKR','1POSBUS','1POSGOZ','1POSMOB','1POSPLA','1POSREP','1POSTRN','1POSVLT','9CONWST')
group by
t0.itemcode
, t0.itemname
, t0.sww
, t0.onhand
, t3.onhand
, t4.onhand
, t5.onhand
, t6.onhand
, t7.onhand
, t8.onhand
, t9.onhand
, t10.onhand
, t11.onhand
, t12.onhand
, t0.onorder
, t0.u_etier
Order By
t0.itemcode
This retrieves the Stock per warehouse of a number of items. It also returns the total sales. What I need to add to the report now is a column for sales of each Business Partner (going to choose specific business partners). How do I do this?
I know the probability is that I have to add a function, but I am not able to create the needed function.
Thank you for all your help!!
Hi Venkri,
For this make a stored procedure and make two temp tablesin SP . in first temp table insert the result of ur query and add a new column of bpname in this table , In other table insert bpname and total sales ,And now make joins between these two temp tables and select the data u want .
Hope u got the idea.
Rgds,
Premraj
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vankri,
could you please format your question and query in a readable structure ?
Please use breakets etc.
Regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.