Hi
CREATE TABLE #temp(itemname nvarchar(25),Particulars nvarchar(100),opening numeric(19,6),receipt numeric(19,6), issue numeric(19,6),closing numeric(19,6))
insert into #temp select max(t1.itemname),whscode,0,0,-SUM(quantity),0 from wtr1 t0 inner join oitm t1 on t0.itemcode = t1.itemcode where t0.docdate >= '1/1/2011' and t0.docdate <= '1/31/2011' and t0.itemcode = t1.itemcode and t0.whscode = 'PROD' and t1.itemcode in('13','17','22')
group by t1.itemcode,t0.whscode
insert into #temp select t2.itemname ,'PROD',0,isnull((select sum(t0.cmpltqty)
from owor t0 where t0.postdate >= '1/1/2011' and t0.postdate <= '1/31/2011' and t0.itemcode = t2.itemcode and t0.warehouse = 'PROD' and t0.type = 'S' and t0.status = 'R'),0) - isnull((select sum(t0.cmpltqty) from owor t0 where t0.postdate >= '1/1/2011' and t0.postdate <= '1/31/2011' and t0.itemcode = t2.itemcode and t0.warehouse = 'PROD' and t0.type = 'D'
and t0.status = 'R'),0) ,0,0 from oitm t2 where t2.itemcode in('13','17','22')
select case when grouping (ItemName)=1 then 'Grand Total'
else (case when grouping(particulars)=1 Then 'Subtotal'
else particulars end ) end,
itemname ItemName,
case when grouping (particulars)=0 then max(opening) else sum(opening) end 'Opening',
case when grouping (particulars)=0 then max(receipt) else sum(receipt) end 'Receipt',
case when grouping (particulars)=0 then max(issue) else sum(issue) end 'Issue',
case when grouping (particulars)=1 then (sum(opening)+sum(receipt))-sum(issue) end 'Closing'
from #temp where (opening > 0 or receipt > 0 or issue > 0) group by itemname,particulars with rollup
Drop table #temp
Output is
Particulars Item Opg Receipt Issue Closing
Item A 12 0 0
PROD Item A 0 2142 0
QC Item A 0 0 50
SLFP Item A 0 0 1984
Subtotal Item A 12 2142 2034 120
Item B 2788 0 0
PROD Item B 0 93054 0
QC Item B 0 0 126
SLFP Item B 0 0 89008
Subtotal Item B 2788 93054 89134 6708
Grand Total NULL 2800 95196 91168 6828
What i want Item Name should appear only once and it should appear in first column . i.e before Particulars.