Consider the following table

Customer
custId char(10)
accountExpiryDate datetime
accountStatus bit

Now, I want to update the accountStatus to False as soon as the
current date becomes accountExpiryDate.

I think it can be done using "SQL Agent" but my webhost doesnt provide
me access to that. I have access only to the Query Analyzer.

Thanks
Shane

Shane,

If you have access to the database from your workstation (and it seems as
though you do), you can create a batch file using osql and schedule it to
run nightly (or more often) using Windows scheduled tasks.

osql -S servername -U userid -P password -q "update Customer set
accountStatus = 0 where accountExpiryDate < CURRENT_TIMESTAMP and
isnull(accountStatus,1) = 1"

-- Bill

<shane.taylor81 (AT) gmail (DOT) com> wrote

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. Here is my guess, unsupporteed by anythign you told us

CREATE TABLE Customers -- plutal names for sets, please
( cust_id char(10)

Where is it? Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity, data
types, etc. in your schema are. Sample data is also a good idea, along
with clear specifications. It is very hard to debug code when you do
not let us see it. Here is my guess:

CREATE TABLE Customers
(cust_id CHAR(10) NOT NULL PRIMARY KEY, --wild guess
acctexpiry_date DATETIME NOT NULL,
..);

Notice I dropped the redundant BIT column. Some newbie actually used
a proprietary, low-level BIT data type. You need to fix that at once
and teach the guy that SQL has no BOOLEAN data types -- that is just
sooooo fundamental!

Just like you would do this in a punch card system 50 years ago!
Running updates to physical storage every day? You are missing the
fundamental concepts of RDBMS in this design. Each row of a table is
a fact that should stand by itself. Use a VIEW not an assembly
language bit flag!!

CREATE VIEW ActiveCustomers (..)
AS
SELECT cust_id, acctexpiry_date, ..
FROM Customers
WHERE CURRENT_TIMESTAMP < acctexpiry_date;

And then you need to consider how much history and account status
codes you want. Do you need to design an acct_status code? Etc.

--CELKO-- wrote:

Yes and no. It's optional, so *of course* every major implementation
deals with it differently.

http://troels.arvin.dk/db/rdbms/#data_types-boolean

If the expiration date is "expires after this date" rather than "expires
on this date", then change the WHERE clause to

WHERE CURRENT_TIMESTAMP < DateAdd(dd, 1, acctexpiry_date);

(shane.taylor81 (AT) gmail (DOT) com) writes:
Change accountStatus to

accountStatus AS (CASE WHEN accountExpirydate < getdate()
THEN convert(bit, 1)
ELSE convert(bit, 0)
END)

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks to CELKO for being so critical.

1) It is pretty obvious that this cannot be the design for my actual
table, this was to simplify the post.





Works Great!!! However, I would go with CELKO's view solution. I have
to create a VIEW on that table anyways.


To CELKO:- You could have conveyed the message better by being a less
rude

All data types have to be NULL-able in SQL. Having a BOOLEAN type
would lead to 4 valued logic with inconsistent rules about how NULLs
propagate. And the various vendor extension do not work or port
either.

I am always like this in Newsgroups, but I am very nice in person. If
I had any friends you could ask them.

--CELKO-- wrote:

It seems like dropping UNKNOWN would leave a sensible set of rules:

and | T N F or | T N F not |
----+------ ---+------ ----+--
T | T N F T | T T T T | F
N | N N F N | T N N N | N
F | F F F F | T N F F | T

Am I overlooking anything?

On Thu, 29 Mar 2007 18:08:00 -0700, Ed Murphy wrote:

Hi Ed,

You've overlooked the basic rule of NULL propagation: any expression
involving NULL results in NULL. In the tables above, there are
exceptions to this rule, such as NULL AND FALSE resulting in FALSE, and
TRUE OR NULL resulting in TRUE.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis