STORED PROCEDURE HELP

Discussion in 'ASP General' started by Simon Gare, May 9, 2007.

  1. Simon Gare

    Simon Gare Guest

    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
    Simon Gare, May 9, 2007
    #1
    1. Advertising

  2. Simon Gare

    Bob Lehmann Guest

    Why are you storing calculated values in the table?

    Bob Lehmann

    "Simon Gare" <> wrote in message
    news:%...
    > 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, May 10, 2007
    #2
    1. Advertising

  3. Simon Gare

    Simon Gare Guest

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

    Simon
    Simon Gare, May 10, 2007
    #3
  4. 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"
    Bob Barrows [MVP], May 10, 2007
    #4
  5. Simon Gare

    Simon Gare Guest

    Thanks Bob will try that out.

    Regards
    Simon
    Simon Gare, May 10, 2007
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. TJS
    Replies:
    4
    Views:
    2,110
  2. Jeff Thur
    Replies:
    2
    Views:
    1,010
    Guest
    Feb 7, 2005
  3. rob
    Replies:
    0
    Views:
    419
  4. rob
    Replies:
    0
    Views:
    324
  5. Mike P
    Replies:
    0
    Views:
    3,274
    Mike P
    Jun 19, 2006
Loading...

Share This Page