SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Grand total Help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Posting Yak Master

241 Posts
Posted - 10/08/2012 :  11:55:27  Show Profile  Reply with Quote
Hey guys

I need some help

i need the grand total from query 1 to be subtracted from the query2 grand total to produce a Final total

Query1


Declare @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 TotalofNetTransactions

FROM
Fact_Financial_History
INNER JOIN
Dim_Outlet
ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
INNER JOIN
dbo.Dim_MCC
ON dbo.Dim_Outlet.MCC_Code = dbo.Dim_MCC.MCC
WHERE
(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_Name
with rollup
) as temp
WHERE temp.DBA_NAME IS NOT NULL OR temp.FDMSAccountNo_First9 = 'Grand Total'













query2

Declare @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_Code
WHERE (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 rollup
order by hst_date_processed desc


eg layout
months sum of net sales sum of net transactions
August 100 300
July 200 200
june 300 100
grandtotal 600 600
Grandtotal 100 200
Final total 500 400
Edited by - masond on 10/08/2012 14:19:58

masond
Posting Yak Master

241 Posts
Posted - 10/08/2012 :  14:19:37  Show Profile  Reply with Quote
Hey,

After re-reading my post, i dont think i am explaing it aswell as i could be.

basically 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 300
July 200 200
june 300 100
(query 1)grandtotal 600 600
(query2)Grandtotal 100 200
Final total 500 400



query1

Declare @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_Code
WHERE (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 rollup
order by hst_date_processed desc


Query2


Declare @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 TotalofNetTransactions

FROM
Fact_Financial_History
INNER JOIN
Dim_Outlet
ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
INNER JOIN
dbo.Dim_MCC
ON dbo.Dim_Outlet.MCC_Code = dbo.Dim_MCC.MCC
WHERE
(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_Name
with rollup
) as temp
WHERE temp.DBA_NAME IS NOT NULL OR temp.FDMSAccountNo_First9 = 'Grand Total'
Edited by - masond on 10/08/2012 14:21:01
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 10/08/2012 :  19:54:20  Show Profile  Reply with Quote
is this for a report? if yes then best thing would be to add this logic in report using ssrs expressions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

masond
Posting Yak Master

241 Posts
Posted - 10/09/2012 :  02:29:51  Show Profile  Reply with Quote
hi visakh16

this is for a report, however i dont have reporting services. so i need to do it via sql
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 10/09/2012 :  23:13:34  Show Profile  Reply with Quote
then use logic like


SELECT
FROM(
...
query1
UNION ALL
query2
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000