automated filtering of entries in a fact with last date

Question

Hi,

we have the problem that sometimes an import does not pass trough and so our fact table has older entries than the current date - that's quite ok, because it happens not so often and the data hasn't to be from the same day.

Now we have the request, that an automated report with a fixed date filter, should use the last entry's date from our fact table.
When the report uses the current date (getdate() or something else) sometimes it doesnt find any entry and so it is blank.
We need to get the last entry date from our fact table for our report to be assigned dynamicly.

Our way was to set a flag in an additional column in the fact table with true, if it is the last entry date or false if it's older.
So we can get the last real date, we need.
Now we built a dimension table with true or false and with the last entry date - beeing filled by a stored procedure that catches the last entry's date from our fact table.

Is there any possibility to solve that problem more beautiful ?

Greets

Kurt from Munich

Answer 1

When the report uses the current date (getdate() or something else) sometimes it doesnt find any entry and so it is blank.

Hallo Kurt aus München,

This is a German forum, so I am going to response in German; if this is a problem for you, please give a brief reply.

GetDate() liefert Dir das aktuelle Datum inkl. Uhrzeit. Wenn in den Faktendaten das Datum ohne Uhrzeit steht, bekommst Du mit einem Filter auf "Datumsspalte >= GetDate()" kein Ergebnis. Du musst den Uhrzeit-Anteil aus GetDate() entfernen, z.B. so:

SELECT GetDate() AS AktDateTime

SELECT DATEADD(day, DATEDIFF(day, {d N'2013-01-01'}, GetDate()), {d N'2013-01-01'}) AS AktDAte

---

Olaf Helper

Blog Xing