Thursday, 9 August 2012
#23 Penggunaan Case pada kondisi
20:28 | 
Posted by
Koral Web
select a.budgetid, budget, b.totalcost, amount,
case when c.budgetid is null then b.totalcost else b.totalcost+amount end x
from vbudgetcapex a
inner join (
select budgetid, SUM(totalcost) totalcost from trcapexdetail a
inner join TrCapexHeader b on (a.capexid=b.capexid)
where a.capexid=85
group by budgetid
) b on (a.budgetid=b.budgetid)
left join [vcapex_pemakaianbudget] c on (a.budgetid=c.budgetid)
where budget < (case when c.budgetid is null then b.totalcost else b.totalcost+amount end)
case when c.budgetid is null then b.totalcost else b.totalcost+amount end x
from vbudgetcapex a
inner join (
select budgetid, SUM(totalcost) totalcost from trcapexdetail a
inner join TrCapexHeader b on (a.capexid=b.capexid)
where a.capexid=85
group by budgetid
) b on (a.budgetid=b.budgetid)
left join [vcapex_pemakaianbudget] c on (a.budgetid=c.budgetid)
where budget < (case when c.budgetid is null then b.totalcost else b.totalcost+amount end)
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)
Wednesday, 1 August 2012
#21 Union di SQL Server
02:44 |
Posted by
Koral Web
create view [dbo].[vbudgetcapex_download] as
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
qty JanuariUnit, e.amount JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=1 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, qty FebruariUnit, e.amount FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=2 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, qty MaretUnit, e.amount MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=3 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, qty AprilUnit, e.amount AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=4 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, qty MeiUnit, e.amount MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=5 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
qty JuniUnit, e.amount JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=6 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, qty JuliUnit, e.amount JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=7 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, qty AgustusUnit, e.amount AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=8 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, qty SeptemberUnit, e.amount SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=9 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, qty OktoberUnit, e.amount OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=10 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
qty NovemberUnit, e.amount NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=11 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, qty DesemberUnit, e.amount DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=12 and a.statusid=3
GO
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
qty JanuariUnit, e.amount JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=1 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, qty FebruariUnit, e.amount FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=2 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, qty MaretUnit, e.amount MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=3 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, qty AprilUnit, e.amount AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=4 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, qty MeiUnit, e.amount MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=5 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
qty JuniUnit, e.amount JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=6 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, qty JuliUnit, e.amount JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=7 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, qty AgustusUnit, e.amount AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=8 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, qty SeptemberUnit, e.amount SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=9 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, qty OktoberUnit, e.amount OktoberValue,
0 NovemberUnit, 0 NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=10 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
qty NovemberUnit, e.amount NovemberValue, 0 DesemberUnit, 0 DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=11 and a.statusid=3
union
select year, a.budgetid, a.assetclass TypeOfRequesitionCode, b.description TypeofRequisitionName, a.description ItemDescription,
c.allocationtype AllocationType, PIC, CostCenter, Company, Price,
0 JanuariUnit, 0 JanuariValue, 0 FebruariUnit, 0 FebruariValue, 0 MaretUnit, 0 MaretValue, 0 AprilUnit, 0 AprilValue, 0 MeiUnit, 0 MeiValue,
0 JuniUnit, 0 JuniValue, 0 JuliUnit, 0 JuliValue, 0 AgustusUnit, 0 AgustusValue, 0 SeptemberUnit, 0 SeptemberValue, 0 OktoberUnit, 0 OktoberValue,
0 NovemberUnit, 0 NovemberValue, qty DesemberUnit, e.amount DesemberValue, a.description Remarks
from TrBudgetCapex a
inner join MAssetClass b on (a.assetclass=b.assetclass)
left join MAllocationType c on (a.allocationtype=c.allocationtypeid)
inner join (select budgetid, MAX(id) id from TrBudgetCapex group by budgetid) d on (a.id=d.id)
inner join (select budgetid, sum(amount) amount from TrBudgetCapex a where statusid=3 group by budgetid) e on (a.budgetid=e.budgetid)
where month=12 and a.statusid=3
GO
Thursday, 26 July 2012
#20 Join table dengan data yang di select
03:42 |
Posted by
Koral Web
select distinct a.tahun, a.bulan, a.tanggal, userid from (
select distinct year(logtime) tahun, month(logtime) bulan, day(logtime) tanggal
from ngac_log
where year(logtime)=2012 and month(logtime)=5
)a left join (
select distinct userid, year(logtime) tahun, month(logtime) bulan, day(logtime) tanggal
from ngac_log
where year(logtime)=2012 and month(logtime)=5 and userid='002412430000000'
) b on (a.tahun=b.tahun and a.bulan=b.bulan and a.tanggal=b.tanggal)
order by 3
Friday, 20 July 2012
#19. Menampilkan seluruf field dalam tabel di MySQL
18:02 |
Posted by
Koral Web
show full fields from database.nama_tabel
Subscribe to:
Posts (Atom)