SQL Query Calculated Field With Date Relationships
I have been querying our FM system from a java application and SQL I wrote a while back but have a problem now which I cannot work out!
We store product prices in our FM system (as including vat) and there is a calculated field for the price excluding vat. I used to be able to query this calculated field from SQL to get the correct value which worked fine. Since the VAT change we had to change the VAT rate and FM shows the price ex vat as being calculated correctly (i.e. 20% vat) however when I query the same field with SQL it returns a different value as to what FM is calculating!
There is a bit more added complexity which is actually where I think the problem lies. We have another table called VAT which stores the VAT rates with a date range (i.e. 17.5% from 1/1/2010 to 3/1/2011, 20% from 4/1/2011 to 31/12/2999 etc). There is a relationship between the product prices and the VAT table so that the prices are calculated based upon the current VAT rate which is determined with the date ranges.
As I said the odd thing is that FM all calculates correctly, It's only when I query it using SQL the problem happens. Another oddity is that if I change the VAT rate record which is no longer active (i.e. the old VAT rate record to 20% from 17.5%) the SQL returns the correct value but this would screw up FM for other parts of the system so I cannot leave it like this. I have tried swapping the records around but this makes no difference.
Has anybody had any experience with querying a calculated field where there is also a relationship to another table with SQL? I am totally baffled by this, if FM allows to query a calculated field then you would think it would do the complete calculation before returning the value! I know this is probably down to bad FM database design, but I did not create the FM system and it's the system we have used for years so don't really want to change it at all when I should be able to query a calculated field with simple SQL. It must have something to do with this relationship between the VAT table and the product prices.
Any help would be much appreciated.