sql server programming: Not 100% sure what you are trying to do, but if you are trying to
limit a WHERE clase to a single day's records, I would suggest
something like this (Pay particular attention to the >= and <
symbols):

CREATE PROCEDURE TestSproc
@THE_DATE DateTime -- Contains something like '2006-12-25
00:00:00.000'
AS

SELECT *
FROM [TABLE1]
WHERE CreateDate >= @THE_DATE
AND CreateDate < DATEADD(d, 1, @THE_DATE)

GO

Call it like this:

EXECUTE TestSproc '2006-12-25'

You could try something like this:

DECLARE @total_cost NUMERIC(20, 4),
@VAT NUMERIC(20, 4)
UPDATE dbo.booking_form
SET @total_cost = mileage_charge + waiting_charge + CarParkToDriver,
total_cost = @total_cost,
@VAT = @total_cost * 17.5/100,
VAT = @VAT,
GrandTotal = @total_cost + @VAT,
TotalToDriver = MileageToDriver + WaitingToDriver + CarParkToDriver

Or you could do something like this without the variables:

UPDATE dbo.booking_form
SET total_cost = mileage_charge + waiting_charge + CarParkToDriver,
VAT = (mileage_charge + waiting_charge + CarParkToDriver) * 17.5/100,
GrandTotal = (mileage_charge + waiting_charge + CarParkToDriver) +
((mileage_charge + waiting_charge + CarParkToDriver) * 17.5/100),
TotalToDriver = MileageToDriver + WaitingToDriver + CarParkToDriver

[quoted text, click to view]

That's a client-side issue, where your client app would have some sort of
timer on it (.NET has several handy timers which could work well for
something like this in a Windows service). The query I gave you isn't a
complete stored procedure, it's just a single statement. You can use CREATE
PROCEDURE (see BOL) to turn it into a stored procedure. Your client could
be timed to call it on a regular basis. If you want it in "real-time",
modify the UPDATE statement to work in a trigger on the table. It would
fire on UPDATEs or INSERTs, and would cause the relevant columns to be
updated every time a new row was inserted, or an existing row was updated.
See BOL for specifics on triggers.

[quoted text, click to view]

Hi need a stored procedure to replace the 4 commands listed below

UPDATE dbo.booking_form SET total_cost = mileage_charge + waiting_charge +
CarParkToDriver
UPDATE dbo.booking_form SET VAT = total_cost * 17.5/100
UPDATE dbo.booking_form SET GrandTotal = total_cost + VAT
UPDATE dbo.booking_form SET TotalToDriver = MileageToDriver +
WaitingToDriver + CarParkToDriver

all of them have the same WHERE clause which I need a little assistance with
the date issue

WHERE allocated = COMPLETED AND TimeOfBooking BETWEEN '" + getdate(), "'",
"''" + " 00:00:00' AND '" + getdate(), "'", "''" + " 23:59:59'

also how to call it?

Thanks


--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk

the WHERE clause is

WHERE allocated = 'COMPLETED' AND TimeOfBooking BETWEEN '" & Date() & "
00:00:00' AND '" & Date() & " 23:59:59'

Thanks


[quoted text, click to view]

Thanks for the reply Robert I am more concerned with the Calculation
statement than the date at the moment have you any ideas on that for me?

UPDATE dbo.booking_form SET total_cost = mileage_charge + waiting_charge +
CarParkToDriver
UPDATE dbo.booking_form SET VAT = total_cost * 17.5/100
UPDATE dbo.booking_form SET GrandTotal = total_cost + VAT
UPDATE dbo.booking_form SET TotalToDriver = MileageToDriver +
WaitingToDriver + CarParkToDriver


Regards
Simon

Thanks Mike,

couple of things, how would I call the procedure? or could it be a time
event every so many minutes it triggers etc?

Regards
Simon

[quoted text, click to view]

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.

Also, learn to describe your problem instead of the solution you want
to force to work. For example, any RDBMS person seeing a column
called "total_cost" knows that your whole schema is a nightmare.
Let's all say it together, from the 4-th week of our DB Design course
--"Never store a computed value in a row!" We put them in a VIEW.

Also, we do not model forms in RDBMS -- we model facts. Forms are
paper or screens that capture whole or partial facts. That was the 2-
nd or 3-rd week of class.

CREATE TABLE Bookings
(booking_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
CHECK (booking_date = DATEADD(d, DATEDIFF(d, 0, booking_date),
0)),
mileage_charge DECIMAL (8,4) NOT NULL
CHECK (mileage_charge >= 0.00)
waiting_charge DECIMAL (8,4) NOT NULL
CHECK (mileage_charge >= 0.00)
car_park_charge DECIMAL (8,4) NOT NULL
CHECK (mileage_charge >= 0.00),
etc,);

[quoted text, click to view]

Why are you using the proprietary getdate() when the Standard
CURRENT_TIMESTAMP is supported? And why are you using them
incorrectly? Can you explain what was allocated and what was
completed?

[quoted text, click to view]

Hi Simon,

If you want changes in the underlying data to propagate immediately to
the other columns, than define them as computed columns. Those are
always up to date; no additional code required.

CREATE TABLE Example
(Id int NOT NULL PRIMARY KEY,
a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
d AS a + b,
-- e AS d - c -- Can't reuse computed column;
e AS (a + b) - c -- Have to repeat expression instead.
);
go
INSERT INTO Example (Id, a, b, c)
VALUES (1, 1, 1, 1);
INSERT INTO Example (Id, a, b, c)
VALUES (2, 3, 4, 5);
SELECT * FROM Example;
UPDATE Example
SET c = 8
WHERE Id = 1;
SELECT * FROM Example;
go
DROP TABLE Example;
go


--
Hugo Kornelis, SQL Server MVP