grouping help

  • For some reason I cant seem to figure this out without having to slice and dice with temp tables. Here is the data......

    typemonth_nbrmonth_nameamount

    Billing1Jan0.0000

    Labor1Jan96862.7200

    Billing2Feb0.0000

    Labor2Feb125233.2200

    Billing3Mar0.0000

    Labor3Mar102244.6200

    Billing4Apr0.0000

    Labor4Apr84076.2100

    Billing5May0.0000

    Labor5May137787.8500

    Billing6Jun0.0000

    Labor6Jun105135.5700

    Billing7Jul0.0000

    Labor7Jul113227.9800

    Billing8Aug0.0000

    Labor8Aug144289.1100

    Billing9Sep380500.0050

    Labor9Sep116142.8500

    Billing10Oct985231.0814

    Labor10Oct142803.6700

    Billing11Nov902050.0530

    Labor11Nov113975.2100

    Billing12Dec623420.2364

    Labor12Dec83180.1200

    I want to take Labor row and divide by Billing grouped by Month. So the results would be.....

    1 Jan 0.00

    2 Feb 0.00

    3 Mar 0.00

    4 Apr 0.00

    5 May 0.00

    6 Jun 0.00

    7 Jul 0.00

    8 Aug 0.00

    9 Sep 0.305237

    10 Oct 0.144944

    11 Nov 0.126351

    12 Dec 0.133425

  • If your table was

    CREATE TABLE someData

    (

    type varchar(20),

    month_nbr int,

    month_name varchar(20),

    amount decimal(10, 4)

    )

    then your query would be

    SELECT month_nbr,

    month_name,

    case when billingData.amount != 0.0

    then laborData.amount / billingData.amount

    else 0.0

    end as result

    FROM someData as laborData INNER JOIN

    someData as billingData

    on laborData.month_nbr = billingData.month_nbr

    and billingData.type = 'Billing'

    WHERE laborData.Type = 'Labor'

    Atlantis Interactive - SQL Server Tools
    My blog[/url]
    Why I wrote a sql query analyzer clone

  • thank you!

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

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