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 |
|
arty
Starting Member
13 Posts |
 Posted - 2005-09-29 : 21:35:18
|
| I am doing an Accounts Project in which I have a database with about 15 columns of which one is a Primary Key-Seeded column(Auto). All the records created are viewed in a Datagrid.I also have a date field(journaldate) set to 'today' -->which gives & saves current date each time the form is opened.The application needs that on the 1st of every Month all the records of previous month should automatically disappear from the datagrid, the records should still be in the database but should not be visible on the datagrid.eg: if journaldate = 01/10/2005, all the records of September should disappear.....I have used a 16th column 'active?' and I set it to '1' in the Insert statement and display the records if the active? = 1. This is working & all records are displaying fine.I need a stored procedure to tell the 'active?' flag to set itself to '0' on 1st of every Month and also a check that only the records of previous month disappear.Any Help is much appreciated.Thanks a Zillion in advance.Aartee-We Live More Than Once!!! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-29 : 21:39:15
|
| You can use a SQL Agent job. This job would be scheduled to run on the 1st of every month. It would run an UPDATE statement as the first step. The second step would be to do your disappearing magic, which I'm not clear on what this means.Tara |
 |
|
|
arty
Starting Member
13 Posts |
Posted - 2005-09-29 : 21:51:35
|
Thankyou for the prompt help Tara.Well, is it possible for you to explain the solution with an example...that would really great.secondly,the disppearing part means, if I create new records in the Journal between say 1st and 30th/31st or every month, I should be able to see all those records everyday,all time :) on the datagrid...but as soon as it is the first of next month all my records created between 1st & 30th/31st of previous month should not be seen on the datagrid...which also means I can create and store my record created on 1st of the new month until 1st of next month....In other words it would also mean that on 1st of every month the datagrid should be empty becuase the date is an automatic field and user entry field so back dated or post dated records cannot be created....too confusing??? Aartee-We Live More Than Once!!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-29 : 21:55:51
|
| You just need to use a WHERE clause in your query to determine what to display in the datagrid.SELECT * FROM YourTableWHERE Active = 1Then in SQL Server Agent, you setup a job to update the Active column once per month. You can get to the jobs via Management in Enterprise Manager. UPDATE YourTableSET Active = 0WHERE SomeDateColumn < GETDATE()Tara |
|
|
|
|
|
|
Hiding Records