SQL Calculate Yearly Average Balance for ForecastHow can I prevent SQL injection in PHP?How do I perform an IF…THEN in an SQL SELECT?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeInserting multiple rows in a single SQL query?How do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableT-SQL average calculationHow to import an SQL file using the command line in MySQL?Calculating difference between daily sum and a average for the same day of the week in defined time range. SQL 10g Oracle
Is camera lens focus an exact point or a range?
Folder comparison
Why in book's example is used 言葉(ことば) instead of 言語(げんご)?
How to color a curve
Reply 'no position' while the job posting is still there
How much character growth crosses the line into breaking the character
Proving a function is onto where f(x)=|x|.
Did US corporations pay demonstrators in the German demonstrations against article 13?
A social experiment. What is the worst that can happen?
Will adding a BY-SA image to a blog post make the entire post BY-SA?
How do I repair my stair bannister?
Difference between -| and |- in TikZ
Why does the integral domain "being trapped between a finite field extension" implies that it is a field?
Is it possible to use .desktop files to open local pdf files on specific pages with a browser?
What (else) happened July 1st 1858 in London?
THT: What is a squared annular “ring”?
Should I stop contributing to retirement accounts?
Transformation of random variables and joint distributions
Divine apple island
How will losing mobility of one hand affect my career as a programmer?
Flux received by a negative charge
Should I install hardwood flooring or cabinets first?
Is XSS in canonical link possible?
What is the grammatical term for “‑ed” words like these?
SQL Calculate Yearly Average Balance for Forecast
How can I prevent SQL injection in PHP?How do I perform an IF…THEN in an SQL SELECT?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeInserting multiple rows in a single SQL query?How do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableT-SQL average calculationHow to import an SQL file using the command line in MySQL?Calculating difference between daily sum and a average for the same day of the week in defined time range. SQL 10g Oracle
I'm trying to find a more efficient way to find the Yearly Average Balance for Accounts in a SQL table.
The number of days for each period are listed below, is there a more efficient way to code this? The #TEMPAVG table itself is a pivot of fields with Period containing the data. (Periods on Columns with #'s, Rows contain all other fields)
Yearly average Balance is calculated by Summarizing each time period, multiplying by # of days, and then dividing by total number of days.
I attempted to insert these Day values into a #TEMPDAYS table but couldn't figure out how to join the data correctly for multiplication of time periods.
TEMP DAYS Table
INSERT INTO #TEMPDAYS(Period,Days)
VALUES
('P01','30'),('P02','31'),('P03','31'),('P04','28'),('P05','31'),('P06','30'),('P07','31'),('P08','30'),('P09','31'),('P10','31'),('P11','30'),('P12','31')
,('Jan','31'),('Feb','28'),('Mar','31'),('Apr','30'),('May','31'),('Jun','30'),('Jul','31'),('Aug','31'),('Sep','30'),('Oct','31'),('Nov','30'),('Dec','31');
Days for Period Avgs
-- DAYS FOR EACH PERIOD ('P01','30'),('P02','31'),('P03','31'),('P04','28'),('P05','31'),('P06','30'),('P07','31'),('P08','30'),('P09','31'),('P10','31'),('P11','30'),('P12','31')
Yearly Average Balance Calculation for Last Time Period P12
SELECT
PERIOD='P12'
,[Account],[BOA],[OrgUnit],[AverageBalance]='YAB',[AmountType]='I',[Years],[Currency],[Scenario],[Version],[IntExt]
,ROUND(
(
SUM(P01)*30 + SUM(P02)*31 + SUM(P03)*31 +
SUM(P04)*28 + SUM(P05)*31 + SUM(P06)*30 +
SUM(P07)*28 + SUM(P08)*31 + SUM(P09)*30 +
SUM(P10)*28 + SUM(P11)*31 + SUM(P12)*30
)
/(30 + 31 + 31 +
28 + 31 + 30 +
31 + 30 + 31 +
31 + 30 + 31
)
,2)
AS DATA
,[DataSource]= [DataSource]+' Avg'
FROM #TEMPAVG
GROUP BY [Account],[BOA],[OrgUnit],[Years],[Currency],[Scenario],[Version],[IntExt],[DATASOURCE]
sql average essbase
asked Mar 21 at 13:55
MikeMike
215
add a comment |
I'm trying to find a more efficient way to find the Yearly Average Balance for Accounts in a SQL table.
The number of days for each period are listed below, is there a more efficient way to code this? The #TEMPAVG table itself is a pivot of fields with Period containing the data. (Periods on Columns with #'s, Rows contain all other fields)
Yearly average Balance is calculated by Summarizing each time period, multiplying by # of days, and then dividing by total number of days.
I attempted to insert these Day values into a #TEMPDAYS table but couldn't figure out how to join the data correctly for multiplication of time periods.
TEMP DAYS Table
INSERT INTO #TEMPDAYS(Period,Days)
VALUES
('P01','30'),('P02','31'),('P03','31'),('P04','28'),('P05','31'),('P06','30'),('P07','31'),('P08','30'),('P09','31'),('P10','31'),('P11','30'),('P12','31')
,('Jan','31'),('Feb','28'),('Mar','31'),('Apr','30'),('May','31'),('Jun','30'),('Jul','31'),('Aug','31'),('Sep','30'),('Oct','31'),('Nov','30'),('Dec','31');
Days for Period Avgs
-- DAYS FOR EACH PERIOD ('P01','30'),('P02','31'),('P03','31'),('P04','28'),('P05','31'),('P06','30'),('P07','31'),('P08','30'),('P09','31'),('P10','31'),('P11','30'),('P12','31')
Yearly Average Balance Calculation for Last Time Period P12
SELECT
PERIOD='P12'
,[Account],[BOA],[OrgUnit],[AverageBalance]='YAB',[AmountType]='I',[Years],[Currency],[Scenario],[Version],[IntExt]
,ROUND(
(
SUM(P01)*30 + SUM(P02)*31 + SUM(P03)*31 +
SUM(P04)*28 + SUM(P05)*31 + SUM(P06)*30 +
SUM(P07)*28 + SUM(P08)*31 + SUM(P09)*30 +
SUM(P10)*28 + SUM(P11)*31 + SUM(P12)*30
)
/(30 + 31 + 31 +
28 + 31 + 30 +
31 + 30 + 31 +
31 + 30 + 31
)
,2)
AS DATA
,[DataSource]= [DataSource]+' Avg'
FROM #TEMPAVG
GROUP BY [Account],[BOA],[OrgUnit],[Years],[Currency],[Scenario],[Version],[IntExt],[DATASOURCE]
sql average essbase
asked Mar 21 at 13:55
MikeMike
215
add a comment |
I'm trying to find a more efficient way to find the Yearly Average Balance for Accounts in a SQL table.
The number of days for each period are listed below, is there a more efficient way to code this? The #TEMPAVG table itself is a pivot of fields with Period containing the data. (Periods on Columns with #'s, Rows contain all other fields)
Yearly average Balance is calculated by Summarizing each time period, multiplying by # of days, and then dividing by total number of days.
I attempted to insert these Day values into a #TEMPDAYS table but couldn't figure out how to join the data correctly for multiplication of time periods.
TEMP DAYS Table
INSERT INTO #TEMPDAYS(Period,Days)
VALUES
('P01','30'),('P02','31'),('P03','31'),('P04','28'),('P05','31'),('P06','30'),('P07','31'),('P08','30'),('P09','31'),('P10','31'),('P11','30'),('P12','31')
,('Jan','31'),('Feb','28'),('Mar','31'),('Apr','30'),('May','31'),('Jun','30'),('Jul','31'),('Aug','31'),('Sep','30'),('Oct','31'),('Nov','30'),('Dec','31');
Days for Period Avgs
-- DAYS FOR EACH PERIOD ('P01','30'),('P02','31'),('P03','31'),('P04','28'),('P05','31'),('P06','30'),('P07','31'),('P08','30'),('P09','31'),('P10','31'),('P11','30'),('P12','31')
Yearly Average Balance Calculation for Last Time Period P12
SELECT
PERIOD='P12'
,[Account],[BOA],[OrgUnit],[AverageBalance]='YAB',[AmountType]='I',[Years],[Currency],[Scenario],[Version],[IntExt]
,ROUND(
(
SUM(P01)*30 + SUM(P02)*31 + SUM(P03)*31 +
SUM(P04)*28 + SUM(P05)*31 + SUM(P06)*30 +
SUM(P07)*28 + SUM(P08)*31 + SUM(P09)*30 +
SUM(P10)*28 + SUM(P11)*31 + SUM(P12)*30
)
/(30 + 31 + 31 +
28 + 31 + 30 +
31 + 30 + 31 +
31 + 30 + 31
)
,2)
AS DATA
,[DataSource]= [DataSource]+' Avg'
FROM #TEMPAVG
GROUP BY [Account],[BOA],[OrgUnit],[Years],[Currency],[Scenario],[Version],[IntExt],[DATASOURCE]
sql average essbase
asked Mar 21 at 13:55
MikeMike
215
I'm trying to find a more efficient way to find the Yearly Average Balance for Accounts in a SQL table.
The number of days for each period are listed below, is there a more efficient way to code this? The #TEMPAVG table itself is a pivot of fields with Period containing the data. (Periods on Columns with #'s, Rows contain all other fields)
Yearly average Balance is calculated by Summarizing each time period, multiplying by # of days, and then dividing by total number of days.
I attempted to insert these Day values into a #TEMPDAYS table but couldn't figure out how to join the data correctly for multiplication of time periods.
TEMP DAYS Table
INSERT INTO #TEMPDAYS(Period,Days)
VALUES
('P01','30'),('P02','31'),('P03','31'),('P04','28'),('P05','31'),('P06','30'),('P07','31'),('P08','30'),('P09','31'),('P10','31'),('P11','30'),('P12','31')
,('Jan','31'),('Feb','28'),('Mar','31'),('Apr','30'),('May','31'),('Jun','30'),('Jul','31'),('Aug','31'),('Sep','30'),('Oct','31'),('Nov','30'),('Dec','31');
Days for Period Avgs
-- DAYS FOR EACH PERIOD ('P01','30'),('P02','31'),('P03','31'),('P04','28'),('P05','31'),('P06','30'),('P07','31'),('P08','30'),('P09','31'),('P10','31'),('P11','30'),('P12','31')
Yearly Average Balance Calculation for Last Time Period P12
SELECT
PERIOD='P12'
,[Account],[BOA],[OrgUnit],[AverageBalance]='YAB',[AmountType]='I',[Years],[Currency],[Scenario],[Version],[IntExt]
,ROUND(
(
SUM(P01)*30 + SUM(P02)*31 + SUM(P03)*31 +
SUM(P04)*28 + SUM(P05)*31 + SUM(P06)*30 +
SUM(P07)*28 + SUM(P08)*31 + SUM(P09)*30 +
SUM(P10)*28 + SUM(P11)*31 + SUM(P12)*30
)
/(30 + 31 + 31 +
28 + 31 + 30 +
31 + 30 + 31 +
31 + 30 + 31
)
,2)
AS DATA
,[DataSource]= [DataSource]+' Avg'
FROM #TEMPAVG
GROUP BY [Account],[BOA],[OrgUnit],[Years],[Currency],[Scenario],[Version],[IntExt],[DATASOURCE]
sql average essbase
sql average essbase
asked Mar 21 at 13:55
MikeMike
215
asked Mar 21 at 13:55
MikeMike
215
asked Mar 21 at 13:55
MikeMike
215
asked Mar 21 at 13:55
MikeMike
215
asked Mar 21 at 13:55
MikeMike
215
215
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55282050%2fsql-calculate-yearly-average-balance-for-forecast%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55282050%2fsql-calculate-yearly-average-balance-for-forecast%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
