Hi Michael, with the current weather conditions in Europe I hope you
either stay indoors or have grown some hair since the Fulda conference
(in Oslo, we were down to -19,5 this morning and I even saw people who
had spent the night outdoors queueing for some very good offers for
the first few customers at a new shop opening today)! I wonder if
freezers come with a heater in case it gets too cold? Anyway, back to
your question:

Try something like

select vfsd.v509index,
case
when vvo.v509index is null then vfsd.Antall
else vvo.Antall //I refuse using one 'l' for Antall
end
from VareFrvStr_Detail vfsd
left join vv_omsetning vvo on vvo.v509index = vfsd.v509index
where (vvo.aar*12 + vvo.mdr) between 24060 and 24063
and not exists(select * from vv_omsetning vvo2
where vvo2.v509index = vvo.v509index
and (vvo2.aar*12 + vvo2.mdr) between
24060 and (vvo.aar*12 + vvo.mdr - 1))

The not exists part will ascertain that you only get the first match
on aar and mdr for each v509index. Note that I actually do not use
Firebird 1.5, so I haven't yet had the pleasure of using the case
statement on a real database. As such, the above query is very likely
to contain errors.

I was uncertain whether I should use mathematics or use and/or to
ascertain the aar/mdr combination to be >=2004 12. I have no idea
which one will be the faster one (other than knowing that you do not
want to use any other index on vv_omsetning than the one for the field
v509index).

HTH,
Set

--- In firebird-support@yahoogroups.com, "Michael Vilhelmsen" wrote:
> SQL Question
>
> I have a tabel (called VareFrvStr_Detail) containing lots of
> records. This tabel has a unique field (varchar(15) called
> V509index) and a field containing a number (Numeric (15,2) called
> Antal).
>
> I also have another tabel (Called vv_omsetning).
> This tabel has the same unique field (varchar(15) called V509Index)
> thah is a reference to the same field above.
> It also contains a field containing a number (Numeric (15,2) called
> Antal) AND two fields (Integer called mdr and aar) contain a
> monthnumber (mdr) and a yearnumber (aar).
> This second tabel is being maintained by triggers.
> I contains the value of Antal from the first tabel at the end of a
> month. I the record in VareFrvStr_Detail has no changing values in a
> given month there will be no record in the second tabel.
>
> Now - I have some users that would like know what the value was i.e.
> december 31. 2004.
>
> Then I could just select all the values where aar=2004 and mdr=12.
> But this doesn't give me the numbers from december 31. 2004 to all
> records, because a record doesn't nessesaly exists in the second
> table. If the record doesn't exists, if have to locate a record in
> either january 2005 (mdr=1 and ar=2005), february 2005 (mdr=2 and
> aar=2005) or marts 2005 (mdr=3 and aar=2005). If the exists no
> records to any months I will have to use the value from the first
> table.
>
> Can I make a SQL statement that gives me these values ?
>
>
> Exampel:
>
> TABLE (VareFrvStr_Detail)
> V509Index Number
> 1111 11
> 2222 22
> 3333 33
>
>
> TABLE (vv_omsetning)
> V509Index aar mdr number
> 1111 2004 12 7
> 2222 2005 2 8
>
>
> The result from the SQL statement should return this:
>
> v509index number
> 1111 7 (Actual value december 2004)
> 2222 8 (Value from february 2005)
> 3333 33 (Value from first table).
>
>
>
> I hope this gives meaning, and that someone can help me.
> I have made another solution that works, but it is very slow, sinse
I
> do a look up in the second table (vv_omsetning) when parsing through
> the first table (VareFrvStr_Detail). The first table can contain as
> many as 1.500.000 records. This gives me a lot of look ups........
>
>
> Regards
> Michael