I am a little "green" and using a very vanilla reporting tool through a web application so I do not have direct access to the database but can query it. I have a column based on a subquery that returns a value, I need to create another column in the result set that counts only if that subquery value is not null.. Below is the example the owned lot will populate a reference number that cannot be counted in the application because its >1 (2278, etc)and the application only allows a SUM calculation, that is why I need the additional "count" column - any suggestions ??
select
substring(r.resourceid,1,6) as Subdivision,
r.resourceid as Lot, r.Address1,
(select top 1 e.EvRefnum from AMDEVENTS e
where
e.resol1id = r.resol1id and
e.resol2id = r.resol2id and
e.resol3id = r.resol3id and
e.restypid = r.restypid and
e.resourceid = r.resourceid and
e.evttype='Owned Lot') as 'Owned Lot Job' ,
1 as [Count]
from AMTRESOURCE r
where
r.restypid='lotid'
and
(select top 1 e.EvRefnum from AMDEVENTS e
where
e.resol1id = r.resol1id and
e.resol2id = r.resol2id and
e.resol3id = r.resol3id and
e.restypid = r.restypid and
e.resourceid = r.resourceid and
e.evttype<>'Owned Lot') is null
and
substring(resourceid,1,3) like '%%sub%%'
order by
resourceid
Terri
select
substring(r.resourceid,1,6) as Subdivision,
r.resourceid as Lot, r.Address1,
(select top 1 e.EvRefnum from AMDEVENTS e
where
e.resol1id = r.resol1id and
e.resol2id = r.resol2id and
e.resol3id = r.resol3id and
e.restypid = r.restypid and
e.resourceid = r.resourceid and
e.evttype='Owned Lot') as 'Owned Lot Job' ,
1 as [Count]
from AMTRESOURCE r
where
r.restypid='lotid'
and
(select top 1 e.EvRefnum from AMDEVENTS e
where
e.resol1id = r.resol1id and
e.resol2id = r.resol2id and
e.resol3id = r.resol3id and
e.restypid = r.restypid and
e.resourceid = r.resourceid and
e.evttype<>'Owned Lot') is null
and
substring(resourceid,1,3) like '%%sub%%'
order by
resourceid
Terri