Subject SQL Statement
Author Michael Vilhelmsen
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 ?


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........