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!!
PremRaj kaushik replied
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.