WITH TotalRecord(location1, totalrecords, brgyindex)
as
(
	select  Realproperty.location, COUNT (tdno), brgyIndex
	from RPTLedger
	join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
	group by RealProperty.location, brgyIndex
),
TotalPending (location2, totalpending, brgyindex)
as
(
	select distinct temp.location, ISNULL (temp2.approved,0),temp.brgyindex
from RPTLedger
left join
(
	select  Realproperty.location[location], RPTLedger.objid[objid], brgyIndex[brgyindex]
	from RPTLedger
	join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
	group by RealProperty.location, brgyIndex, RPTLedger.objid
)temp
on RPTLedger.objid=temp.objid
left join
(
select  Realproperty.location[location], COUNT (tdno)[approved]
from RPTLedger
join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
where RPTLedger.state='PENDING'
group by RealProperty.location
)temp2
on temp.location=temp2.location
),
TotalApproved (location3, totalapproved, brgyindex)
as
(
select distinct temp.location, ISNULL (temp2.approved,0),temp.brgyindex
from RPTLedger
left join
(
	select  Realproperty.location[location], RPTLedger.objid[objid], brgyIndex[brgyindex]
	from RPTLedger
	join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
	group by RealProperty.location, brgyIndex, RPTLedger.objid
)temp
on RPTLedger.objid=temp.objid
left join
(
select  Realproperty.location[location], COUNT (tdno)[approved]
from RPTLedger
join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
where RPTLedger.state='APPROVED'
group by RealProperty.location
)temp2
on temp.location=temp2.location
), 
TotalExcempt (location4, totalexcempt, brgyindex)
as
(select temp3.location, ISNULL(temp4.expt,0)[excempt], temp3.brgyindex 
from RPTLedger
left join
(
	select  Realproperty.location[location], RPTLedger.objid[objid], brgyIndex[brgyindex]
	from RPTLedger
	join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
	group by RealProperty.location, brgyIndex, RPTLedger.objid
)temp3 on RPTLedger.objid = temp3.objid
left join
(
select location, COUNT (RPTLedger.tdno)[expt]
from RPTLedger
	join TaxDeclaration on (RPTLedger.tdid=TaxDeclaration.objid)
	join RPU on (RPU.objid = TaxDeclaration.rpuid)
where exemptCode is not NULL
and RPTLedger.state ='PENDING'
group by location
)temp4 on temp3.location=temp4.location
),
TotalZeroAss (location5, totalzero, brgyindex)
as
(select temp5.location, ISNULL(temp6.zero,0)[ZeroCount], temp5.brgyindex 
from RPTLedger
left join
(
	select  Realproperty.location[location], RPTLedger.objid[objid], brgyIndex[brgyindex]
	from RPTLedger
	join RealProperty on (RPTLedger.realPropertyId = RealProperty.objid)
	group by RealProperty.location, brgyIndex, RPTLedger.objid
)temp5 on RPTLedger.objid = temp5.objid
left join
(
select location, COUNT (RPTLedger.tdno)[zero]
from RPTLedger
	join TaxDeclaration on (RPTLedger.tdid=TaxDeclaration.objid)
	join RPU on (RPU.objid = TaxDeclaration.rpuid)
where exemptCode is NULL
and RPTLedger.state ='PENDING'
and RPTLedger.assessedValue ='0'
group by location
)temp6 on temp6.location=temp5.location
)

select TotalRecord.location1[Barangay], totalrecords,totalapproved,
totalpending, totalexcempt, totalzero
from TotalRecord 
join TotalPending on (TotalRecord.location1 =TotalPending.location2)
join TotalApproved on (TotalRecord.location1=TotalApproved.location3)
join TotalExcempt on (TotalRecord.location1 = TotalExcempt.location4)
join TotalZeroAss on (TotalRecord.location1=TotalZeroAss.location5)
group by TotalRecord.location1, totalrecords, TotalPending.location2,
totalpending, TotalApproved.location3, totalapproved, TotalApproved.brgyindex, totalexcempt, totalzero
order by TotalApproved.brgyindex asc

quote:

---

Originally posted by phyxe

i see... well initially i just combined the queries with the "with" function, well my database has some data status such as "PENDING" and "APPROVED" so i basically need a query that will generate a single table result wherein it would search on a table that the "where" clause would include both the "PENDING" and "APPROVED" as a criteria for searching

---