March 23, 2017 at 2:09 am
Hello All, my today's query is simple but seems I'm trying too hard and hence killing my thought process. So I have a monthly currency feed getting loaded in a table T_Currency (it's a delete-load I.e. not saving any historical data). And it's not a regular monthly feed. If there is any change then only it gets loaded else old data works for new month. Now we have to show the report based on currency calculations for two months (current and old). So need to save two month data. It's decided that we'll keep new table for old currency set as lots of app components using this table T_currency for latest set and don't need this two month set.
How should I go ahead with getting this new table populated ? I.e. keeping previous month currency set. One more thing - we can get multiple currency loads in a month and latest one stays to be used for upcoming month.
Hope it's not confusing.
March 23, 2017 at 3:18 am
Personally, I'd just keep all the historical currency data. Unless storage space is really than much of a premium for you, it's not going to be a problem, as storage space is going to be minimal.
Guessing at your application, am I safe to assume that you're using exchange rates against something like a transaction? Something that happened on a specific date (and time) at least. I would therefore store the data with a FromDate and ToDate value. Then, each time you check the currency currencies, you do an upsert. If the currency is unchanged, then your data stays that way. If it has, then you update the current live value (which you will probably have a NULL in the ToDate for), and set it to yesterday's date. Then insert to new value into the table, with today's date as the FromDate.
Then, when joining your transaction table to your currency table, you'd simply need to add to your JOIN a statement to only return the value where the transaction date is between the currency From and To dates.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 27, 2017 at 10:53 am
Thom A - Thursday, March 23, 2017 3:18 AMPersonally, I'd just keep all the historical currency data. Unless storage space is really than much of a premium for you, it's not going to be a problem, as storage space is going to be minimal.Guessing at your application, am I safe to assume that you're using exchange rates against something like a transaction? Something that happened on a specific date (and time) at least. I would therefore store the data with a FromDate and ToDate value. Then, each time you check the currency currencies, you do an upsert. If the currency is unchanged, then your data stays that way. If it has, then you update the current live value (which you will probably have a NULL in the ToDate for), and set it to yesterday's date. Then insert to new value into the table, with today's date as the FromDate.
Then, when joining your transaction table to your currency table, you'd simply need to add to your JOIN a statement to only return the value where the transaction date is between the currency From and To dates.
Don't forget about setting the ToDate in the old record. Also, when you query for the latest value, in the range between FromDate and ToDate is NOT going to work with NULL being present in the ToDate field, which most assuredly would be the value in your description of this. You would have to use an ISNULL(ToDate, '9999-12-31') or something like it to handle the NULL.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply