Grand total Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grand total Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-08 : 11:55:27

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-08 : 14:19:37

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'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-08 : 19:54:20

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/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-09 : 02:29:51

hi visakh16

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-09 : 23:13:34

then use logic like


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources