STORED PROCEDURE HELP

[Simon Gare]

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

 

[Bob Lehmann]

Why are you storing calculated values in the table?

Bob Lehmann

 

[Simon Gare]

Because I cant think of anyway else to do it Bob, unless you can suggest
something?

Simon

 

[Bob Barrows [MVP]]

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

 

[Simon Gare]

Thanks Bob will try that out.

Regards
Simon