cancel
Showing results for 
Search instead for 
Did you mean: 

Sales Report

Former Member
0 Kudos

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!!

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Vankri,

could you please format your question and query in a readable structure ?

Please use breakets etc.

Regards