djmason_2001 (10/9/2012)
heyHas 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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]