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
 Division And Sum Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 07/04/2013 :  06:06:05  Show Profile  Reply with Quote
Hey guys

I hoping you will be able to help me

Aim – Create a new column called “% of Sales” . The calculation for this column is a division of the month sales (hst_date_processed)via the total sum of every month.

would appreciate any help available

This is my query
Declare @Rolling12tempFROM varchar(10)
Declare @Rolling12tempTO varchar(10)
set @Rolling12tempFROM = (select dateadd(MM,-11,max(Period))
from [FDMS].[dbo].[Fact_ProcessingCost])
set @Rolling12tempTO = (select (max(Period))
from [FDMS].[dbo].[Fact_ProcessingCost])

--Calculating every Parent Sales For Rolling 12 By month --
SELECT
Dim_Outlet.ParentID,
hst_date_processed as Months,
Sum(Fact_Financial_History.hst_sales_amt) as Sales
--Into #fin
FROM Fact_Financial_History
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where (hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
and ParentID = '878177270880'
group by ParentID, hst_date_processed
having SUM (hst_sales_amt) > 0
Order by Months desc


Which produces the following

ParentID Months Sales
878177270880 01/05/2013 47101314
878177270880 01/04/2013 49201040
878177270880 01/03/2013 47103607
878177270880 01/02/2013 41352269
878177270880 01/01/2013 45916620
878177270880 01/12/2012 41420931
878177270880 01/11/2012 44688557
878177270880 01/10/2012 51193302
878177270880 01/09/2012 46170188
878177270880 01/08/2012 54335833
878177270880 01/07/2012 55296355
878177270880 01/06/2012 46528750



Expected result

ParentID Months Sales % of Sales
878177270880 01/05/2013 47101314 8%
878177270880 01/04/2013 49201040 9%
878177270880 01/03/2013 47103607 8%
878177270880 01/02/2013 41352269 7%
878177270880 01/01/2013 45916620 8%
878177270880 01/12/2012 41420931 7%
878177270880 01/11/2012 44688557 8%
878177270880 01/10/2012 51193302 9%
878177270880 01/09/2012 46170188 8%
878177270880 01/08/2012 54335833 10%
878177270880 01/07/2012 55296355 10%
878177270880 01/06/2012 46528750 8%

Total 570308766 100.00%













Edited by - masond on 07/04/2013 06:12:14

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/04/2013 :  07:17:42  Show Profile  Reply with Quote

SELECT *,
Sales/Sum(Sales) OVER () AS [%Sales]
FROM
(
SELECT
Dim_Outlet.ParentID,
hst_date_processed as Months,
Sum(Fact_Financial_History.hst_sales_amt) as Sales
--Into #fin
FROM Fact_Financial_History 
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9 
where (hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
and ParentID = '878177270880'
group by ParentID, hst_date_processed
having SUM (hst_sales_amt) > 0
)t
Order by [Months] desc


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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 07/04/2013 :  08:12:43  Show Profile  Reply with Quote
hi visakh16

Thank you for your post

If i want to produce an other column which is called "%Index"
The calculation for this column is a average of the month sales (hst_date_processed)via the total sum of every month
how would i do that ?

i have tried adapting your query, but i am getting the same value being populated within my dataset

SELECT *,
Sales/Sum(Sales) OVER () AS [%Sales],
AVG(Sales) OVER () AS [Index]
FROM
(
SELECT
Dim_Outlet.ParentID,
hst_date_processed as Months,
Sum(Fact_Financial_History.hst_sales_amt) as Sales
--Into #fin
FROM Fact_Financial_History
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
where (hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
and ParentID = '878177270880'
group by ParentID, hst_date_processed
having SUM (hst_sales_amt) > 0
)t
Order by [Months] desc
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/04/2013 :  08:48:04  Show Profile  Reply with Quote

SELECT *,
Sales/Sum(Sales) OVER () AS [%Sales],
AvgSales/Sum(Sales) OVER () AS [%Index]
FROM
(
SELECT
Dim_Outlet.ParentID,
hst_date_processed as Months,
Sum(Fact_Financial_History.hst_sales_amt) as Sales,
Avg(Fact_Financial_History.hst_sales_amt * 1.0) as AvgSales
--Into #fin
FROM Fact_Financial_History 
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9 
where (hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
and ParentID = '878177270880'
group by ParentID, hst_date_processed
having SUM (hst_sales_amt) > 0
)t
Order by [Months] desc


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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 07/04/2013 :  09:10:16  Show Profile  Reply with Quote
HI Visakh16

Thank you for your response, However i am not getting the required results which i expect

By using your logic i am producing the following

ParentID Months Sales AvgSales %Sales %Index
8.78177E+11 01/05/2013 47101313.87 37590.8331 8.25% 0.01%
8.78177E+11 01/04/2013 49201040.47 38924.87379 8.62% 0.01%
8.78177E+11 01/03/2013 47103607.48 38832.32274 8.25% 0.01%
8.78177E+11 01/02/2013 41352268.8 35163.49388 7.25% 0.01%
8.78177E+11 01/01/2013 45916619.75 37947.61963 8.05% 0.01%
8.78177E+11 01/12/2012 41420931.15 34719.97582 7.26% 0.01%
8.78177E+11 01/11/2012 44688557.3 36010.11869 7.83% 0.01%
8.78177E+11 01/10/2012 51193301.79 41586.76019 8.97% 0.01%
8.78177E+11 01/09/2012 46170187.7 38668.49891 8.09% 0.01%
8.78177E+11 01/08/2012 54335833.06 43192.23614 9.52% 0.01%
8.78177E+11 01/07/2012 55296355.01 43132.88222 9.69% 0.01%
8.78177E+11 01/06/2012 46528749.85 37889.86144 8.15% 0.01%


What i should be getting is the following

ParentID Months Sales AvgSales %Sales %Index
8.78177E+11 01/05/2013 47101313.87 37590.8331 8.25% 99.11%
8.78177E+11 01/04/2013 49201040.47 38924.87379 8.62% 103.53%
8.78177E+11 01/03/2013 47103607.48 38832.32274 8.25% 99.11%
8.78177E+11 01/02/2013 41352268.8 35163.49388 7.25% 87.01%
8.78177E+11 01/01/2013 45916619.75 37947.61963 8.05% 96.61%
8.78177E+11 01/12/2012 41420931.15 34719.97582 7.26% 87.15%
8.78177E+11 01/11/2012 44688557.3 36010.11869 7.83% 94.03%
8.78177E+11 01/10/2012 51193301.79 41586.76019 8.97% 107.72%
8.78177E+11 01/09/2012 46170187.7 38668.49891 8.09% 97.15%
8.78177E+11 01/08/2012 54335833.06 43192.23614 9.52% 114.33%
8.78177E+11 01/07/2012 55296355.01 43132.88222 9.69% 116.35%
8.78177E+11 01/06/2012 46528749.85 37889.86144 8.15% 97.90%


To produce the %index Column i need to look at the indvidual month sales, and then work out a % average from the total sales
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/04/2013 :  09:44:35  Show Profile  Reply with Quote
quote:
Originally posted by masond



If i want to produce an other column which is called "%Index"
The calculation for this column is a average of the month sales (hst_date_processed)via the total sum of every month



Do you mean this:


SELECT *,
Sales/Sum(Sales) OVER () AS [%Sales],
AvgSales*Months*100/Sum(Sales) OVER ()   AS [%Index]FROM
(
SELECT
Dim_Outlet.ParentID,
hst_date_processed as Months,
Sum(Fact_Financial_History.hst_sales_amt) as Sales,
Avg(Fact_Financial_History.hst_sales_amt * 1.0) as AvgSales
--Into #fin
FROM Fact_Financial_History 
INNER JOIN Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9 
where (hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
and ParentID = '878177270880'
group by ParentID, hst_date_processed
having SUM (hst_sales_amt) > 0
)t
Order by [Months] desc


Can you explain in mathematical terms what "%Index" column should be?

Edited by - MuMu88 on 07/04/2013 10:07:24
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