I am not sure how to put this question forward:
Ihave two tables contracts and contracts_balances
for each record in a contracts table there can be multiple records in contracts balance table based on the start_date and end_date of contract (pleas see the link)
in the contracts table, column qty specifies the quantity which will ordered by costumers
in the contracts balance , after each month this is traced and teh remaining balanvce is inputted in balance_aftermonth column.
I am trying to join these two tables to calculate the total quantity ordered per contract and then see the remaining balance for a current month (in this case month 3)
SELECT c.product_name, sum(c.qty), c.contract_prod, c.start_date, sum(cb.balance_aftermonth) as remaining_bal,cb.monthlydates
From contracts c
join contracts_balance cb on (cb.product_name = c.product_name AND cb.contract_id_fk = c.contract_id)
WHERE c.type_of = 'forecast' and Month(cb.monthlydates) = 3
GROUP BY c.product_name, c.contract_prod, c.start_date,cb.monthlydates
Everything works fine, but in the column remaining_balance shows the wrong calculations. It should sum the remining balance for where the month == 3 and sum the balances.
UPDATE: ANSWER
SELECT c.product_name, sum(distinct c.qty),c.contract_prod, c.start_date, t.monthlydates,sum(distinct t.suum)
From contracts c
Join (select product_name ,balance_aftermonth as suum ,contract_prod, monthlydates,start_date from contracts_balance where month(monthlydates) = 3 ) as t ON t.product_name=c.product_name and t.start_date=c.start_date
where c.type_of = 'forecast'
GROUP BY c.product_name, c.contract_prod, c.start_date,t.monthlydates
