SQL - Displaying month to date sales

Posted on 2007-07-29
Last Modified: 2013-12-24

Quick Question:

I wrote an SQL statement that selects the sales value for each day throughout the month chosen.

Statement as follows:

select sphwhseno, extend (spddate, month to day) spmonth1,
sum(spdvalue) sale
from salesbyperiod
where sphwhseno = "NPL"
and spddate >= "01072007"
and spddate <= "31072007"
group by 1,2
order by 2

What i would like to know is how i can modify this to also sum the days values up into a month total, but spread out over the month. So it would add up the daily sales values over the month and display the to date value.


07/02/2007 - Daily: 7000 : Monthly 7000
07/03/2007 - Daily: 7000 : Monthly: 14000
07/04/2007 - Daily: 10,000: Monthly: 24000


Thanks for your time.

Question by:SaxonNZ
    LVL 15

    Accepted Solution

    Not sure if this syntax is valid in your DBMS, but in SQL Server, the following would give you the results you're looking for.

    select sphwhseno, extend (spddate, month to day) spmonth1,
    sum(spdvalue) daily,
    (select sum(spdvalue) from salesbyperiod where sphwhseno = "NPL" and spdate >= "01072007" and spdate <= s.spdate) monthly
    from salesbyperiod s
    where sphwhseno = "NPL"
    and spddate >= "01072007"
    and spddate <= "31072007"
    group by 1,2
    order by 2
    LVL 4

    Assisted Solution

    I'm not sure if the PL/SQL also works on Informix, but it works on it, you can make a store procedure with the above query being the cursor. And while you read the value of the daily transaction, you can sum the amount for the monthly as you need.

    Author Comment

    Derekk -

    I gave that a try. It grabs the daily values as before - Perfect.

    The monthly total for each day is unfortunately showing the entire months worth instead of just showing the months total up to that day.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now