select a.division, 
a.subregion, 
a.district, 
a.territory, 

(
select sum(coalesce((case when rp.contractenddate is null then 0 when rp.contractenddate >= w.startdate and rp.contractenddate < w.enddate then 1 else 0 end),0))
from rpRepHomeBaseLink y
join rpt_weeknotodate w
on w.yearweekno = a.weekno
join rpContract rp
on y.contractid = rp.contractid 
where y.homebasename = a.territory
and y.startweekno <= a.weekno
and (y.endweekno >= a.weekno
or y.endweekno is null ) 
)
as terminations


from maSalesOfficeView a

where a.weekno between 200901 and 200926
order by 4

quote:

---

Originally posted by Kristen

What happened to the original post?

---

quote:

---

Originally posted by logeshwar

Hi The second query which you gave worked fine.The first query which you gave had the group by clause.that is why the error ws thrown....

Thanks a lot

---

quote:

---

I have a DB2 query which needs to be converted to be compliant with SQL Server. The challenge is to work around the Aggregate functioning on subqueried which is not allowed in Sql Server.

i run this query and i get the error message as
cannot perform aggregate functions on subqueries..

Can u help me in the workaround

The DB2 Query is

select a.div,
a.subreg,
a.dis,
a.ter,

sum(coalesce(
(
select sum(b.finalcount)
from HomeBaseLink x
join pMW b
on b.level = 2
and b.weekno = a.weekno
and b.repcontractid = x.contractid
where x.homebasename = a.ter
and x.startweekno <= a.weekno
and (x.endweekno > a.weekno
or x.endweekno is null )

)
,0))
as finalcount,


sum(coalesce(
(
select count(distinct z.contractid)
from HBL z
where z.homebasename = a.ter
and z.startweekno <= a.weekno
and (z.endweekno >= a.weekno
or z.endweekno is null )
)
,0))
as accumulatedcount,
sum(coalesce(
(
select sum(coalesce((case when rp.contractenddate is null then 0 when rp.contractenddate >= w.startdate and rp.contractenddate < w.enddate then 1 else 0 end),0))
from HBL y
join rpt_weeknotodate w
on w.yearweekno = a.weekno
join rpContract rp
on y.contractid = rp.contractid
where y.homebasename = a.territory
and y.startweekno <= a.weekno
and (y.endweekno >= a.weekno
or y.endweekno is null )
)
,0)) as terminations


from maSales a

where a.weekno between 200901 and 200926


group by a.div,
a.subreg,
a.dis,
a.ter

order by 4
;

---

quote:

---

Originally posted by Kristen

Thanks Madhi

---