automating end-of-month procedure 1

[invictus28]

Hi guys & gals,
This is a lengthy question, so I broke it down into sections below. Hopefully, someone can offer some advice & suggestions:


Background
=================================
I am the webmaster at LabBoss.com, a site that allows vendors to list used/surplus laboratory equipment for sale. We are developing a new version where our vendors will be charged on a per ad/per day basis. (Currently we charge a flat rate of $15 for 60 days)

It might also be useful to know that we have clients in virtually every advanced nation in the world, so 24-7 operation is pretty important to us.

I would like to know if anyone can suggest a way to automate (without human intervention) the end-of-month archiving of the records that track the days on which an ad has run.

Design Idea
=================================
I have designed the ad tables (SQL7) to look something like this:

adid (int), vendorid(int), several description fields (varchar), initdate (smdatetime), #dayscurrentmonth (int) and several bit fields corresponding to the 31 days in a month. (combining this with the #days in the current month gives an accurate picture of the month) thus:

DAY IN MONTH
--------5---------10--------15--------20--------25--------30
1|0|0|1|1|0|1|1|1|0|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|0|0|0

where 0 means the ad did not run that day and 1 means that it did.

Once a vendor "turns an ad on" for a day, s/he is charged for it for the full day. Thus s/he can only "turn off" an ad starting on the NEXT day whereas s/he can only "turn on" an ad on the current day.

If a vendor were to create an ad on the 4th of April then the inital setting would be thus:

#dayscurrentmonth: 28
--------5---------10--------15--------20--------25--------30
0|0|0|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|0|0|0

If the vendor turned the ad OFF on the 15th, it would look as follows:
--------5---------10--------15--------20--------25--------30
0|0|0|1|1|1|1|1|1|1|1|1|1|1|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0
(note that the vendor is still charged for the 15th since s/he has already been billed for current day and can only affect future days)

If, on the 20th, the indecisive vendor switched the ad back on, it would result in:
--------5---------10--------15--------20--------25--------30
0|0|0|1|1|1|1|1|1|1|1|1|1|1|1|0|0|0|0|1|1|1|1|1|1|1|1|1|0|0|0

The crux of the issue
====================================
Just before midnight, the automatic process would archive this information to a different table, writing the following info:
adid#: 999
vendor#: 999
month: 4
year: 2001
totaldays: 21
monthcost: $5.25 (since rates will vary, we must store this)

The automatic process would also reset the bits thus:
#dayscurrentmonth: 31
--------5---------10--------15--------20--------25--------30
1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1

Then, at his leisure, our billing guy could pull up the archived data and bill the customer for the previous month.


My questions
==============================
1) How to automate the archiving/reset process? We don't want to initiate this process by hand every end-of-month around midnight.
2) Comments on the data design.
3) Any further helpful suggestions.


Thank you,
Eric Griggs, Webmaster
(labboss.com, belari.com, et. al)

 

[vasah20]

In SQL Server 7 you can create use the agent to run a specified task at any interval. In order to get the task to run, you would need to create a stored proc, and execute it every month. If you have no experience with stored procs, they are pretty easy. They use something called T-SQL, which is just an extension to the SQL92 that we all use.

for info:

http://www.sqlteam.com

http://www.swynk.com

the agent is perfect for automating any database tasks that you have. you can even use BOL to learn more about Stored procs and the the Agent... BOL is an invaluable reference.

without knowing more about the tables contained in the DB, it's hard for me to say if your data design is good or not. But, since I was able to understand your example, it looks like it's effective, and from your example it looks like it fits perfectly. So there's probably no issues that you have with your DB.. besides that, it seems like there are a lot of business rules that you have to follow in your application, so your design might be best.

anyways -
hth

---------
Leo Mendoza
lmendoza@students.depaul.edu