CREATE PROCEDURE get_year_sum( in_year integer)
returns (out_month varchar(10), amount integer)
AS
declare monthnum integer
begin

/* call as "select * from get_year_sum */

outmonth = 0;

while (monthnum <= 12) do
begin
outmonth = outmonth + 1;
amount = 0;

/* I'll leave it for you to adjust the select to just return a sum for the
current month */

select
sum(topay)
from invoice
where invoicedate between :startdate and :enddate
into :amount;

suspend;
end
end



--
Nick
-----We Solve your Computer Problems---
Panther, Ingres, UNIX, Interbase, Firebird - Available Shortly

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of smantti73
Sent: 05 October 2005 16:10
To: firebird-support@yahoogroups.com
Subject: [firebird-support] How to return rows when no data exists?


Hello to each and all.


I'm using Firebird 1.5 with Delphi 2005, and I have a little app that
deals with invoices. Not surprisingly, it contains a "monthly sales"
report that displays sums of invoices per month, on a given time
period. All is well, except I can't figure out how to display months
in the result set that contain no data.

So, for example if I want to return this year's sales per month, I'd
want the result set to look like this:

month amount
1/2005 0
2/2005 2000
3/2005 0
4/2005 5000
5/2005 0

... and so on. I get the data with a query like this:

select
(extract (month from invoicedate) || ''/'' || extract (year from
invoicedate)) as month, sum(topay) as amount
from invoice
where invoicedate between :startdate and :enddate
group by
extract (year from invoicedate), extract (month from invoicedate)
order by extract (year from invoicedate), extract (month from
invoicedate)

Works like a champ, except that if there are no invoices for a given
month, it will obviously not return a row, so the result set looks
like this:

month amount
2/2005 2000
4/2005 5000

... which is looks daft in a report and doesn't even answer the user's
question of "this years sales" accurately. I've been given a hint
that this could probably be done with a stored procedure, iterating
through the months and returning zeroes for months where no data
exists. Trouble is, I don't know how to do that, so any
hints/pointers would be greatly appreciated.


TIA,
Antti Kurenniemi









++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





SPONSORED LINKS
Technical
<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&
w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq
+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technic
al+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> support Computer
<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical
+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&
w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsof
t+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA> technical support
Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g> computer
technical support
Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+s
upport&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4
=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+
technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w> technical support
Hewlett
<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA> packard
technical support Microsoft
<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technica
l+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support
&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microso
ft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw> technical
support

_____

YAHOO! GROUPS LINKS



* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .


_____




[Non-text portions of this message have been removed]