STORED PROCEDURE HELP

S

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
 
S

Simon Gare

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

Simon
 
B

Bob Barrows [MVP]

Simon said:
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())
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top