> >
> >
> >
> > Thanks, Tushar,
> >
> > This was one of my toughest queries partly because of the challenge of putting together a mixed bag of data, and also surprisingly enhanced by the fact that some things like 'between' decided not to work!
> >
> > Just thought I'll attach the route where I am heading with what I have got with your help.
> >
> >

** Advancement- Trying to incorporate sub blocks also...(mixed block & subblock data entry) 
> > SELECT A.ddate Date,;
> > A.cblock Block,;
> > B.nArea Area,;
> > C.cName SubBlock,;
> > 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,; 
> > IIF(C.cparent=A.Cblock,(SELECT sum(RowPlants(C.nspx,C.nspy,C.narea,C.nrdmPlants)) as TotPlants FROM Blockprofile C ;
> > WHERE C.cParent=A.cBlock AND (A.ddate>=C.dstart AND A.ddate <=C.dend)),;
> > 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 TotPlants,;
> > (A.nMen+A.nWmn+A.nadl) as Heads,;
> > ROUND((A.nMen+A.nWmn+A.nadl)/;
> > ((SELECT sum(RowPlants(C.nspx,C.nspy,C.narea,C.nrdmPlants)) as TotPlants FROM Blockprofile C ;
> > WHERE C.cParent=A.cBlock AND (A.ddate>=C.dstart AND A.ddate <=C.dend)))*(C.narea*(43560/(C.nspx*C.nspy))),2) as HdsSpread;
> > From Dailywork2 A;
> > LEFT Outer JOIN BlockProfile C;
> > ON(A.cBlock = C.cParent OR A.cBlock=C.cName) and (A.ddate>=C.dstart AND A.ddate <=C.dend);
> > LEFT OUTER JOIN Block2 B;
> > on A.cBlock=B.block
> > 
> > 
> > FUNCTION RowPlants
> > 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
> > 
> > 

> >
> > Thanks, again,
> >
> > Regards,
> >
> > Steve.
>
> Steve
>
> You will soon be the resident expert of SQL here :).
>
> I should have told you this long ago. Don't use A,B,C for alias of tables in the SQL. It may lead to problems.
>
> Also, you are using BlockProfile in 1 query and 2 subqueries. Each time you have given the table the same alias. That may be confusing like
> (A.ddate>=C.dstart AND A.ddate <=C.dend)
> in this A is Dailywork2 of the outside SELECT but C can be of the outside SELECT or of the SUB SELECT.
>
> Regards
> Tushar



Thanks a lot, Tushar. I will make necessary modifications. Possibly, what you pointed out is what's causing a great deal of confusion.

Steve