STORED PROCEDURE HELP

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

Re: STORED PROCEDURE HELP

Why are you storing calculated values in the table?

Bob Lehmann

"Simon Gare" wrote in message
news:%23JTAdBokHHA.4676@TK2MSFTNGP02.phx.gbl...
> 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
>
>

Re: STORED PROCEDURE HELP

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

Simon

Re: STORED PROCEDURE HELP

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
,
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"

Re: STORED PROCEDURE HELP

Thanks Bob will try that out.

Regards
Simon