Hi all, I hope someone can help me with this problem. I need to get
totals from 2 separate tables, both linked to a third table by the
field HAUL_CODE.
This is a simplified version. I have put the "real" result into the
HAUL_NAME so that anyone who can help can see exactly what to expect.
I've put the count in to show that tbl_weigh.haul_code # 3 is in twice
but there is only 1 record. My problem is presumably with the join but
I can't work it out.
System is Firebird 2.03.12981 on MS Server 2003

tbl_haulier (this is the master table)
HAUL_CODE HAUL_NAME
1 one - result s/be 1000,100,10 count 1
2 two - result s/be 6420,642,0 count 3
3 three - result s/be 3000,0,63 count 1
4 four - result s/be 0,0,40 count 0

tbl_weigh
HAUL_CODE HAUL_DOLLARS MILEAGE_DOLLARS DATE_IN WEIGH_CODE
1 1000 100 11/11/2007 1
2 2000 200 11/11/2007 2
2 2200 220 13/11/2007 4
2 2220 222 15/11/2007 5
3 3000 0 11/11/2007 3

Tbl_account_charges
HAUL_CODE AMOUNT DATE_IN CHARGE_CODE
1 10 11/11/2007 1
3 30 11/11/2007 2
3 33 15/11/2007 4
4 40 11/11/2007 3

This is the result set - line 3 - HAUL_CODE 3 should only be 3000 with
a count of 1
HAUL_CODE HAUL_NAME
HAUL_DOLLARS MILEAGE_DOLLARS CHARGES COUNT_WEIGH_CODE
1 one - result s/be 1000,100,10 count 1 1000 100 10 1
2 two - result s/be 6420,642,0 count 3 6420 642 0 3
3 three - result s/be 3000,0,63 count 1 6000 0 63 2
4 four - result s/be 0,0,40 count 0 0 0 40 0

totals should be 10420 742
113


This is the test SP I am running to give this result
CREATE PROCEDURE JOIN_TEST (
fromdate date,
uptodate date)
returns (
haul_code integer,
haul_name char(40),
haul_dollars integer,
mileage_dollars integer,
charges integer,
count_weigh_code integer)
as
begin
for
select tbl_haulier.haul_code, tbl_haulier.haul_name,
count(tbl_weigh.weigh_code),
coalesce(sum( tbl_weigh.haul_dollars ),0),
coalesce(sum( tbl_weigh.mileage_dollars ),0),
coalesce(sum( tbl_account_charges.amount ),0)
from tbl_haulier
left join tbl_weigh on (tbl_weigh.haul_code = tbl_haulier.haul_code)
and tbl_weigh.date_in between :FromDate and :UpToDate
left join tbl_account_charges on (tbl_haulier.haul_code =
tbl_account_charges.haul_code)
and tbl_account_charges.date_in between
:FromDate and :UpToDate
group by tbl_haulier.haul_code, tbl_haulier.haul_name
into :haul_code,
:haul_name,
:count_weigh_code,
:haul_dollars,
:mileage_dollars,
:charges
do
suspend;
end

Thanks
Alan
--
Alan J Davies
Aldis