for report need take total sales subtracted cost profit. different expressions have tried, haven't had luck.
to find total sales multiply weight times unit cost . example: [ic_productlots].[originalquantity_stk] *[icp].[unitcost]
total weight = 1,000 unit cost = 3 total sales = 3,000 i need find out how store total sales variable , able use later expression such as:
max(@totalsalesvariable - (@purchasecost + @prod_costlbs) * @inputweight) [profit] currently using expression:
avg([icp].[unitcost]) on (partition [arc].[customercode]) * sum(sum([ic_productlots].[originalquantity_stk])) on (partition [arc].[customercode]) - ( @purchasecost + @prod_costlbs ) * @inputweight but not work because avg unit cost not produce correct number. beginning think can calculate profit if use variable store total sales amount.
catch, variable cannot include where statement because where determined user input @ time of running report.
i using microsoft sql sever 2005
my full code posted below: updated after comment implementing @costofsales
set nocount on; declare @purchasecost decimal(19,8); declare @inputweight decimal(19,8); declare @prod_costlbs decimal(19,8); declare @costofsales decimal(19,8); set @purchasecost = 2.58; set @inputweight = 18100; set @prod_costlbs = .15; set @costofsales = (@purchasecost + @prod_costlbs) * @inputweight; select cast([arc].[customercode] nvarchar(40)) + ' - ' + cast([arc].[name] nvarchar(40)) [supplier] , [pc].productcode , [pc].description1 , count(ic_productlots.originalquantity_alt) [boxes] , ic_productlots.unitofmeasure_alt , sum(ic_productlots.originalquantity_stk) [weight] , ic_productlots.unitofmeasure_stk , [icp].unitcost [unit cost] , sum([ic_productlots].[originalquantity_stk] *[icp].[unitcost]) [total sales] , avg(([ic_productlots].[originalquantity_stk] / [ic_productlots].[originalquantity_alt])) [avg. box weight] , sum([ic_productlots].[originalquantity_stk] / @inputweight) [yield] , @inputweight - sum(sum([ic_productlots].[originalquantity_stk])) on () [shrink] , max((@purchasecost + @prod_costlbs) * @inputweight) [cost] , sum([ic_productlots].[originalquantity_stk] * [icp].[unitcost]) on (partition [arc].[customercode]) - @costofsales [profit] , @costofsales [costofsales] (((( ic_products [pc] inner join dc_transactions [dct] on [pc].productkey = [dct].productkey) inner join ar_customers [arc] on [dct].customerkey = [arc].customerkey) inner join ic_productlots on [dct].lotkey = ic_productlots.lotkey) left outer join ic_productcosts [icp] on icp.productkey=pc.productkey , icp.productcostcode=5) (ic_productlots.productiondate >= { ts '2015-06-24 00:00:00' } , (ic_productlots.productiondate <= { ts '2015-06-24 00:00:00' } or ic_productlots.productiondate null)) , ((1=1) , [arc].customerkey in (124) ) group cast([arc].[customercode] nvarchar(40)) + ' - ' + cast([arc].[name] nvarchar(40)) , [pc].productcode , [pc].description1 , ic_productlots.unitofmeasure_alt , ic_productlots.unitofmeasure_stk , [icp].unitcost , ic_productlots.productiondate , [arc].customerkey , [arc].customercode order cast([arc].[customercode] nvarchar(40)) + ' - ' + cast([arc].[name] nvarchar(40)) , @inputweight - sum(sum([ic_productlots].[originalquantity_stk])) on () , max((@purchasecost + @prod_costlbs) * @inputweight) , sum([ic_productlots].[originalquantity_stk] * [icp].[unitcost]) on (partition [arc].[customercode]) - @costofsales
Comments
Post a Comment