Hi all,

I have an SQL request, which is getting more complicated by the day.

To simplify my request,I have an SQL statement that selects a set of conditions from 2 joined tables finally LEFT OUTER JOINED to another Sub-select statement grouped by a summed field.

Now I would like the SUB SELECT STATEMENT to satisfy this condition - that the sub-Select Table only tabulates conditions where the child table has records corresponding to the primary select main table has a date between the Start Date and the End date of the child table in the sub-select.

Anyway, I am enclosing the code here, after removing some fields to make easy reading.

Any help will be greatly appreciated.

SET enginebehavior 70

SELECT A.ddate,;
B.Block,;
C.cname as name,;
C.narea as area,;
ROUND(D.plants,2) as TotPlants,;
FROM Dailywork2 A ;
Inner Join Block2 B ;
On A.cblock=B.block ;
Left Outer JOIN ;
BlockProfile C ON A.cblock = C.cparent;
Left OUTER Join ;
(select C.cparent,sum(C.narea*(43560/(C.nspx*C.nspy)),0))+(SUM(C.nrdmPlants)) as Plants ;
FROM BlockProfile C  group by cparent ) D ;
on D.cparent=A.cblock ;
WHERE A.ddate < C.dstart

i.e. I require D to only sum data for which A.ddate falls between BlockProfile dStart and dEnd. My request is only for the sub select data

Thanks a lot,

Steve