Hi,

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.
>
>

>

<B>SET enginebehavior 70
>
>SELECT A.ddate,;
>B.Block,;
>C.cname as name,;
>C.narea as area,;
>ROUND(Temp.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 ) Temp ;
>on Temp.cparent=A.cblock ;
>WHERE A.ddate < C.dstart</B>
>

>
>
>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