Sum query - 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

Sum query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-30 : 09:30:56

Hey guys

i need some help,

I have built a query, which produces the following results

Parentid mcc_code hst_date_Processed Netsales_rolling
Test 5812 2011-10-11 1000
Test 5812 2011-11-11 1000
Which works perfectly fine
My next part of the query is that , what ever the mcc code is, in my first query ( so in this instance 5812)

I would want to sum the total for all the other parent ids by each month ( but exclude the parent id in the intial query)

my query so far is

Declare @r12 datetime
set @r12 = dateadd(MM,-12,(select MAX(hst_date_processed)
from dbo.Fact_Financial_History))

SELECT
o.ParentID,
o.MCC_Code,
f.hst_date_processed,
SUM([Net_Sales]) as [NetSales_Rolling]

FROM [FDMS].[dbo].[Fact_Financial_History] f inner join fdms.dbo.Dim_Outlet o on f.hst_merchnum = o.FDMSAccountNo_First9
where hst_date_processed >@r12
and RM_Account = 'y'
and parentid = '878020700885'
group by
ParentID,MCC_Code,f.hst_date_processed
order by ParentID

Lookign forward to your help

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-30 : 09:34:10

i know i can write the query as below.Which produces the desired result however , this report will be comming from excel, so the parentid and mcc will always change

Declare @r12 datetime
set @r12 = dateadd(MM,-12,(select MAX(hst_date_processed)
from dbo.Fact_Financial_History))

SELECT
o.MCC_Code,
f.hst_date_processed,
SUM([Net_Sales]) as [NetSales_Rolling]

FROM [FDMS].[dbo].[Fact_Financial_History] f inner join fdms.dbo.Dim_Outlet o on f.hst_merchnum = o.FDMSAccountNo_First9
where hst_date_processed >@r12
and RM_Account = 'y'
and MCC_Code ='5812'
and ParentID not like '878020700885'

group by
MCC_Code,f.hst_date_processed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 09:44:06

[code]
Declare @r12 datetime
set @r12 = dateadd(MM,-12,(select MAX(hst_date_processed)
from dbo.Fact_Financial_History))

[code]
SELECT
o.MCC_Code,
f.hst_date_processed,
[NetSales_Rolling]
FROM [FDMS].[dbo].[Fact_Financial_History] f
inner join fdms.dbo.Dim_Outlet o on f.hst_merchnum = o.FDMSAccountNo_First9
cross apply (SELECT SUM([Net_Sales]) as [NetSales_Rolling]
FROM [FDMS].[dbo].[Fact_Financial_History] f1
inner join fdms.dbo.Dim_Outlet o1 on f1.hst_merchnum = o1.FDMSAccountNo_First9
WHERE RM_Account = 'y'
and MCC_Code = o1.MCC_Code
and ParentID <> f.ParentID
and hst_date_processed > = DATEADD(mm,DATEDIFF(mm,0,f.hst_date_processed),0)
and hst_date_processed < DATEADD(mm,DATEDIFF(mm,0,f.hst_date_processed)+1,0)
)t1
where hst_date_processed >@r12
and RM_Account = 'y'
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources