Hi - can anyone help with this query. Firebird 1.5 on W2k
I have a table with deliveries stored on a daily basis and want a total of
deliveries by day between selected dates.
Table (simplified) looks like this
Date_In Date
Tonnes Numeric(9,3)

This SP works fine and I get this returned:
DATE_IN TONNES_CONTRACT
2006.03.10 10
2006.03.11 11
etc..

CREATE PROCEDURE GET_DELIVERIES_DAY (
FROMDATE DATE,
UPTODATE DATE)
RETURNS (
DATE_IN DATE,
TONNES_CONTRACT NUMERIC(9,3)
AS
BEGIN
for select date_in,
sum(tonnes)
from deliveries
where date_in>=:FromDate
and date_in<=:UpToDate
group by date_in
into :date_in,:tonnes_contract
do
suspend;
END

What I need, however, is a distinction between contract tonnes and non-contract
tonnes, as follows, in this example I'm running the query twice, but the output
is wrong - The first part of the contract is correct, but the second part just
shows the last total. The first part of the asl is all null, the second part is
correct:
DATE_IN TONNES_CONTRACT TONNES_ASL
2006.03.10 10 <null>
2006.03.11 11 <null>
2006.03.10 11 20
2006.03.11 11 21

etc..

CREATE PROCEDURE GET_DELIVERIES_DAY (
FROMDATE DATE,
UPTODATE DATE)
RETURNS (
DATE_IN DATE,
TONNES_CONTRACT NUMERIC(9,3),
TONNES_ASL NUMERIC(9,3))
AS
BEGIN
for select date_in,
sum(tonnes)
from deliveries
where contract_code=11
and date_in>=:FromDate
and date_in<=:UpToDate
group by date_in
into :date_in,:tonnes_contract
do
suspend;
for select date_in,
sum(tonnes)
from deliveries
where contract_code<>11
and date_in>=:FromDate
and date_in<=:UpToDate
group by date_in
into :date_in,:tonnes_asl
do
suspend;
END

I then tried the following which gives the overall totals for each day - so
while I'm trying to use a sub-select I've obviously got myself mixed up! Can
anyone help please.

DATE_IN TONNES_CONTRACT TONNES_ASL
2006.03.10 21 41
2006.03.11 21 41

CREATE PROCEDURE GET_DELIVERIES_DAY (
FROMDATE DATE,
UPTODATE DATE)
RETURNS (
DATE_IN DATE,
TONNES_CONTRACT NUMERIC(9,3),
TONNES_ASL NUMERIC(9,3))
AS
BEGIN
for select date_in,
(select sum(tonnes) from deliveries
where contract_code=11
and date_in>=:FromDate
and date_in<=:UpToDate),
(select sum(tonnes) from deliveries
where contract_code<>11
and date_in>=:FromDate
and date_in<=:UpToDate)
from deliveries
group by date_in
into :date_in,:tonnes_asl:tonnes_contract
do
suspend;
END
--
Alan J Davies
Aldis
Tel: +44 (0)1926 842069
Mob: +44 (0)7885 372793