Not an easy question, atleast I don't think i

Results 1 to 3 of 3

Thread: Not an easy question, atleast I don't think i

  1. #1
    Join Date
    Dec 1969

    Default Not an easy question, atleast I don't think i

    Ok here goes folks. I have a little scenario to explain, then maybe somebody could tell me how they would go about accomplishing this task. Lets say you have a database filled with user statistics. like what I have here;<BR><BR>Now, what I would like to do is at the 1st of every month clear everyones score, to zero and all of their accomplishments to zero while saving the previous months stats. How would I go about doing this, other than uploading a blank database each month, and re inserting everyones username password in that bland db, I do have an edit feature behind the app that would let me reset everyone to zero, but I would have to do it manually, is there an automated function that would allow me to do this at the beginning of each month.<BR>thanks<BR>tbank

  2. #2
    Join Date
    Dec 1969

    Default RE: Not an easy question, atleast I don't thi

    I have brinkster, but I can&#039;t remember what third part components they have.<BR><BR>If it&#039;s SQL Server, you can run a query at a predetirmed time. As far as third part components, you won&#039;t be able to upload to brinkster (but check if they have ones that allow you to execute your ASP at timed intervals).<BR><BR>Alternatively, and not good solution, but one that works, is do it yourself every month or do a check on your home page (a serious waste of resources, but...) to see if it&#039;s time to update the db.<BR><BR>You should check with Brinkster first to see what they have.

  3. #3
    Join Date
    Dec 1969

    Default Well, tedious at best, but maybe not *too* hard...

    It depends on how much accessibility you have to the underlying database. If you have full control then:<BR><BR>(1) Either schedule a script to run at 0:00:01 AM on the first of every month *or* have script in the loging page that checks to see if a new month has started without the DB being updated.<BR><BR>(2) Use SELECT INTO to, essentially, copy all the needed info from the current table into another *table* (please don&#039;t use a separate DB! Really bad practice and makes later cross-month queries much harder and slower), one perhaps named for the month and year you are backing up. If you prefer to stuff the info into a *single* history table, you could use INSERT INTO instead.<BR><BR>************<BR><BR>But I just realized: You could avoid the whole problem by using a *NORMALIZE* database! I keep preaching normalization, and this is a good spot to use one!<BR><BR>Instead of storing the stats for each user right in the user record in the main table, you have a *separate* "statistics" table that is linked to the main table via userid (that is, the foreign key in the statistics table refers to the primary key in the users table). The statistics table *ALSO* has a "Year/Month" field!!!<BR><BR>Now, when you add to a user&#039;s statistics, you simply be sure to specify the proper Year/Month to affect! And now you have 100% of the history of every user stored in one table! Talk about being able to make *efficient* queries whenever you want to!<BR><BR>You&#039;d easily be able to graph a user&#039;s scores on a month by month instance, for example. A very fast and very efficient query.<BR><BR>SO *do* please consider reorganizing your DB along the classic "normalization" lines!<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts