> > Steve
> >
> > Hmmm. This is getting complicated. I think your second select requires a join
> >
> >

> > 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  ;
> > LEFT OUTER JOIN ;
> > Dailywork2 A ;
> > on C.cparent=A.cblock ;
> > group by cparent WHERE BETWEEN(A.ddate ,C.dstart,C.dend)) D ;
> > on D.cparent=A.cblock ;
> > WHERE BETWEEN(A.ddate ,C.dstart,C.dend)

> >
> > Regards
> > Tushar
>
>
> No Luck, Tushar, Sorry.
>
> I've remodelled the query again into two distinct selects, one with Plants and another with sum(plants). The second select works fine when the 'between' clause is not incorporated, but is obviously giving me a sum of plants of the entire table, and not of the period specified. The moment I incorporate the 'between', I get irregulated results of the sum. Basically, the between function should fire by linking on to the dailywork table immediate record and doing a sum of plants there.
>
> My Code:
>
>

SELECT Temp1.Ddate,Temp1.cblock,Temp1.cName,temp1.plants,Temp1.Men,Temp2.Plants as totPlants;
> from;
> (SELECT A.ddate,A.cblock,C.cname,;
> 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,; 
> C.nspx as spx,C.nspy as spy,A.nmen as Men;
> FROM Dailywork2 A;
> Left Outer JOIN BlockProfile C;
> ON A.cblock = C.cparent;
> and BETWEEN(A.ddate,C.dstart,C.dend)) temp1;
> LEFT outer Join;
> (select 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 ) Temp2;
> ON temp1.cBlock=temp2.cparent
> 
> 
> * where BETWEEN(A.ddate,C.dstart,C.dend)
> 

>
> Thanks a lot, Tushar,
>
> Regards,
>
> Steve.

Steve

I feel your second select has to be joined to the Dailywork2 to select only the relevant date ranges in the SUM().

SELECT A.ddate,A.cblock,C.cname,NumberPlants(c.nspx,c.nspy,C.narea,C.nrdmPlants)  as Plants,; 
C.nspx as spx,C.nspy as spy,A.nmen as Men ;
FROM Dailywork2 A ;
Left Outer JOIN BlockProfile C ;
ON A.cblock = C.cparent ;
and BETWEEN(A.ddate,C.dstart,C.dend);
LEFT outer JOIN ;
(select cparent, sum(NumberPlants(d.nspx,d.nspy,d.narea,d.nrdmPlants)) as Plants ;
FROM BlockProfile d group by cparent ;
<B>left outer JOIN Dailywork2 e ;
on e.cblock = d.cparent ;
WHERE BETWEEN(e.ddate,d.dstart,d.dend)</B> ;
) Temp2;
ON temp1.cBlock=temp2.cparent



FUNCTION NumberPlants
LPARAMETERS lnX,lnY,lnArea,lnNrdm
IF ISNULL(lnX) OR ISNULL(lnY) OR lnX=0 OR lnY=0
	RETURN lnNrdm
ELSE
	RETURN ROUND(lnArea*43560/(lnX*lnY),2)
ENDIF

The function NumberPlants is just because I was getting confused with the long statement. You can use the old thing there.

Regards
Tushar