Group by one table but aggregate a joined table in x++

Question

I'm trying to aggregate CustConfirmTrans.LineAmount records grouped by VAT % found in TaxData.TaxValue. To that end I'm joining as follows:

while select sum(LineAmount) from custConfirmTrans
    where custConfirmTrans.ConfirmID == custConfirmJour.ConfirmId
  join taxGroup
    where taxGroup.TaxGroup == custConfirmTrans.TaxGroup
  join taxItemGroup
    where taxItemGroup.TaxCode == taxGroup.TaxGroup
       && taxItemGroup.TaxItemGroup == custConfirmTrans.TaxItemGroup
  join taxData
    group taxData.TaxValue
    where taxData.TaxCode == taxItemGroup.TaxCode
       && taxData.TaxFromDate <= custConfirmJour.ConfirmDate
       && taxData.TaxToDate >= custConfirmJour.ConfirmDate

I've tried some variations of it but I'm generally getting one of these problems:

• taxData.TaxValue is 0 when it shouldn't be
• I'm getting many lines when I should only get 1

Answer 1

For 0 value of TaxValue: you dont have TaxValue in select, just sum of LineAmount. For many lines: did you used inner join right? Most probably on taxData

taxItemGroup.TaxCode == taxGroup.TaxGroup, is that right or typo? You have taxCode in next WHERE statement below