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:



Attached Image below










share|improve this question
























  • 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
















-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:



Attached Image below










share|improve this question
























  • 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














-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:



Attached Image below










share|improve this question















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:



Attached Image below







sql sql-server sum lag






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 23 '18 at 5:51









marc_s

570k12811031251




570k12811031251










asked Nov 23 '18 at 4:54









Sunil 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


















  • 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












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





share|improve this answer





















    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


















    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





    share|improve this answer


























      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





      share|improve this answer
























        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





        share|improve this answer












        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






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 23 '18 at 5:09









        Squirrel

        11.7k22027




        11.7k22027






























            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.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • 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














            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





















































            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







            Popular posts from this blog

            What visual should I use to simply compare current year value vs last year in Power BI desktop

            How to ignore python UserWarning in pytest?

            Alexandru Averescu