Friday, 3 August 2012
#22 Penggunaan Multi Join dan Subquery di sql server
00:55 | 
Posted by
Koral Web
create view vbudgetcapex as
select a.budgetid, YEAR, month, assetclassid, assetclass, costcenter, allocationtypeid, allocationtype, c.price, pic, company, a.description,
qty_ori, isnull(qty_pemakaian,0) qty_pemakaian, /*qty_ori-qty_pemakaian qty,*/ budget, cast(budget/c.price as int) qty, isnull(totalcost,0) totalcost, budget - isnull(totalcost,0) amount, isnull(costsettlement,0) costsettlement
from (
select a.budgetid, YEAR, month, a.assetclass assetclassid, c.description assetclass, costcenter, a.allocationtype allocationtypeid, d.allocationtype, pic, company, a.description, max(qty) qty_ori,
SUM(amount) budget
from trbudgetcapex a
inner join MAssetClass c on (a.assetclass=c.assetclass)
left join MAllocationType d on (a.allocationtype=d.allocationtypeid)
where statusid=3 --and a.budgetid=1418
group by a.budgetid, YEAR, month, a.assetclass, c.description, costcenter, a.allocationtype, d.allocationtype, pic, company, a.description
)a left join
(
select budgetid, sum(qty_pemakaian) qty_pemakaian, SUM(totalcost) totalcost, SUM(costsettlement) costsettlement
from (
select a.budgetid, a.qty qty_pemakaian, case when isnull(costsettlement,0)>0 then costsettlement else a.totalcost end totalcost, isnull(costsettlement,0) costsettlement
from TrCapexDetail a
left join TrCapexSettlement b on (a.id=b.id)
inner join TrCapexHeader c on (a.capexid=c.capexid)
where statusid <>7
)x group by budgetid
) b on (a.budgetid=b.budgetid)
inner join (
select budgetid, price from (
select id, budgetid, price from trbudgetcapex a
)x
inner join (select max(id) id from TrBudgetCapex group by budgetid) b on (x.id=b.id)
) c on (a.budgetid=c.budgetid)
select a.budgetid, YEAR, month, assetclassid, assetclass, costcenter, allocationtypeid, allocationtype, c.price, pic, company, a.description,
qty_ori, isnull(qty_pemakaian,0) qty_pemakaian, /*qty_ori-qty_pemakaian qty,*/ budget, cast(budget/c.price as int) qty, isnull(totalcost,0) totalcost, budget - isnull(totalcost,0) amount, isnull(costsettlement,0) costsettlement
from (
select a.budgetid, YEAR, month, a.assetclass assetclassid, c.description assetclass, costcenter, a.allocationtype allocationtypeid, d.allocationtype, pic, company, a.description, max(qty) qty_ori,
SUM(amount) budget
from trbudgetcapex a
inner join MAssetClass c on (a.assetclass=c.assetclass)
left join MAllocationType d on (a.allocationtype=d.allocationtypeid)
where statusid=3 --and a.budgetid=1418
group by a.budgetid, YEAR, month, a.assetclass, c.description, costcenter, a.allocationtype, d.allocationtype, pic, company, a.description
)a left join
(
select budgetid, sum(qty_pemakaian) qty_pemakaian, SUM(totalcost) totalcost, SUM(costsettlement) costsettlement
from (
select a.budgetid, a.qty qty_pemakaian, case when isnull(costsettlement,0)>0 then costsettlement else a.totalcost end totalcost, isnull(costsettlement,0) costsettlement
from TrCapexDetail a
left join TrCapexSettlement b on (a.id=b.id)
inner join TrCapexHeader c on (a.capexid=c.capexid)
where statusid <>7
)x group by budgetid
) b on (a.budgetid=b.budgetid)
inner join (
select budgetid, price from (
select id, budgetid, price from trbudgetcapex a
)x
inner join (select max(id) id from TrBudgetCapex group by budgetid) b on (x.id=b.id)
) c on (a.budgetid=c.budgetid)
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment