Post reply

Calculating Agregate value based on select statements from different tables

  • March 4, 2008 at 3:30 pm

    #184721

    Hi Everyone, new to the board...glad to have found it.

    I'm trying to calculate a numeric value based on queries from two different tables.

    this is query number one:

    SELECT SUM(ROOMREVENUE) AS 'ROOM REVENUE' FROM RPT_HOTEL_STATS WHERE PROPERTY = 'DELSOL'

    AND STAT_DATE > '2007-12-31 00:00:00.000'

    AND STAT_DATE <= DATEDIFF(day, 0, getdate())

    this is query two:

    select SUM(NUMROOMS) FROM AVAILABILITY_ROOMS WHERE RES_DATE > '2007-12-31 00:00:00.000'

    AND RES_DATE <= DATEDIFF(day, 0, getdate())

    I want to divide query result 1 by query result 2!!

    can anyone help with syntax?

  • March 4, 2008 at 4:25 pm

    #785257

    Welcome aboard... first, here's one answer two your problem...

    SELECT

    (

    SELECT SUM(ROOMREVENUE) AS 'ROOM REVENUE' FROM RPT_HOTEL_STATS WHERE PROPERTY = 'DELSOL'

    AND STAT_DATE > '2007-12-31 00:00:00.000'

    AND STAT_DATE <= DATEDIFF(day, 0, getdate())

    )

    /

    (

    select SUM(NUMROOMS) FROM AVAILABILITY_ROOMS WHERE RES_DATE > '2007-12-31 00:00:00.000'

    AND RES_DATE <= DATEDIFF(day, 0, getdate())

    )

    Second, click on the URL in my signature for ideas on how to get some good answers quickly... it's a little bit long, but well worth the read. You'll be amazed at how quick folks respond to well formatted code, table creation statements, and data they don't have to convert to load.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 5, 2008 at 10:18 am

    #785620

    Thank you, worked great. Thanks for the advice, I will read that thread.

  • March 5, 2008 at 10:31 am

    #785640

    And thanks for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply