Update Status Field after Expiry Date

[shane.taylor81]

Consider the following table

Customer
custId char(10)
accountExpiryDa te datetime
accountStatus bit

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

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

[AlterEgo]

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 accountExpiryDa te < CURRENT_TIMESTA MP and
isnull(accountS tatus,1) = 1"

-- Bill

<sh************ @gmail.comwrote in message
news:11******** *************@r 56g2000hsd.goog legroups.com...

Consider the following table

Customer
custId char(10)
accountExpiryDa te datetime
accountStatus bit

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

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

[--CELKO--]

>Consider the following table <<

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)

accountExpiryDa te datetime
accountStatus bit

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

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

[--CELKO--]

>Consider the following table <<

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!

>Now, I want to update the account_status to FALSE as soon AS the current date becomes accountexpiry_d ate. <<

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_TIMESTA MP < 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.

[Ed Murphy]

--CELKO-- wrote:

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!

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

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

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

WHERE CURRENT_TIMESTA MP < DateAdd(dd, 1, acctexpiry_date );

[Erland Sommarskog]

(sh************ @gmail.com) writes:

Consider the following table

Customer
custId char(10)
accountExpiryDa te datetime
accountStatus bit

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

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.

Change accountStatus to

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

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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

[shane.taylor81]

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.

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

Thanks again from the "newbie"

>You need to fix that at once and teach the guy that SQL has no BOOLEAN data types -- that is just sooooo >fundamental!

Did not know that.

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

To CELKO : Would want your comments on this for "MS SQL Server"

Change accountStatus to

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

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 :(

[--CELKO--]

>Did not know that. <<

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.

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

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

[Ed Murphy]

--CELKO-- wrote:

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.

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?

[Hugo Kornelis]

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

>--CELKO-- wrote:

>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.

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?

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