sql - Subtotaling with Ranges and Limited Compatibility -
on sql server 2008 r2, trying create stored procedure give user subtotals of commodities based on inputted range. appreciate assistance.
however, ran problem in query displays commodities being calculated user ends values exceed inputted range. i'll include sample data on bottom have tried.
here's example (that took out columns in clarity in example):
as can tell, range between 2000 , 10000, commodity 998-32 going exceed 10000 in subtotals still displays since each po no individually less 10000.
here sample data:
drop table ##mytable; create table ##mytable( commodity varchar(15) not null ,po_no integer not null ,line_no integer not null ,po_line_description varchar(82) ,commodity_description varchar(60) not null ,fiscal_year integer not null ,vendor_id integer not null ,vendor_name varchar(20) not null ,quantity integer not null ,unit_cost numeric(7,2) not null ,line_amount numeric(7,2) not null ); insert ##mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-18',1448923,1,'face scholastic book order attached.','sale of surplus , obsolete books',2015,47650,'scholastic inc',1,9999.8,9999.8); insert ##mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-32',1416311,2,'first 12 months maintenance agreement billed quarterly @ .0039 per b/w copy','sale of surplus , obsolete copy machines',2015,341479,'ricoh usa, inc',1,5148,5148); insert ##mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-32',1424377,1,null,'sale of surplus , obsolete copy machines',2015,300590,'konica minolta/ cit',1,2894.58,2894.58); insert ##mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-32',1404031,1,'1st (12) months of (36) month lease payment (1) mpc4503 copier.','sale of surplus , obsolete copy machines',2015,341479,'ricoh usa, inc',1,2050.68,2050.68); insert ##mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-75',1401552,1,'blanket order 50 teachers - each teacher not exceed $100.00.','sale of surplus , obsolete paper , paper products',2015,27536,'knowledge tree',1,5000,5000); insert ##mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-78',1521390,1,'pl02286>pressure plumber instant drain opener 24 shot cartridge','sale of surplus , obsolete plumbing equipment , supplies',2015,402985,'tech mech supply llc',480,8,3840); i have tried using sum() on (partition [ ] order [ ] rows unbounded preceding) apparently on clause row introduced in sql server 2012 , not work 2008 r2.
i have tried using group rollup message says "the cube() , rollup() grouping constructs not allowed in current compatibility mode. allowed in 100 mode or higher." when asked our dba, said can't move 100 mode 90 mode because lot of things break.
so i'm stuck below query, has problem stated earlier, issue of giving me data want subtotals of data exceed specified range.
p.s. have noticed if commodity has po nos within selected range po nos costs outside of it, query give me within selected range misleading since still commodity outside selected range if calculated. shouldn't listed results @ commodities within selected range.
alter procedure [dbo].[poreport] ( @param1 int ,@param2 int ,@param3 int ) begin set nocount on; select distinct row_number() on ( order t.product_id ,t.po_no ,t.line_no ) [rowid] ,isnull(t.product_id, 'null') [commodity] ,isnull(t.po_no, 'null') [po no] ,isnull(t.line_no, 'null') [line no] ,quotename(t.description, '"') [po line description] ,quotename(c.description, '"') [commodity description] ,isnull(t.fy, 'null') [fiscal year] ,ph.vendor_id [vendor id] ,quotename(v.vendor_name, '"') [vendor name] ,t.quantity ,t.unit_cost ,t.quantity * t.unit_cost [line amount] ,( select cast(0.00 numeric(10, 2)) ) sub_total_cost ##tmpporeport dbo.dbvw_fi_req_po_items t inner join dbo.fi_vendor fv on t.inst_id = fv.inst_id inner join dbo.fi_req_po_header ph on t.po_no = ph.po_no inner join dbo.fi_vendor v on ph.vendor_id = v.vendor_id inner join dbo.fi_commodity c on t.product_id = c.fi_commodity_code t.inst_id = 'sc00' , t.fy = @param1 , v.vendor_type = 'v' , t.po_no not null , ( t.product_id <> '' , t.product_id not null ) , t.quantity * t.unit_cost between @param2 , @param3 group t.product_id ,t.po_no ,t.line_no ,t.description ,c.description ,t.fy ,ph.vendor_id ,v.vendor_name ,t.quantity ,t.unit_cost ,ph.created_date order commodity declare @pid varchar(15) = 00 ,@quantity int ,@unit_cost numeric(10, 2) ,@previd varchar(15) ,@rowid bigint ,@prevrowid bigint ,@rowamount numeric(10, 2) ,@subtotal numeric(10, 2) = 0.00 set numeric_roundabort off; while exists ( select top 1 * ##tmpporeport sub_total_cost = 0.00 ) begin set @rowamount = ( select top 1 (quantity * unit_cost) ##tmpporeport sub_total_cost = 0.00 ) select top 1 @pid = commodity ,@rowid = rowid ##tmpporeport sub_total_cost = 0.00 if (@pid = @previd) , (@rowid <> @prevrowid) begin set @subtotal += @rowamount; update t set sub_total_cost = @subtotal ##tmpporeport t t.commodity = @pid , rowid = @rowid set @previd = @pid; set @prevrowid = @rowid end else begin set @subtotal = @rowamount; update t set sub_total_cost = @subtotal ##tmpporeport t t.commodity = @pid , rowid = @rowid set @previd = @pid; set @prevrowid = @rowid end end set numeric_roundabort on; select * ##tmpporeport [line amount] between @param2 , @param3 drop table ##tmpporeport end thanks!
sorry if not clear. if want duplicate sum on rows need use cte or similar sub query below. not optimized query give running sum on commodity. uses rank() on partition instance or row number per po within commodity.
declare @mytable table( commodity varchar(15) not null ,po_no integer not null ,line_no integer not null ,po_line_description varchar(82) ,commodity_description varchar(60) not null ,fiscal_year integer not null ,vendor_id integer not null ,vendor_name varchar(20) not null ,quantity integer not null ,unit_cost numeric(7,2) not null ,line_amount numeric(7,2) not null ); insert @mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-18',1448923,1,'face scholastic book order attached.','sale of surplus , obsolete books',2015,47650,'scholastic inc',1,9999.8,9999.8); insert @mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-32',1416311,2,'first 12 months maintenance agreement billed quarterly @ .0039 per b/w copy','sale of surplus , obsolete copy machines',2015,341479,'ricoh usa, inc',1,5148,5148); insert @mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-32',1424377,1,null,'sale of surplus , obsolete copy machines',2015,300590,'konica minolta/ cit',1,2894.58,2894.58); insert @mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-32',1404031,1,'1st (12) months of (36) month lease payment (1) mpc4503 copier.','sale of surplus , obsolete copy machines',2015,341479,'ricoh usa, inc',1,2050.68,2050.68); insert @mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-75',1401552,1,'blanket order 50 teachers - each teacher not exceed $100.00.','sale of surplus , obsolete paper , paper products',2015,27536,'knowledge tree',1,5000,5000); insert @mytable(commodity,po_no,line_no,po_line_description,commodity_description,fiscal_year,vendor_id,vendor_name,quantity,unit_cost,line_amount) values ('998-78',1521390,1,'pl02286>pressure plumber instant drain opener 24 shot cartridge','sale of surplus , obsolete plumbing equipment , supplies',2015,402985,'tech mech supply llc',480,8,3840); declare @fiscalyear int declare @lowtotalcost int declare @hightotalcost int set @fiscalyear=2015 set @lowtotalcost=1000 set @hightotalcost=10000 select * ( select commodity,po_no,quantity,unit_cost,line_amount, runningtotal= ( select sum(totalcost) ( select commodity,totalcost =(mt.quantity * mt.unit_cost), instance=rank()over(partition commodity order commodity,po_no desc) @mytable mt (mt.quantity * mt.unit_cost) between @lowtotalcost , @hightotalcost , (mt.fiscal_year=@fiscalyear) )as y (y.commodity=x.commodity) and(y.instance<=x.instance) ) ( select commodity,po_no,quantity,unit_cost,line_amount, totalcost = (mt.quantity * mt.unit_cost), instance=rank()over(partition commodity order commodity,po_no desc) @mytable mt --is filter here -->(mt.quantity * mt.unit_cost) between @lowtotalcost , @hightotalcost --and (mt.fiscal_year=@fiscalyear) )as x )as z (commodity in ( select commodity ( select commodity, total=sum(quantity * unit_cost) @mytable mt2 mt2.fiscal_year=@fiscalyear group commodity )as a.total between @lowtotalcost , @hightotalcost ) ) select * @mytable mt 
Comments
Post a Comment