for report creating need find shrink. shrink difference. input weight (user generated) subtracted sum of total weight (originalquantity_stk)
currently using expression
sum(@inputweight - [ic_productlots].[originalquantity_stk]) [shrink] i tried use
sum(@inputweight - [weight]) [shrink] because grand total of weight calculated:
sum(ic_productlots.originalquantity_stk) [weight] when user runs report enter information , 1 piece of information input weight. example enter 20,000. if total weight adds 19,800, shrink should equal 200.
an example output this:
input weight: 20,000
product # | weight | shrink
1234 | 12,000 |
1235 | 6,500 |
1236 | 500 |
1237 | 800 |
--------------------------
total |19,800 | 200
let me know if makes sense or not.
i using microsoft sql sever 2005.
full code reference:
set nocount on; declare @purchasecost decimal(19,8); declare @inputweight decimal(19,8); set @purchasecost = 2.58; set @inputweight = 20000; select distinct 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(round([dct].[quantity_stk] *[icp].[unitcost], 2)) [total sales] , avg(([ic_productlots].[originalquantity_stk] / [ic_productlots].[originalquantity_alt])) [avg. box weight] , sum([ic_productlots].[originalquantity_stk] / @inputweight) [yield] , sum(@inputweight - [ic_productlots].[originalquantity_stk]) [shrink] (((( 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)) , ([arc].customercode = ' 904') 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].customercode order cast([arc].[customercode] nvarchar(40)) + ' - ' + cast([arc].[name] nvarchar(40))
pretty sure want take sum of weight minus input value.
sum([weight]) - @inputweight [shrink] or maybe is
@inputweight - sum([weight]) [shrink] 
Comments
Post a Comment