This report is specifically for getting the MEW factor on a site.
Nothing in this should need to be changed.
select
'Residential' as [Type], count (*) as Cnt, '.333' as Multiplier, cast(count(*) * .333 as int) as [MEW Factor]
from
contact
join monitoring on contact.serialno = monitoring.serialno
where
contact.conttype = 1 and
contact.subtype = 0 and
monitoring.commstate = 1
union all
select
'Commercial Inactive' as [Type], count (*) as Cnt , '1' as Multiplier, cast(count(*) as int) as [MEW Factor]
from
contact join monitoring on contact.serialno = monitoring.serialno
where
contact.CONTTYPE = 1 and
contact.SUBTYPE = 1 and
MONITORING.commstate = 1 and
not exists (select * from sarea where serialno = contact.serialno and isnull(schedno, '') <> '')
union all
select
'Commercial O/C' as [Type], count (*) as Cnt, '3' as Multiplier, cast(count(*) * 3 as int) as [MEW Factor]
from
contact join monitoring on contact.serialno = monitoring.serialno
join sarea on sarea.serialno = contact.serialno and isnull(sarea.schedno, '') <> ''
where
contact.CONTTYPE = 1 and
contact.SUBTYPE = 1 and
monitoring.commstate = 1
union all
select null as [Type], null as Cnt, null as Multiplier, cast(Total.Total as int) as [MEW Factor]
from CONTACT
cross apply (
select count (*) as Cnt
from contact
join monitoring on contact.serialno = monitoring.serialno
where
contact.conttype = 1 and
contact.subtype = 0 and
monitoring.commstate = 1) as Cnt1
cross apply (
select count(*) as Cnt
from contact
join monitoring on contact.serialno = monitoring.serialno
where
contact.CONTTYPE = 1 and
contact.SUBTYPE = 1 and
monitoring.commstate = 1 and
not exists (select * from sarea where serialno = contact.serialno and isnull(schedno, '') <> '')) as Cnt2
cross apply (
select count(*) as Cnt
from contact
join monitoring on contact.serialno = monitoring.serialno
join sarea on sarea.serialno = contact.serialno and isnull(sarea.schedno, '') <> ''
where
contact.CONTTYPE = 1 and
contact.SUBTYPE = 1 and
monitoring.commstate = 1) as Cnt3
cross apply (select Cnt3.Cnt * 3 + round(Cnt1.Cnt * .333, 0) + Cnt2.Cnt as Total) as Total
where CONTTYPE = 0 and SERIALNO = 1
