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



How does this look, Tushar?

**Improvement ... Changing the Alias Name
SELECT DW.ddate Date,;
DW.cblock Block,;
BL.nArea Area,;
BP.cName SubBlock,;
ROUND(iif(not isnull(BP.nspx) and not isnull(BP.nspy) and BP.nspx<>0 and BP.nspy<>0,BP.narea*(43560/(BP.nspx*BP.nspy)),BP.nrdmPlants),2) as Plants,; 
IIF(BP.cparent=DW.Cblock,(SELECT sum(RowPlants(BP2.nspx,BP2.nspy,BP2.narea,BP2.nrdmPlants)) as TotPlants FROM Blockprofile BP2 ;
WHERE BP2.cParent=DW.cBlock AND (DW.ddate>=BP2.dstart AND DW.ddate <=BP2.dend)),;
ROUND(iif(not isnull(BP.nspx) and not isnull(BP.nspy) and BP.nspx<>0 and BP.nspy<>0,BP.narea*(43560/(BP.nspx*BP.nspy)),BP.nrdmPlants),2))as TotPlants,;
(DW.nMen+DW.nWmn+DW.nadl) as Heads,;
ROUND((DW.nMen+DW.nWmn+DW.nadl)/;
(IIF(BP.cparent=DW.Cblock,(SELECT sum(RowPlants(BP2.nspx,BP2.nspy,BP2.narea,BP2.nrdmPlants)) as TotPlants FROM Blockprofile BP2 ;
WHERE BP2.cParent=DW.cBlock AND (DW.ddate>=BP2.dstart AND DW.ddate <=BP2.dend)),ROUND(iif(not isnull(BP.nspx) and not isnull(BP.nspy) and BP.nspx<>0 and BP.nspy<>0,BP.narea*(43560/(BP.nspx*BP.nspy)),BP.nrdmPlants),2)))*(BP.narea*(43560/(BP.nspx*BP.nspy))),2) as HdsSpread;
From Dailywork2 DW;
LEFT Outer JOIN BlockProfile BP;
ON(DW.cBlock = BP.cParent OR DW.cBlock=BP.cName) and (DW.ddate>=BP.dstart AND DW.ddate <=BP.dend);
LEFT OUTER JOIN Block2 BL;
on DW.cBlock=BL.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

Do you think that by not declassifying my sub-selects, it was the reason why I wasn't able to segregate my select at the bottom with a join like how you had shown me in one of your earlier threads, treating the sub-select with a new name, which was used as a source of the main fields in the main select?

Steve