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
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment