Post reply

Need your valuable thoughts in changing the query

  • November 6, 2007 at 11:14 am

    #180932

    I just pasted part of the query..Here the Group column in the select identifies what type of data is present in that row..We have total 6 six group types based on the conditions in the where clause.

    For eg in the below case "Non Job Listing Products" is giving as a group when TrialDeal_Flg = 0...In the similar way the group name changes based on the where clauses..

    Currently, there are 6 insert statements to insert the data to single fact table..All the 6 groups data will be there. Can you please suggest me a better alternative instead of writing 6 individual inserts to attain this.

    select'Non Job Listing Products' as Group,

    max(a.DisplayName_DS) Display_Name,

    sum(a.JobsPurchased) Jobs_Purchased,

    sum(a.ActualAmount_AMT) Invoice_Item_Amount,

    fromFTInvoiceItema11 with (nolock)

    joinFTContractValuea12 with (nolock)

    on (a11.Account_ID = a12.Account_ID and

    where(a112.IED_Month_ID in (@YearMonth)

    and a113.usagecapability_id not in (39)

    and a12.TrialDeal_FLG = 0

    and a111.RevenueFamily_ID not in (5, 8, 10)

    group bya.DisplayName_DS

  • November 6, 2007 at 9:56 pm

    #748960

    Not to over simplify too much... but why don't you just use CASE statements?

    --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 2 posts - 1 through 1 (of 1 total)

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