Hi, I have a question about summing data, as follows:
NEW_TABLE
CODE AMOUNT SELL_DATE
1 10 10.11.2014
2 20 10.11.2014
3 30 07.11.2014
1 100 11.11.2014
2 200 11.11.2014
2 2000 11.11.2014
1 150 10.11.2014
3 500 11.11.2014
create or alter procedure new_table_sum (
fromdate date,
uptodate date)
returns (
sell_date date,
stock integer,
repair integer,
sh integer)
as
begin
for select
iif(n.code=1,sum(n.amount),0),
iif(n.code=2,sum(n.amount),0),
iif(n.code=3,sum(n.amount),0),
n.sell_date
from new_table n
where n.sell_date between :fromdate and :uptodate
group by n.sell_date,n.code
into
:stock,
:repair,
:sh,
:sell_date
do
suspend;
end
This gives the correct totals but not in the correct format:
SELL_DATE STOCK REPAIR SH
07.11.2014 0 0 30
10.11.2014 160 0 0
10.11.2014 0 20 0
11.11.2014 100 0 0
11.11.2014 0 2200 0
11.11.2014 0 0 500
What I want is this format, one row per day:
SELL_DATE STOCK REPAIR SH
07.11.2014 0 0 30
10.11.2014 160 20 0
11.11.2014 100 2200 500
I can get the correct format by executing a second SP like this:
create or alter procedure new_table_sum2 (
fromdate date,
uptodate date)
returns (
sell_date date,
stock integer,
repair integer,
sh integer)
as
begin
for select
sum(n.stock),
sum(n.repair),
sum(n.sh),
n.sell_date
from new_table_sum(:fromdate,:uptodate) n
where n.sell_date between :fromdate and :uptodate
group by n.sell_date
into
:stock,
:repair,
:sh,
:sell_date
do
suspend;
end
but I want to know if it can be done in a single SP, or is this an
acceptable way to get what I want? It applies to many scenarios.
Any help gratefully received.
Alan
--
Alan J Davies
Aldis
