Can't use SQL lag sum
-3
My query is like this
SELECT
[day], [time],AvaliableTimes,
CASE
WHEN AvaliableTimes > 0
THEN SUM(AvaliableTimes) OVER (ORDER BY [day], [time], AvaliableTimes)
ELSE 0
END AS SumValue
FROM
[AvailableTimes]
WHERE
[day] = 1 AND BranchAreaId = 1
ORDER BY
[day], [time], AvaliableTimes
I want to start sum from 0 if value is null or 0.
Results:
sql
sql-server sum lag edited Nov 23 '18 at 5:51
marc_s
575k12811091256
asked Nov 23 '18 at 4:54
Sunil PaudelSunil Paudel
83
add a comment |
-3
My query is like this
SELECT
[day], [time],AvaliableTimes,
CASE
WHEN AvaliableTimes > 0
THEN SUM(AvaliableTimes) OVER (ORDER BY [day], [time], AvaliableTimes)
ELSE 0
END AS SumValue
FROM
[AvailableTimes]
WHERE
[day] = 1 AND BranchAreaId = 1
ORDER BY
[day], [time], AvaliableTimes
I want to start sum from 0 if value is null or 0.
Results:
sql
sql-server sum lag edited Nov 23 '18 at 5:51
marc_s
575k12811091256
asked Nov 23 '18 at 4:54
Sunil PaudelSunil Paudel
83
What is the problem? Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 23 '18 at 5:00
Post the tables and data as text READ THIS
– Juan Carlos Oropeza
Nov 23 '18 at 5:04
add a comment |
-3
-3
-3
My query is like this
SELECT
[day], [time],AvaliableTimes,
CASE
WHEN AvaliableTimes > 0
THEN SUM(AvaliableTimes) OVER (ORDER BY [day], [time], AvaliableTimes)
ELSE 0
END AS SumValue
FROM
[AvailableTimes]
WHERE
[day] = 1 AND BranchAreaId = 1
ORDER BY
[day], [time], AvaliableTimes
I want to start sum from 0 if value is null or 0.
Results:
sql
sql-server sum lag edited Nov 23 '18 at 5:51
marc_s
575k12811091256
asked Nov 23 '18 at 4:54
Sunil PaudelSunil Paudel
83
My query is like this
SELECT
[day], [time],AvaliableTimes,
CASE
WHEN AvaliableTimes > 0
THEN SUM(AvaliableTimes) OVER (ORDER BY [day], [time], AvaliableTimes)
ELSE 0
END AS SumValue
FROM
[AvailableTimes]
WHERE
[day] = 1 AND BranchAreaId = 1
ORDER BY
[day], [time], AvaliableTimes
I want to start sum from 0 if value is null or 0.
Results:
sql
sql-server sum lag sql
sql-server sum lag edited Nov 23 '18 at 5:51
marc_s
575k12811091256
asked Nov 23 '18 at 4:54
Sunil PaudelSunil Paudel
83
edited Nov 23 '18 at 5:51
marc_s
575k12811091256
asked Nov 23 '18 at 4:54
Sunil PaudelSunil Paudel
83
edited Nov 23 '18 at 5:51
marc_s
575k12811091256
edited Nov 23 '18 at 5:51
marc_s
575k12811091256
edited Nov 23 '18 at 5:51
marc_s
575k12811091256
575k12811091256
asked Nov 23 '18 at 4:54
Sunil PaudelSunil Paudel
83
asked Nov 23 '18 at 4:54
Sunil PaudelSunil Paudel
83
asked Nov 23 '18 at 4:54
Sunil PaudelSunil Paudel
83
83
What is the problem? Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 23 '18 at 5:00
Post the tables and data as text READ THIS
– Juan Carlos Oropeza
Nov 23 '18 at 5:04
add a comment |
What is the problem? Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 23 '18 at 5:00
Post the tables and data as text READ THIS
– Juan Carlos Oropeza
Nov 23 '18 at 5:04
What is the problem? Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 23 '18 at 5:00
What is the problem? Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 23 '18 at 5:00
Post the tables and data as text READ THIS
– Juan Carlos Oropeza
Nov 23 '18 at 5:04
Post the tables and data as text READ THIS
– Juan Carlos Oropeza
Nov 23 '18 at 5:04
add a comment |
1 Answer
1
active
oldest
votes
0
you can use a recursive CTE to do it. Perform the cumulative sum in the rcte and if AvailableTimes = 0, reset it
; with
cte as
(
select *, rn = row_number() over (order by time)
from yourtable
),
rcte as
(
select *, sumvalues = AvailableTimes
from cte
where rn = 1
union all
select c.*, sumvalues = case when c.AvailableTimes <> 0
then r.sumvalues + c.AvailableTimes
else c.AvailableTimes
end
from cte c
inner join rcte r on c.rn = r.rn + 1
)
select day, time, AvailableTimes, sumvalues
from rcte
order by time
answered Nov 23 '18 at 5:09
SquirrelSquirrel
11.8k22127
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%2f53440831%2fcant-use-sql-lag-sum%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
0
you can use a recursive CTE to do it. Perform the cumulative sum in the rcte and if AvailableTimes = 0, reset it
; with
cte as
(
select *, rn = row_number() over (order by time)
from yourtable
),
rcte as
(
select *, sumvalues = AvailableTimes
from cte
where rn = 1
union all
select c.*, sumvalues = case when c.AvailableTimes <> 0
then r.sumvalues + c.AvailableTimes
else c.AvailableTimes
end
from cte c
inner join rcte r on c.rn = r.rn + 1
)
select day, time, AvailableTimes, sumvalues
from rcte
order by time
answered Nov 23 '18 at 5:09
SquirrelSquirrel
11.8k22127
add a comment |
0
you can use a recursive CTE to do it. Perform the cumulative sum in the rcte and if AvailableTimes = 0, reset it
; with
cte as
(
select *, rn = row_number() over (order by time)
from yourtable
),
rcte as
(
select *, sumvalues = AvailableTimes
from cte
where rn = 1
union all
select c.*, sumvalues = case when c.AvailableTimes <> 0
then r.sumvalues + c.AvailableTimes
else c.AvailableTimes
end
from cte c
inner join rcte r on c.rn = r.rn + 1
)
select day, time, AvailableTimes, sumvalues
from rcte
order by time
answered Nov 23 '18 at 5:09
SquirrelSquirrel
11.8k22127
add a comment |
0
0
0
you can use a recursive CTE to do it. Perform the cumulative sum in the rcte and if AvailableTimes = 0, reset it
; with
cte as
(
select *, rn = row_number() over (order by time)
from yourtable
),
rcte as
(
select *, sumvalues = AvailableTimes
from cte
where rn = 1
union all
select c.*, sumvalues = case when c.AvailableTimes <> 0
then r.sumvalues + c.AvailableTimes
else c.AvailableTimes
end
from cte c
inner join rcte r on c.rn = r.rn + 1
)
select day, time, AvailableTimes, sumvalues
from rcte
order by time
answered Nov 23 '18 at 5:09
SquirrelSquirrel
11.8k22127
you can use a recursive CTE to do it. Perform the cumulative sum in the rcte and if AvailableTimes = 0, reset it
; with
cte as
(
select *, rn = row_number() over (order by time)
from yourtable
),
rcte as
(
select *, sumvalues = AvailableTimes
from cte
where rn = 1
union all
select c.*, sumvalues = case when c.AvailableTimes <> 0
then r.sumvalues + c.AvailableTimes
else c.AvailableTimes
end
from cte c
inner join rcte r on c.rn = r.rn + 1
)
select day, time, AvailableTimes, sumvalues
from rcte
order by time
answered Nov 23 '18 at 5:09
SquirrelSquirrel
11.8k22127
answered Nov 23 '18 at 5:09
SquirrelSquirrel
11.8k22127
answered Nov 23 '18 at 5:09
SquirrelSquirrel
11.8k22127
answered Nov 23 '18 at 5:09
SquirrelSquirrel
11.8k22127
11.8k22127
add a comment |
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%2f53440831%2fcant-use-sql-lag-sum%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
What is the problem? Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example
– Juan Carlos Oropeza
Nov 23 '18 at 5:00
Post the tables and data as text READ THIS
– Juan Carlos Oropeza
Nov 23 '18 at 5:04