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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Auto-update Based on Today's Date and Future Date?

Author  Topic 

tkepongo
Yak Posting Veteran

62 Posts
Posted - 2011-08-16 : 13:52:32
(DB Forums, sqlservercentral Cross Post)

Hi Everyone,

I am fairly new with SQL Server and don't know how to approach this problem.

I'm using SQL Server 2005 Express and I have an Audit table with an "AuditStatus" and "ScheduleDate" column that contains the day the audit has been scheduled (mm/dd/yyyy format). I also have two tables that contains the calendar quarters and what months are associated with each quarter (Q1:1,2,3; Q2:4,5,6, Q3:7,8,9; Q4:10,11,12).

The audit can be scheduled to a calendar quarter without a schedule date. However, if there is no schedule date, and today's date is two weeks before the assigned quarter begins, the audit status needs to be changed.

For example: Let's say I schedule an audit for Q4 2011 today without a schedule date. Then September 16th 2011 arrives. There is still no schedule date for the audit. SQL Server must somehow automatically update the Audit's status to "Delinquent" because October 1st is the first day of the 4th quarter.

So, is there a way for SQL Server to automatically update the "AuditStatus" column if today's date is two weeks before the beginning of an assigned quarter?

Thank you all for your time and help!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2011-08-16 : 14:39:36
Yes there is. But we must see your table DDL and have some proper sample data.
Also an expected output is wanted.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

tkepongo
Yak Posting Veteran

62 Posts
Posted - 2011-08-16 : 16:49:18
Here's the DDL for the tables. 

	CREATE TABLE tbl_Audit
	(
	Audit_ID int identity(4120505,1),
	ScheduledQuarter varchar(5) not null, 
	ScheduledYear smallint not null,
	ScheduleDate smalldatetime,
	Audit_Status varchar (100),
	Primary key clustered (Audit_ID),
	Unique (Group_Name, ScheduledYear, ScheduledQuarter)

	CREATE TABLE tbl_CalendarQuarters
	(
	ID smallint identity (1,1) primary key,
	Quarter varchar(5),
	Months smallint,
	Unique(Quarter, Months)
	)


So an Audit record can look like (column names changed for simplicity)

Audit_ID|Quarter|Year |Date |Status
12345|Q1|2011|Null|Pending Date|

The Calendar Quarters table looks like
Quarter|Month|
Q1|01
Q1|02....
Q4|12

So lets say with Audit# 12345, I have assigned it to Q1 2011 but with no schedule date yet. Today is is 03/16/2011 (two weeks before Q2) and there still is no schedule date for the audit. I want the record to automatically be updated to:

Audit_ID|Quarter|Year |Date |Status
12345|Q1|2011|Null|[Delinquent|

I am using Access 2007 as a front-end with SQL Server 2005 Express as a back end. Some have suggested SQL Server Scheduled Jobs, but that is not possible with the Express edition.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2011-08-16 : 17:15:43
Sort of. See http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

tkepongo
Yak Posting Veteran

62 Posts
Posted - 2011-08-16 : 18:17:59
quote:
Originally posted by SwePeso

Sort of. See http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express


N 56°04'39.26"
E 12°55'05.63"


Thanks for the link. I'll look into it and hopefully everything will work out fine!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site