InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 05:19:00 GMT20http://www.sqlservercentral.com/Forums/Topic1369986-391-1.aspx[quote][b]djmason_2001 (10/9/2012)[/b][hr]Hi thank you for your response In your last part of the query i dont get what this is meant to do I understand you want me to select certain columns/titles. but the on query2.somcolumns is confusing me SELECT WhateverYouWantFROM Query1INNER/LEFT OUTER/FULL OUTER JOIN Query2 ON Query2.somecolumns = Query1.somecolumnsORDER BY SomethingMeaningful --------------------------------------------------------------------------------[/quote]I've no idea how you might join these two CTE's together because I can't see your data - only you know that. "somecolumns" is a placeholder for what the real columns might be.Tue, 09 Oct 2012 03:20:58 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1369986-391-1.aspxHi thank you for your response In your last part of the query i dont get what this is meant to do I understand you want me to select certain columns/titles. but the on query2.somcolumns is confusing me SELECT WhateverYouWantFROM Query1INNER/LEFT OUTER/FULL OUTER JOIN Query2 ON Query2.somecolumns = Query1.somecolumnsORDER BY SomethingMeaningful --------------------------------------------------------------------------------Tue, 09 Oct 2012 02:42:22 GMTdjmason_2001http://www.sqlservercentral.com/Forums/Topic1369986-391-1.aspx[quote][b]djmason_2001 (10/9/2012)[/b][hr]hey Has anyone else got any ideas on how to solve this issue ?[/quote]CTE's are probably easiest for this, the two queries are I think may be too dissimilar to be merged together:[code="sql"];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 WhateverYouWantFROM Query1INNER/LEFT OUTER/FULL OUTER JOIN Query2 ON Query2.somecolumns = Query1.somecolumnsORDER BY SomethingMeaningful [/code]Tue, 09 Oct 2012 01:46:21 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1369986-391-1.aspxhey Has anyone else got any ideas on how to solve this issue ?Tue, 09 Oct 2012 00:34:51 GMTdjmason_2001http://www.sqlservercentral.com/Forums/Topic1369986-391-1.aspxHi it is in a report, i will need to show each value so for eg i need to see grand total of query1grand total of query2and the difference once query2 has been subtracted from query1. which produce a new totalMon, 08 Oct 2012 15:09:35 GMTdjmason_2001http://www.sqlservercentral.com/Forums/Topic1369986-391-1.aspxIs this a report? If so, a calculated field would be easier. If not, there are a few ways to deal with this. They all involve the basic premise of:[code]select (select sum(col) as Total from Query1) - (select sum(col) as OtherTotal from Query2) as NewTotal[/code]This could be done in a collection of subqueries like above or you could do it in a cte. Either way, that is basically what you need to do.Mon, 08 Oct 2012 15:04:30 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1369986-391-1.aspxDoes anyone have any ideas?Mon, 08 Oct 2012 14:33:22 GMTdjmason_2001http://www.sqlservercentral.com/Forums/Topic1369986-391-1.aspxHey guys This is my first post on this website, so please be gentle i need some help producing a grand total i have query 1 (which is below) which produces a grand total, i then have query 2 ( which is below query1) which also produces a grand total on there. i need the grand total from query 2 to be subtracted from the query1 grand total to produce a Final total For eg layout months sum of net sales sum of net transactions August 100 300July 200 200june 300 100(query 1)grandtotal 600 600(query2)Grandtotal 100 200Final total 500 400query1Declare @date varchar(10)set @Date = (select dateadd(MM,-2,max(hst_date_processed))from FDMS.dbo.Fact_Financial_History) SELECT coalesce (cast(DATENAME(MM,hst_date_processed) as varchar(20)), 'Grand Total') as 'Months',SUM(Fact_Financial_History.Net_Sales) AS [Sum Of Net Sales], SUM(Fact_Financial_History.Net_Trans) AS [Sum Of Net Transactions]FROM Fact_Financial_History INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History.hst_prod_code + '-' + Fact_Financial_History.hst_plan_code = Dim_Interchange_Tier_2.Plan_CodeWHERE (Fact_Financial_History.hst_date_processed >= @date)--and Dim_Interchange_Tier_2.Qualification_2 in ('moto')and (dbo.Fact_Financial_History.hst_prod_code='79' Or dbo.Fact_Financial_History.hst_prod_code='81')and (dbo.Fact_Financial_History.hst_plan_code= '002'Or dbo.Fact_Financial_History.hst_plan_code='010' Or dbo.Fact_Financial_History.hst_plan_code='011'Or dbo.Fact_Financial_History.hst_plan_code='015' Or dbo.Fact_Financial_History.hst_plan_code='037' Or dbo.Fact_Financial_History.hst_plan_code='033' Or dbo.Fact_Financial_History.hst_plan_code='021' Or dbo.Fact_Financial_History.hst_plan_code='019' Or dbo.Fact_Financial_History.hst_plan_code='020' Or dbo.Fact_Financial_History.hst_plan_code='030' Or dbo.Fact_Financial_History.hst_plan_code='029')and Dim_Interchange_Tier_2.Plan_Code <> '79-021'GROUP BY hst_date_processed with rolluporder by hst_date_processed descQuery2Declare @date varchar(10)set @Date = (select dateadd(MM,-2,max(hst_date_processed))from FDMS.dbo.Fact_Financial_History) SELECT * FROM ( SELECT CASE WHEN GROUPING([dbo].[Dim_Outlet].FDMSAccountNo_First9) = 1 THEN 'Grand Total'ELSE CAST([dbo].[Dim_Outlet].FDMSAccountNo_First9 AS VARCHAR(20))END AS FDMSAccountNo_First9,dbo.Dim_Outlet.DBA_Name,SUM(dbo.Fact_Financial_History.Net_Sales) AS TotalofNetAmount, SUM(dbo.Fact_Financial_History.Net_Trans)AS TotalofNetTransactionsFROM Fact_Financial_History INNER JOINDim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9INNER JOIN dbo.Dim_MCC ON dbo.Dim_Outlet.MCC_Code = dbo.Dim_MCC.MCCWHERE (dbo.Fact_Financial_History.hst_prod_code BETWEEN '79' AND '84')and MCC_Code = '5968' AND (dbo.Fact_Financial_History.hst_date_processed > @date)group by [dbo].[Dim_Outlet].FDMSAccountNo_First9,[dbo].[Dim_Outlet].DBA_Namewith rollup) as temp WHERE temp.DBA_NAME IS NOT NULL OR temp.FDMSAccountNo_First9 = 'Grand Total'Mon, 08 Oct 2012 12:18:22 GMTdjmason_2001