• djmason_2001 (10/9/2012)


    hey

    Has anyone else got any ideas on how to solve this issue ?

    CTE's are probably easiest for this, the two queries are I think may be too dissimilar to be merged together:

    ;WITH Query1 AS (

    SELECT

    coalesce (cast(DATENAME(MM,hst_date_processed) as varchar(20)), 'Grand Total') as [Months],

    SUM(ffh.Net_Sales) AS [Sum Of Net Sales],

    SUM(ffh.Net_Trans) AS [Sum Of Net Transactions]

    FROM Fact_Financial_History ffh

    INNER JOIN Dim_Interchange_Tier_2 i

    ON (ffh.hst_prod_code + '-' + ffh.hst_plan_code) = i.Plan_Code

    WHERE ffh.hst_date_processed >= @date

    AND ffh.hst_prod_code IN ('79','81')

    AND ffh.hst_plan_code IN ('002','010','011','015','037','033','021','019','020','030','029')

    AND I.Plan_Code <> '79-021'

    GROUP BY ffh.hst_date_processed

    WITH ROLLUP

    --ORDER BY ffh.hst_date_processed desc

    ),

    Query2 AS (

    SELECT *

    FROM (

    SELECT

    CASE

    WHEN GROUPING(o.FDMSAccountNo_First9) = 1 THEN 'Grand Total'

    ELSE CAST(o.FDMSAccountNo_First9 AS VARCHAR(20))

    END AS FDMSAccountNo_First9,

    o.DBA_Name,

    SUM(ffh.Net_Sales) AS TotalofNetAmount,

    SUM(ffh.Net_Trans)AS TotalofNetTransactions

    FROM Fact_Financial_History ffh

    INNER JOIN Dim_Outlet o

    ON ffh.hst_merchnum = o.FDMSAccountNo_First9

    INNER JOIN dbo.Dim_MCC

    ON o.MCC_Code = dbo.Dim_MCC.MCC

    WHERE ffh.hst_date_processed > @date

    AND ffh.hst_prod_code BETWEEN '79' AND '84'

    AND o.MCC_Code = '5968'

    GROUP BY

    o.FDMSAccountNo_First9,

    o.DBA_Name

    WITH ROLLUP

    ) AS temp

    WHERE temp.DBA_NAME IS NOT NULL

    OR temp.FDMSAccountNo_First9 = 'Grand Total'

    )

    SELECT WhateverYouWant

    FROM Query1

    INNER/LEFT OUTER/FULL OUTER JOIN Query2

    ON Query2.somecolumns = Query1.somecolumns

    ORDER BY SomethingMeaningful


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]