SaxonNZ
asked on
SQL - Displaying month to date sales
Hello,
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.
Example:
07/02/2007 - Daily: 7000 : Monthly 7000
07/03/2007 - Daily: 7000 : Monthly: 14000
07/04/2007 - Daily: 10,000: Monthly: 24000
Etc
Thanks for your time.
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.
Example:
07/02/2007 - Daily: 7000 : Monthly 7000
07/03/2007 - Daily: 7000 : Monthly: 14000
07/04/2007 - Daily: 10,000: Monthly: 24000
Etc
Thanks for your time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.