CODEWITHSUNDEEP
asp.netsqlvb.net
Michael
Michael

Reputation: 21

How do I automate the running of a SQL query?

What should i research in order to accomplish this task?

I am using MS-SQL & VB.Net

I have a SQL table that stores startdate and enddate. I want to run a query on that table every 10 minutes in order to check if the enddate is greater than today and if it is I would like to add a 1 to another column if it is not I would like to add a 0.

How can I achieve this?

Upvotes: 2

Views: 491

Answers (3)

Seph
Seph

Reputation: 8693

Your question seems like you're asking for the wrong thing.

It sounds like you are looking for a trigger to fire off and update a calculated field. So it sounds like you want to setup a database trigger on the table and have that trigger fire on insert or update

USE [<database_name>]
GO

/****** Object:  Trigger [dbo].[tr_<tablename>_endDateCheck]    Script Date: 03/16/2011 12:42:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tr_<tablename>_endDateCheck]
    ON  [dbo].[<tablename>]
    AFTER INSERT,UPDATE
AS 
    BEGIN

        UPDATE [<tablename>] SET isEnded = CASE WHEN (SELECT endDate FROM inserted WHERE id = inserted.id).endDate > GetDate() THEN 1 ELSE 0 END WHERE id IN (SELECT ID FROM inserted)

    END
GO

Or something very similar to this anyway

You then put a scheduled job that will run at 0001 hour and update all the records which had endDate = yesterday's date

UPDATE [<tablename>] SET isEnded = 1 WHERE isEnded = 0 AND endDate BETWEEN DATEADD(DAY, -1, GETDATE()) AND GETDATE()

edit: or is endDate actually time specific as well?

Perhaps in this case you should be using a view to select your data instead with a view definition of:

SELECT *, CASE WHEN endDate > GetDate() THEN 1 ELSE 0 END AS HasEnded
FROM [<TableName>]

Edit2: fixed issue with the scheduled job which was not correct for endDate values that include a time value

Upvotes: 1

Gent
Gent

Reputation: 2685

You can write a SQL Job, or if you want write a service to run against the database, if you are more comfortable in code or the actions require more extensive business rules.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

Create a SQL Server job to run your update query.

UPDATE YourTable
    SET AnotherColumn = AnotherColumn + 1
    WHERE enddate > DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0) /* Today's date at midnight */

Upvotes: 0

Related Questions