Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hong_ma
Starting Member
37 Posts |
 Posted - 2006-01-04 : 15:49:40
|
| in a database table, there are several columns, one fo them is frequency which are monthly, quarterly. semi_annual, annually. there are some records in the table. what I am trying to do? I want to those records will be automaticaly created one itself, which dependes on frequncy. for example, there are three records record1 a1, b1, c1, monthly, d1. record2 a2, b2, c2, quarterly, d2. record3 a3, b3, c3, annaully, d3. I want to record1 every month to create itself. so after one year, there will be 12 same records. for record2, every quarter, will create itself. after a year, there will be 4 same records for record3. one year, only crete one copy of the record. How can do that? Thanks |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-04 : 17:08:56
|
| Create stored procedures to do this, and schedule them as SQL Server Agent jobs to run at the beginning of each month, quarter, or year as needed.CODO ERGO SUM |
 |
|
|
hong_ma
Starting Member
37 Posts |
Posted - 2006-01-04 : 21:44:59
|
| Thanks! Can I get an example? or give more detail. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-04 : 22:04:51
|
| >> there will be 12 same records. Why 12 same records ? Don't you have a primary key on this table ? Can you post the table structure ?-----------------[KH]2006 a new beginning |
|
|
|
hong_ma
Starting Member
37 Posts |
Posted - 2006-01-05 : 16:51:07
|
| for business requirement to need 12 same record. the table has primary key. but 12 same records have different key. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-05 : 20:26:31
|
| Can you post your table structure ? We will be able to help you better if we have more info on the table.-----------------[KH] |
|
|
|
hong_ma
Starting Member
37 Posts |
Posted - 2006-01-06 : 16:26:40
|
| there are two tables which are report and report_history.the report has sereveral fields which are Frequency(monthly,quarterly,semi-Annualy,annually),startDate, reportID(primary key),the report_hisory has several fields which are histioyID(primary key) and reportID(fk), dateDue, periodDate.in the application there are two pages to display the report record and the report_history record. in the report page, it will display Frequency and start date. in the MS SQL server 2000.A job will check the each record. if Frequency is monthly. the job will create a date which is start date Plus one month, on that date. a history record will be created. for example. start date is01/06/2006. Frequency is monthly. then 02/06/2006. job will create the first history record. 03/06/2006,job will create the second record. etc. if Frequency is quarterly, start date is 01/08/2006. then04/08/2006, job will create the first history record. etc the logic like that. Thanks. |
|
|
|
hong_ma
Starting Member
37 Posts |
Posted - 2006-01-11 : 13:50:01
|
| to simply this question, how to use vb.net to call a job in database? how to pass a variable to a job? Thanks. |
|
|
|
|
|
|
|
|
automaticaly create a record