sql server - What is the query for this complex job -
i have following tables
materials groups ------------------------------- |grpid | grpname | grpparentid | ------------------------------- materials -------------------------------------- |matid | matname | matgroupid |price | -------------------------------------- stores ----------------------------- |stid | stname | stparentid | ----------------------------- billtype -------------------- |typeid | typename | -------------------- those types
store out store in/price sales return first period inventory sales end period inventory store out/price purchase store in ready product in purchase return sales return1 raw materials out bills ---------------------------------- |billid | billtype| client..|....| ---------------------------------- billitems --------------------------------- |itemid| matid| quantity|billid | --------------------------------- the required show report in form 
i think it's big miss , don't know do
i tried joins , pivots ,, got incomplete report think complicated
this latest try
declare @table1 table ( matguid uniqueidentifier, typeguid uniqueidentifier, qty int ) declare @table2 table ( matgroup varchar(250), matname varchar(250), billtypename varchar(250), qty int ) insert @table1 select fatoraitem.matguid, fatora.typeguid, sum(fatoraitem.qty) qty bi000 fatoraitem left join bu000 fatora on fatora.guid = fatoraitem.parentguid left join bt000 fatoratype on fatoratype.guid = fatora.typeguid group fatoraitem.matguid, fatora.typeguid insert @table2 select gr000.name matgroup, mt000.name matname, bt000.name billtypename, t1.qty @table1 t1 left join mt000 on mt000.guid = t1.matguid left join bt000 on bt000.guid = t1.typeguid left join gr000 on mt000.groupguid = gr000.guid declare @storecols nvarchar(max), @billtypecols nvarchar(max), @stmt nvarchar(max) select @billtypecols = isnull(@billtypecols + ', ', '') + '[' + t.billtypename + ']' (select distinct billtypename @table2) t if object_id('tempdb..#results') not null drop table #results select * #results @table2 select @stmt = 'select * #results t pivot ( sum(t.qty) t.billtypename in (' + @billtypecols + ') ) p' exec sp_executesql @stmt
i don't know why people vote down? instead of editing question, giving advice in comment or answering question people vote down.
i solved it's not solved 100% it's 90%, in other words accumulation of stores not working[it calculates quantity materials in store, parent store don't sum children stores] sql
create procedure [dbo].[advancedbillmatreport] @paramuserguid uniqueidentifier = null, @parammaterialguid uniqueidentifier = 0x0, @paramgrouplevel int = 0, @paramstoreguid uniqueidentifier = 0x0, @paramdisplaytype int = 1, @parampricetype int = 0, @parambilltypesguidsstr varchar(max) = null set nocount on if object_id('tempdb..#result') not null drop table #result if object_id('tempdb..#results') not null drop table #results if object_id('tempdb..#secviol') not null drop table #secviol if object_id('tempdb..#secviols') not null drop table #secviols create table [#secviol] (type [int], cnt [int]) create table [#secviols] (type [int], cnt [int]) --billtypes declare @billtypesguids table ( typeguid uniqueidentifier, typename varchar(250) ) insert @billtypesguids select t1.guid typeguid, bt000.name typename splitstring ( @parambilltypesguidsstr, ';' ) t1 left join bt000 on bt000.guid = t1.guid --matrials actual vertical part of thetable declare @materials table ( matguid uniqueidentifier, groupguid uniqueidentifier, code varchar(250), name varchar(250), latinname varchar(250), [level] int, [path] [varchar](8000), [type] int ) insert @materials exec repmattreereport --/////////////filter materials/////////////////////////////// delete @materials (level < @paramgrouplevel) if @parammaterialguid <> 0x0 begin delete @materials matguid <> @parammaterialguid end select * #result @materials exec prcchecksecurity @paramuserguid delete @materials matguid not in (select matguid #result) drop table #result insert #secviols select * #secviol truncate table #secviol --/////////////done filtering////////////////////////// --stores repeated horizontal parts of table declare @stores table ( storeguid uniqueidentifier, name varchar(250), parentguid uniqueidentifier, level int ) insert @stores(storeguid, name, parentguid, level) select t1.guid storeguid, st000.name, st000.parentguid, t1.level fngetstoreslisttree(@paramstoreguid, 1) t1 left join st000 on t1.guid = st000.guid --/////////////filter stores/////////////////////////// select * #result1 @stores exec prcchecksecurity @paramuserguid,0,0,'#result1' delete @stores storeguid not in (select storeguid #result1) drop table #result1 insert #secviols select *from #secviol truncate table #secviol --/////////////done filtering////////////////////////// --the grouped bill items declare @matdata table ( matguid uniqueidentifier, storeguid uniqueidentifier, typeguid uniqueidentifier, groupguid uniqueidentifier, qty int ) insert @matdata select fatoraitem.matguid, fatoraitem.storeguid, fatora.typeguid, mada.groupguid, isnull(sum(fatoraitem.qty),0) qty bi000 fatoraitem left join bu000 fatora on fatora.guid = fatoraitem.parentguid left join mt000 mada on mada.guid = fatoraitem.matguid group fatoraitem.matguid, fatoraitem.storeguid, fatora.typeguid, mada.groupguid insert @materials([type], matguid, groupguid, name,path) values(2, 0x0, 0x0, 'المجموع الكلي', (select max(path) @materials))--for total select [type], [level], matguid, groupguid, name #results @materials order path declare selectedbilltypes cursor local select typeguid, typename @billtypesguids declare storesiterator cursor local select storeguid, name @stores declare matiterator cursor local select matguid, name, level, type @materials open selectedbilltypes--bills-types declare @typeguid uniqueidentifier declare @typename varchar(250) fetch next selectedbilltypes @typeguid, @typename while(@@fetch_status = 0) begin open storesiterator--stores declare @storeguid uniqueidentifier declare @storename varchar(250) fetch next storesiterator @storeguid, @storename while(@@fetch_status = 0) begin declare @columnqname varchar(max) declare @columnpname varchar(max) declare @alterstatement varchar(max) set @columnqname = '[' + @typename + '.' + @storename + '.q]' set @alterstatement = 'alter table #results add ' + @columnqname + ' int not null default(0)' execute(@alterstatement) if(@paramdisplaytype = 1)--expaned display begin set @columnpname = '[' + @typename + '.' + @storename + '.p]' set @alterstatement = 'alter table #results add ' + @columnpname + ' int not null default(0)' execute(@alterstatement) end open matiterator--mats , groups declare @matguid uniqueidentifier, @matname varchar(250), @matlevel int, @mattype int fetch next matiterator @matguid, @matname, @matlevel, @mattype while(@@fetch_status = 0) begin declare @price int, @quantity int declare @insertstatement varchar(max) select @price = case when @parampricetype = 0x4 whole when @parampricetype = 0x8 half when @parampricetype = 0x10 export when @parampricetype = 0x20 vendor when @parampricetype = 0x40 retail when @parampricetype = 0x80 enduser end mt000 guid = @matguid if @mattype = 1 select @quantity = isnull(sum(qty), 0) @matdata t1 (t1.matguid = @matguid , t1.storeguid = @storeguid , t1.typeguid = @typeguid) else set @quantity = 0 set @price = @price * @quantity set @insertstatement = 'update #results set ' + @columnqname + ' = ' + cast(@quantity varchar(50)) if(@paramdisplaytype = 1)--expaned display begin set @insertstatement += ', ' + @columnpname + ' = ' + cast(@price varchar(50)) end set @insertstatement += ' matguid = ''' + cast(@matguid varchar(50)) + '''' execute(@insertstatement) fetch next matiterator @matguid, @matname, @matlevel, @mattype end close matiterator declare @updatestatement varchar(max) declare @total varchar(max) declare @maxlevel int select @maxlevel = isnull(max(level),0) #results set @total = 'update #results set ' + @columnqname + ' = ' + '(select isnull(sum(' + @columnqname + '), 0) #results t2 t2.type = 1)' if(@paramdisplaytype = 1)--expaned display begin set @total += ', ' + @columnpname + ' = ' + '(select isnull(sum(' + @columnpname + '), 0) #results t2 t2.type = 1) ' end set @total +=' [type] = 2' execute(@total) while (@maxlevel>-1) begin set @updatestatement = 'update #results set ' + @columnqname + ' = ' + '(select isnull(sum(' + @columnqname + '), 0) #results t2 t2.groupguid = #results.matguid)' if(@paramdisplaytype = 1)--expaned display begin set @updatestatement += ', ' + @columnpname + ' = ' + '(select isnull(sum(' + @columnpname + '), 0) #results t2 t2.groupguid = #results.matguid) ' end set @updatestatement += ' [type] = 0 , level = ' + cast(@maxlevel varchar(50)) execute(@updatestatement) set @maxlevel -= 1 end fetch next storesiterator @storeguid, @storename end close storesiterator fetch next selectedbilltypes @typeguid, @typename end close selectedbilltypes select * #results this result 
the table header built in c++, names columns names column name = billtypename.storename.q quantity or billtypename.storename.p price. in c++ iterate on columns names in table , split them , process header in table body put them come server
Comments
Post a Comment