Using Data from a specific "AS OF" date possible?

[branch861]

I'm a newbie...so bear with me...PLEASE!

I have been asked to create a series of reports that utilize the 3/31 database. Our data within SQL server has AS OF dates. Which means that the data that is showing within SQL server right now is data from the previous day. So, therefore, tomorrow's data will actually have the data I want. Perfect!

My PROBLEM and QUESTION:

What do I do on April 2nd? How can I utilize that same 3/31 database after the AS OF date changes to 4/1? Does this make sense? One thing I'm doing is preparing to run the reports on tomorrow however I know that people are going to approach me after the fact wanting to access the 3/31 database. Can you guide me please? Thanks!

BTW, I'm running SQL 7.0 and we backup nightly using ArcServe...if that helps.

Lachele

 

[cheyney]

So let me get this straight: each row of data in your table has an "AS OF" date, and you want to pull a report for data with a certain "AS OF" date?

for example:
TableA

ID ASOF
1 04/01/2003
2 04/01/2003
3 04/02/2003
4 04/02/2003
5 04/03/2003

if you want to pull data with an ASOF date of march 2nd, you'd want to pull only rows with ID 3 & 4? Do you just want to filter by ASOF date?

cheyney

 

[branch861]

No. I would be soooo happy if it were that simple.

The issue is that I have tables and within those tables within the database they all have the same ASOF_date. Every record would have the same ASOF_date. And this date changes everyday. Make sense? Thanks for taking the time to respond.

ID ASOF
1 04/01/2003
2 04/01/2003
3 04/01/2003
4 04/01/2003
5 04/01/2003

 

[cheyney]

you want to be able to go back to a specific point in time and query data as if the data hadn't changed since? If this is the case, then its kindof hard without totally restructuring your database. It seems datarows are overwritten with new data instead of new rows being inserted and the old data kept. I suspect you're going to have to restore backups to do historical queries, and this probably isn't feasible. Hopefully this isn't what you have to do...please clarify a little more

cheyney

 

[SQLSister]

Let me get this straight you have a database that has data input over time, but the date in the ASOF column changes every day? Why is that? Do you have a different date field that gives you information about the actual date of the data? If not there is no way to go back in time and see what records were good on a past date. It sounds to me as if your database design and your customer needs are not congruent and the way the database is designed needs to change to meet customer requirements. If I knew more about the function and structure of the existing database, I could perhaps give you some more pertinent advice. With what you've give so far, what you want to do is not possible without changing your database in some way or storing data redundantly.

 

[SQLBill]

If all of the ASOF dates are changing, then there isn't a way to retrieve data from earlier days without reloading from backups. Using your example, on 1 April all records get updated to reflect ASOF 04/01/2003. So as far as the database is concerned there AREN'T any records from 03/31/2003.

-SQLBill

 

[branch861]

You're right! I would like to query data as if the data hadn't changed. I plan to run all of my reports tomorrow. But I know that there will be those folks coming to me after the fact wanting reports after the AS OF date has changed. They keep telling me that they want me to use the 3/31 database.

So the only way you can see is restoring the database? Doesn't that mean that I'll be overwriting the current data? In addtion, is there anyway that I can save the 3/31 data in anticipation of this problem.? Please advise.

And to shed light on some of the points raised by SQLSISTER...I'll try as best I can to clarify.

O.K. Our SQLServer is populated with data from another system nightly...and consequently the ASOF date changes to denote the AS-of date of extracted data. Yes! I am able to query the database for date ranges etc. Hopefully I have explained my situation better. So, when they say that they want the 3/31 database this tells me that they want to use the data that was extracted on 3/31...See?

 

[cheyney]

YOu can restore a database backup file to a different database name. You'll just have to alter your report generating process to point to the new (old?) database.

how often do you have to pull reports? how many different reports do you have? you might be better to generate and archive 'all possible reports' (scary thought) when all the data is going to be overwritten

cheyney

 

[branch861]

<<how often do you have to pull reports? how many different reports do you have?>>

This is a once/year type situation and there are numerous reports. I have completed and tested the report requests I've been given so far. And I plan to run those tomorrow. My concern is about the late report requests that I'm sure to get. So, my only option is to restore....to a new database.....huh? YUK!!! I dread this!!

 

[SQLSister]

If you are being populated nightly from a differnt database, then redesigning the database is probably not a good option. You can copy the database as it stands on 3-31 to a new file or backup after it is populated and then restore the backup to a differnt location therby keeping your original intact and having a new copy of the database. Note you will probably need to do this at the end of each quarter, so you will also have a 30 June database, a September 30 database and a Dec 31 database. IF the data you need to access is limited, you could probably get away with setting up another table and copying the data at the set intervals to it. Then run the queries against that. The ASOF date then will be the date of the data you copied. This will become much larger than you original data table though, so make sure you have the space to do this first as records will be repeated with differnt ASOF dates.

 

[branch861]

<<You can copy the database as it stands on 3-31 to a new file >>

I like this option. Sounds easiest and most straight forward for a novice. Right?

<<Note you will probably need to do this at the end of each quarter>>

No as a matter of fact it's only once a year.

<<IF the data you need to access is limited, you could probably get away with setting up another table and copying the data at the set intervals to it. Then run the queries against that. >>

The problem is that I don't know what I may be requested to do. So, I will actually need the entire database just in case. So, copying the database sounds like an easy plan. I pray...

 

[branch861]

I had a thought and wanted your input. What if I imported the tables I needed and used Access to create the reports instead of copying the entire database. Doesn't that sound like a viable option. Please advise at your convenience.