Simon Gare wrote:
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?
I'm with Bob. Unless these values need to reflect history, they should not
be stored in the table. Create a view:
CREATE VIEW vBooking_form_calculations AS
SELECT
<the primary key column(s) of booking_form>,
mileage_charge + waiting_charge + CarParkToDriver as total_cost ,
(mileage_charge + waiting_charge + CarParkToDriver)
* 17.5/100 as VAT,
mileage_charge + waiting_charge + CarParkToDriver
+ (mileage_charge + waiting_charge + CarParkToDriver)
* 17.5/100 as GrandTotal ,
MileageToDriver + WaitingToDriver + CarParkToDriver as
TotalToDriver
FROM dbo.booking_form
Now, whenever you need those values, just create a join between this view
and booking_form
Alternatively, if using SQL2000 or above, you can create calculated columns
_in your table_.
For your WHERE clause, you should use:
WHERE allocated = COMPLETED AND TimeOfBooking >= getdate()
AND TimeOfBooking < DATEADD(day,1,getdate())
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
