• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7371
  • Last Modified:

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.

0
SaxonNZ
Asked:
SaxonNZ
2 Solutions
 
derekkrommCommented:
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
0
 
prast1007Commented:
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.
0
 
SaxonNZAuthor Commented:
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.

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now