Calculating Average Monthly Consumption
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
-1
I'm working on an self improvement inventory management project in Laravel 4 but can't figure out the best way to perform the average monthly consumption (AMC) calculation.
I have two tables i.e commodities table (id,item_name,price) and stockcard (id, item_id, qty_in, qty_out,transaction_date) from which i should derive the AMC calculation.
FORMULA = (sum of current month's qty_out + sum of previous two month's qty_out) / 3
Can anyone bail me out on how to go about it in plain PHP and mysql?
php mysql laravel laravel-4
edited Nov 16 '18 at 12:57
Madhur Bhaiya
19.6k62336
asked Nov 16 '18 at 12:54
O'ByronO'Byron
308
add a comment |
-1
I'm working on an self improvement inventory management project in Laravel 4 but can't figure out the best way to perform the average monthly consumption (AMC) calculation.
I have two tables i.e commodities table (id,item_name,price) and stockcard (id, item_id, qty_in, qty_out,transaction_date) from which i should derive the AMC calculation.
FORMULA = (sum of current month's qty_out + sum of previous two month's qty_out) / 3
Can anyone bail me out on how to go about it in plain PHP and mysql?
php mysql laravel laravel-4
edited Nov 16 '18 at 12:57
Madhur Bhaiya
19.6k62336
asked Nov 16 '18 at 12:54
O'ByronO'Byron
308
2
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 16 '18 at 12:58
add a comment |
-1
-1
-1
I'm working on an self improvement inventory management project in Laravel 4 but can't figure out the best way to perform the average monthly consumption (AMC) calculation.
I have two tables i.e commodities table (id,item_name,price) and stockcard (id, item_id, qty_in, qty_out,transaction_date) from which i should derive the AMC calculation.
FORMULA = (sum of current month's qty_out + sum of previous two month's qty_out) / 3
Can anyone bail me out on how to go about it in plain PHP and mysql?
php mysql laravel laravel-4
edited Nov 16 '18 at 12:57
Madhur Bhaiya
19.6k62336
asked Nov 16 '18 at 12:54
O'ByronO'Byron
308
I'm working on an self improvement inventory management project in Laravel 4 but can't figure out the best way to perform the average monthly consumption (AMC) calculation.
I have two tables i.e commodities table (id,item_name,price) and stockcard (id, item_id, qty_in, qty_out,transaction_date) from which i should derive the AMC calculation.
FORMULA = (sum of current month's qty_out + sum of previous two month's qty_out) / 3
Can anyone bail me out on how to go about it in plain PHP and mysql?
php mysql laravel laravel-4
php mysql laravel laravel-4
edited Nov 16 '18 at 12:57
Madhur Bhaiya
19.6k62336
asked Nov 16 '18 at 12:54
O'ByronO'Byron
308
edited Nov 16 '18 at 12:57
Madhur Bhaiya
19.6k62336
asked Nov 16 '18 at 12:54
O'ByronO'Byron
308
edited Nov 16 '18 at 12:57
Madhur Bhaiya
19.6k62336
edited Nov 16 '18 at 12:57
Madhur Bhaiya
19.6k62336
edited Nov 16 '18 at 12:57
Madhur Bhaiya
19.6k62336
19.6k62336
asked Nov 16 '18 at 12:54
O'ByronO'Byron
308
asked Nov 16 '18 at 12:54
O'ByronO'Byron
308
asked Nov 16 '18 at 12:54
O'ByronO'Byron
308
308
2
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 16 '18 at 12:58
add a comment |
2
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 16 '18 at 12:58
2
2
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 16 '18 at 12:58
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 16 '18 at 12:58
add a comment |
1 Answer
1
active
oldest
votes
2
You should be able to do this using conditional aggregation for example
drop table if exists t;
create table t(item int,qty_out int , dt date);
insert into t values
(1,1,'2018-09-01'),(1,1,'2018-10-01'),(1,1,'2018-11-01');
select item,
sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) thismm,
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end) last2mm,
(sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) +
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end)
) / 3 amc
from t
where year(dt)*12 + month(dt) >= (year(now()) * 12 + month(now()) -2)
group by item ;
+------+-----------+-------+--------+
| item | thismonth | last2 | amc |
+------+-----------+-------+--------+
| 1 | 1 | 2 | 1.0000 |
+------+-----------+-------+--------+
1 row in set (0.01 sec)
Note the conversion to month number to simplify where dates go over a year end.
Of course if you are looking for a 3 month rolling average then that would be a different question.
answered Nov 16 '18 at 13:22
P.SalmonP.Salmon
8,1102515
You are a life saver. This is exactly what i was looking for and works perfectly
– O'Byron
Nov 17 '18 at 9:06
@O'Byron If an answer has helped you. You should consider marking it as accepted answer (click on checkmark besides the answer to turn it green). Please see: How to accept an answer for closure. You get points for it as well. Thanks :)
– Madhur Bhaiya
Nov 18 '18 at 15:45
Hadn't seen that. thanks @MadhurBhaiya
– O'Byron
Nov 19 '18 at 14:14
add a comment |
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
});
}
});
draft saved
draft discarded
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%2f53338333%2fcalculating-average-monthly-consumption%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
You should be able to do this using conditional aggregation for example
drop table if exists t;
create table t(item int,qty_out int , dt date);
insert into t values
(1,1,'2018-09-01'),(1,1,'2018-10-01'),(1,1,'2018-11-01');
select item,
sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) thismm,
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end) last2mm,
(sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) +
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end)
) / 3 amc
from t
where year(dt)*12 + month(dt) >= (year(now()) * 12 + month(now()) -2)
group by item ;
+------+-----------+-------+--------+
| item | thismonth | last2 | amc |
+------+-----------+-------+--------+
| 1 | 1 | 2 | 1.0000 |
+------+-----------+-------+--------+
1 row in set (0.01 sec)
Note the conversion to month number to simplify where dates go over a year end.
Of course if you are looking for a 3 month rolling average then that would be a different question.
answered Nov 16 '18 at 13:22
P.SalmonP.Salmon
8,1102515
You are a life saver. This is exactly what i was looking for and works perfectly
– O'Byron
Nov 17 '18 at 9:06
@O'Byron If an answer has helped you. You should consider marking it as accepted answer (click on checkmark besides the answer to turn it green). Please see: How to accept an answer for closure. You get points for it as well. Thanks :)
– Madhur Bhaiya
Nov 18 '18 at 15:45
Hadn't seen that. thanks @MadhurBhaiya
– O'Byron
Nov 19 '18 at 14:14
add a comment |
2
You should be able to do this using conditional aggregation for example
drop table if exists t;
create table t(item int,qty_out int , dt date);
insert into t values
(1,1,'2018-09-01'),(1,1,'2018-10-01'),(1,1,'2018-11-01');
select item,
sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) thismm,
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end) last2mm,
(sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) +
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end)
) / 3 amc
from t
where year(dt)*12 + month(dt) >= (year(now()) * 12 + month(now()) -2)
group by item ;
+------+-----------+-------+--------+
| item | thismonth | last2 | amc |
+------+-----------+-------+--------+
| 1 | 1 | 2 | 1.0000 |
+------+-----------+-------+--------+
1 row in set (0.01 sec)
Note the conversion to month number to simplify where dates go over a year end.
Of course if you are looking for a 3 month rolling average then that would be a different question.
answered Nov 16 '18 at 13:22
P.SalmonP.Salmon
8,1102515
You are a life saver. This is exactly what i was looking for and works perfectly
– O'Byron
Nov 17 '18 at 9:06
@O'Byron If an answer has helped you. You should consider marking it as accepted answer (click on checkmark besides the answer to turn it green). Please see: How to accept an answer for closure. You get points for it as well. Thanks :)
– Madhur Bhaiya
Nov 18 '18 at 15:45
Hadn't seen that. thanks @MadhurBhaiya
– O'Byron
Nov 19 '18 at 14:14
add a comment |
2
2
2
You should be able to do this using conditional aggregation for example
drop table if exists t;
create table t(item int,qty_out int , dt date);
insert into t values
(1,1,'2018-09-01'),(1,1,'2018-10-01'),(1,1,'2018-11-01');
select item,
sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) thismm,
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end) last2mm,
(sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) +
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end)
) / 3 amc
from t
where year(dt)*12 + month(dt) >= (year(now()) * 12 + month(now()) -2)
group by item ;
+------+-----------+-------+--------+
| item | thismonth | last2 | amc |
+------+-----------+-------+--------+
| 1 | 1 | 2 | 1.0000 |
+------+-----------+-------+--------+
1 row in set (0.01 sec)
Note the conversion to month number to simplify where dates go over a year end.
Of course if you are looking for a 3 month rolling average then that would be a different question.
answered Nov 16 '18 at 13:22
P.SalmonP.Salmon
8,1102515
You should be able to do this using conditional aggregation for example
drop table if exists t;
create table t(item int,qty_out int , dt date);
insert into t values
(1,1,'2018-09-01'),(1,1,'2018-10-01'),(1,1,'2018-11-01');
select item,
sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) thismm,
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end) last2mm,
(sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) +
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end)
) / 3 amc
from t
where year(dt)*12 + month(dt) >= (year(now()) * 12 + month(now()) -2)
group by item ;
+------+-----------+-------+--------+
| item | thismonth | last2 | amc |
+------+-----------+-------+--------+
| 1 | 1 | 2 | 1.0000 |
+------+-----------+-------+--------+
1 row in set (0.01 sec)
Note the conversion to month number to simplify where dates go over a year end.
Of course if you are looking for a 3 month rolling average then that would be a different question.
answered Nov 16 '18 at 13:22
P.SalmonP.Salmon
8,1102515
answered Nov 16 '18 at 13:22
P.SalmonP.Salmon
8,1102515
answered Nov 16 '18 at 13:22
P.SalmonP.Salmon
8,1102515
answered Nov 16 '18 at 13:22
P.SalmonP.Salmon
8,1102515
8,1102515
You are a life saver. This is exactly what i was looking for and works perfectly
– O'Byron
Nov 17 '18 at 9:06
@O'Byron If an answer has helped you. You should consider marking it as accepted answer (click on checkmark besides the answer to turn it green). Please see: How to accept an answer for closure. You get points for it as well. Thanks :)
– Madhur Bhaiya
Nov 18 '18 at 15:45
Hadn't seen that. thanks @MadhurBhaiya
– O'Byron
Nov 19 '18 at 14:14
add a comment |
You are a life saver. This is exactly what i was looking for and works perfectly
– O'Byron
Nov 17 '18 at 9:06
@O'Byron If an answer has helped you. You should consider marking it as accepted answer (click on checkmark besides the answer to turn it green). Please see: How to accept an answer for closure. You get points for it as well. Thanks :)
– Madhur Bhaiya
Nov 18 '18 at 15:45
Hadn't seen that. thanks @MadhurBhaiya
– O'Byron
Nov 19 '18 at 14:14
You are a life saver. This is exactly what i was looking for and works perfectly
– O'Byron
Nov 17 '18 at 9:06
You are a life saver. This is exactly what i was looking for and works perfectly
– O'Byron
Nov 17 '18 at 9:06
@O'Byron If an answer has helped you. You should consider marking it as accepted answer (click on checkmark besides the answer to turn it green). Please see: How to accept an answer for closure. You get points for it as well. Thanks :)
– Madhur Bhaiya
Nov 18 '18 at 15:45
@O'Byron If an answer has helped you. You should consider marking it as accepted answer (click on checkmark besides the answer to turn it green). Please see: How to accept an answer for closure. You get points for it as well. Thanks :)
– Madhur Bhaiya
Nov 18 '18 at 15:45
Hadn't seen that. thanks @MadhurBhaiya
– O'Byron
Nov 19 '18 at 14:14
Hadn't seen that. thanks @MadhurBhaiya
– O'Byron
Nov 19 '18 at 14:14
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53338333%2fcalculating-average-monthly-consumption%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
2
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 16 '18 at 12:58