> Steve
>
> Instead of WHERE A.ddate < C.dstart
> use
> WHERE BETWEEN(A.ddate ,C.dstart,C.dend)
>
> Never use ENGINEBEHAVIOUR 70 for new queries. It is only meant to run queries developed in previous versions of VFP till they are corrected. If you use ENGINEBEHAVIOUR 70 and tomorrow require to change to something like SQL Server, your work will increase.
>
> Regards
> Tushar
>
> > 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



Hi Tushar,

Problem solved. Two betweens for the select and sub-select have done the trick. Don't now how I couldn't figure this out earlier. Thanks for all the help.

With best regards,

Steve

SET enginebehavior 90

SELECT A.ddate,;
B.Block,;
C.cname as name,;
C.narea as area,;
ROUND(iif(not isnull(c.nspx) and not isnull(c.nspy) and C.nspx<>0 and C.nspy<>0,C.narea*(43560/(C.nspx*C.nspy)),C.nrdmPlants),2) as Plants,; 
ROUND(D.plants,2) as TotPlants,;
ROUND((A.nmen/D.plants)* iif(not isnull(c.nspx) and not isnull(c.nspy) and C.nspx<>0 and C.nspy<>0,ROUND(C.narea*(43560/(C.nspx*C.nspy)),0),C.nrdmPlants),2) as Spread, ;
C.nspx as spx,C.nspy as spy,A.nmen as Men;
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(iif(not isnull(c.nspx) and not isnull(c.nspy) and C.nspx<>0 and C.nspy<>0,C.narea*(43560/(C.nspx*C.nspy)),0))+(SUM(C.nrdmPlants))as Plants ;
FROM BlockProfile C  group by cparent WHERE BETWEEN(dailywork2.ddate ,C.dstart,C.dend)) D ;
on D.cparent=A.cblock ;
WHERE BETWEEN(A.ddate ,C.dstart,C.dend)