Update Status Field after Expiry Date

SQL Apprentice Question
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.



Celko Answers

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


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.

Incorrect syntax near the keyword 'As'

SQL Apprentice Question
I'm trying to complete an If, Then statement within a script that I put
together, and I keep getting an "Incorrect syntax near the keyword 'As'"
error toward the end of the statement.


The whole statement says that if you are taking the payroll codes from the
benefit table, then provide the payroll code's description such "health
insurance" or "dental insurance".


The very beginning of my clause, of course, says "SELECT ... CASE
UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
ELSE 0 END As [Payroll Description]


There must be something wrong with the ELSE portion. I tried to fix it, by
saying ELSE '' END As [Payroll Description]. But, I got the same error.


What am I doing wrong, and how can I fix this?



Celko Answers
Having blanks inside a column name is a bad idea, since the name will
not port to other languages, is very easy to mis-type and messes up
the data dictionary. You also used the proprietary square brackets
instead of the proper double quotes.

You can make your code a lot easier to read without the extra WHEN
clauses. A CASE expression has one and only one data type. You need
to be sure that the THEN clauses are all the same data type. That can
be done with implicit promotion or use a CAST() function.


CASE
WHEN UPR30300.payrol_cd
IN ('0SC2HF', 'SC2LE', 'SC2LS', 'SC2LC')
THEN UPR40800.dscriptn
ELSE '{{Unknown}}' AS payroll_description

SQL Conundrum

SQL Apprentice Question
I have the following SQL schema

Bookings
-------------
BookID
Hours


BookingStatus
---------------------
BookStatusID
BookingId
StatusID
UserId
Date


Statues
-----------
StatusID


Statuses included Created, Covered, Cancelled, Not Covered, To Be
Arranged, etc.


The BookingStatus table contains a history of the statuses a booking
goes through. Whenever a user updates the status of a booking, a new
record is inserted into the Booking Status with the corresponding
data.


I'm trying to create a report to show the number of bookings each user
has covered over a given time period. However, i only want to count
bookings that are still covered (i.e. have not been cancelled since
they were covered) and i don't want it to count bookings that have
been covered more than once. If it did, a user would be credited for
covering an individual booking more than once.


In other words, i need to fetch only the top 1 status of each booking
and count only those that are covered, if you see what i mean!


I could record the current status of the booking in my Bookings table,
but i'm worried about data integrity and keeping the booking status
consistant.


Anyone got any bright ideas about how i can either design my schema
better, or come up with an efficient SQL query to select my report? I
also need to sum the hours each user has covered, as well as a count
of bookings covered.


Many thanks for any suggestions


Celko Answers
>> Many thanks for any suggestions

Here is a "cut & paste" on history tables. In your case, I would also look at my article on Transition Constraints at www.dbazine.com for more ideas.

===============
The start and stop times are what you should have been catching in the first place and not the computed hours. Think raw data and single facts when designing a table. Let me use a history table for price changes. The fact to store is that a price had a duration:

CREATE TABLE PriceHistory (upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date <> 0.0000),
etc.);


You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
BETWEEN predicate to get the appropriate price.


SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);


It is also a good idea to have a VIEW with the current data:


CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;