Skip to Content
0
Former Member
Feb 17, 2011 at 02:32 PM

Query

34 Views

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.