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



Hi Tushar,

Was trying out your code but I got a syntax error at the point of sum(numberPlants.

Since this is the first time I am using a sub function in an SQl select, I wasn't able to sort it out.

Could you please check from your end. Thanks for all the help, tushar,

With best regards,

Steve

Tushar,

While I was tinkering with this SQL, I derived another simplistic sequence of code, which might make easier reading, and requires the same result.(The sum of plants that fulfill the criteria of being within dstart and dend).I've also cut off some of the general fields to enhance readability. Just thought I'd send it to you, to see whether it could be used with some modifications. Somehow, the sum is not giving me desired results here too.

The code:

SELECT A.ddate,A.cblock,E.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,; 
sum(iif(not isnull(E.nspx) and not isnull(E.nspy) and E.nspx<>0 and E.nspy<>0,E.narea*(43560/(E.nspx*E.nspy)),0))+(SUM(E.nrdmPlants))as TotPlants ;
FROM Dailywork2 A;
LEFT outer JOIN BlockProfile C;
LEFT Outer JOIN Blockprofile E;
on A.cblock = E.cParent;
on A.cBlock = C.cParent;
and BETWEEN(A.ddate,C.dstart,C.dend);
AND BETWEEN(A.ddate,E.dstart,E.dend);
group by 1,2,3,4

One Blockprofile Table is to give individual values of Plants (C). The other Blockprofile Table (E) is to give the consolidated summed value of plants for the between Period. Both tables are joined to the same field of Dailywork (A)

Steve