?
Solved

Daily cumulative sales for 3 salesmen

Posted on 2011-05-10
12
Medium Priority
?
533 Views
Last Modified: 2012-08-14
I have a simple table where 3 salesmen each record their cumulative sales revenues, but they do not update their records on a daily basis, leaving gaps between entries.  As example, all have recorded 2011-04-30 month end data but May data is irregular for each person:
-- ----------------------------
-- Table structure for `ytdsales`
-- ----------------------------
DROP TABLE IF EXISTS `ytdsales`;
CREATE TABLE `ytdsales` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `Name` char(10) NOT NULL,
  `Date` date NOT NULL,
  `cumSales` mediumint(9) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

-- ----------------------------
-- Records of ytdsales
-- ----------------------------
INSERT INTO `ytdsales` VALUES ('1', 'A', '2011-04-30', '100');
INSERT INTO `ytdsales` VALUES ('2', 'A', '2011-05-06', '120');
INSERT INTO `ytdsales` VALUES ('3', 'A', '2011-05-10', '125');
INSERT INTO `ytdsales` VALUES ('4', 'A', '2011-05-20', '150');
INSERT INTO `ytdsales` VALUES ('5', 'B', '2011-04-30', '120');
INSERT INTO `ytdsales` VALUES ('6', 'B', '2011-05-08', '130');
INSERT INTO `ytdsales` VALUES ('7', 'B', '2011-05-17', '155');
INSERT INTO `ytdsales` VALUES ('8', 'C', '2011-04-30', '70');
INSERT INTO `ytdsales` VALUES ('9', 'C', '2011-05-09', '80');
INSERT INTO `ytdsales` VALUES ('10', 'C', '2011-05-14', '95');

Open in new window

I want to query this table and get as a result the totals for A+B+C for each distinct date for which their are entries, so the resulting output would be (on the above data):

2011-04-30  290  (ie 100+120+70)
2011-05-06  310 (ie120+120+70)
2011-05-08  320 (ie 120+130+70)
2011-05-09  330 (ie 120+130+80)
etc

Can anyone show me how to do this?
0
Comment
Question by:ShanghaiD
  • 8
  • 3
12 Comments
 

Author Comment

by:ShanghaiD
ID: 35735112
If it makes it any easier, I'm also happy to have the output on a day by day basis with amounts interpolated, like this:

2011-04-30  290
2011-05-01  290
2011-05-02  290
2011-05-03  290
2011-05-04  290
2011-05-05  290
2011-05-06  310
2011-05-07  310
2011-05-08  320
2011-05-09  330
etc
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35735125
Select name, date, sum(cumsales) from ytdsales group by name, date
0
 

Author Comment

by:ShanghaiD
ID: 35735146
No, that gives:
A      2011-04-30      100
A      2011-05-06      120
A      2011-05-10      125
A      2011-05-20      150
B      2011-04-30      120
B      2011-05-08      130
B      2011-05-17      155
C      2011-04-30      70
C      2011-05-09      80
C      2011-05-14      95

I want A+B+C totals (one total figure only) for each Date.
0
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.

 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35735155
Oh. Then just drop name from the output
Select date, sum(cumsales) from ytdsales group by date
0
 

Author Comment

by:ShanghaiD
ID: 35735168
No, that gives:
2011-04-30      290
2011-05-06      120
2011-05-08      130
2011-05-09      80
2011-05-10      125
2011-05-14      95
2011-05-17      155
2011-05-20      150
which is still not correct.
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35735213
I think I understand now. I'm leaving the dailysales in for now just for error checking. You can drop that column if you like.
Select date, sum(cumsales) as dailysales, (select sum(cumsales) from ytdsales b where b.date <= a.date) as cumulativesales
from ytdsales a
group by date
0
 

Author Comment

by:ShanghaiD
ID: 35735281
No, that gives:
2011-04-30      290      290
2011-05-06      120      410
2011-05-08      130      540
2011-05-09      80      620
2011-05-10      125      745
2011-05-14      95      840
2011-05-17      155      995
2011-05-20      150      1145
I want to get:
2011-04-30  290
2011-05-06  310
2011-05-08  320
2011-05-09  330
0
 

Author Comment

by:ShanghaiD
ID: 35735296
aarontomosky: I "think" your sytax will work IF the sales entries were sales for that date (only) and not cumulative ytd sales on that date (as they currently are).
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35735475
There is one entry for '2011-05-06' and cumSales for 2011-05-06 is 120. The cumulative should be sum of all 2011-04-30 cumSales + 2011-05-06 cumSales. Hence it should be ((100+120+70)+120) = 410. right?
0
 

Author Comment

by:ShanghaiD
ID: 35735651
Not quite because the second 120 is the updated cumulative total for A (which was previously 100) so the correct total should be ((100+120+70)+(120-100))=310 and not 410.
0
 

Accepted Solution

by:
ShanghaiD earned 0 total points
ID: 35736019
I've (eventually) found the solution myself (although there may be a more
elegant solution to be found):

set @n:=0;
select date, (select @n:=@n+sum(n))+previousA+previousB+previousC
YTDcumulative from
(
select date,
ifnull((select cumsales from ytdsales b where name='A' and b.date =
a.date)-(select max(cumsales) from ytdsales b where b.date < a.date and
name='A'),0) +
ifnull((select cumsales from ytdsales b where name='B' and b.date =
a.date)-(select max(cumsales) from ytdsales b where b.date < a.date and
name='B'),0) +
ifnull((select cumsales from ytdsales b where name='C' and b.date =
a.date)-(select max(cumsales) from ytdsales b where b.date < a.date and
name='C'),0) AS N,
ifnull((select max(cumsales) from ytdsales b where name='A' and b.date <=
LAST_DAY(DATE_SUB(a.Date,INTERVAL 1 month))),0) AS previousA,
ifnull((select max(cumsales) from ytdsales b where name='B' and b.date <=
LAST_DAY(DATE_SUB(a.Date,INTERVAL 1 month))),0) AS previousB,
ifnull((select max(cumsales) from ytdsales b where name='C' and b.date <=
LAST_DAY(DATE_SUB(a.Date,INTERVAL 1 month))),0) AS previousC
from ytdsales a
) t2
group by date;

which gives:

2011-04-30  0
2011-05-06  310
2011-05-08  320
2011-05-09  330
2011-05-10  335
2011-05-14  350
2011-05-17  375
2011-05-20  400

and as my ultimate goal is to get cumulative Daily sales for the month
(rather than cumulative Year to Date sales), I've then simplified the above
to:

set @n:=0;
select date, (select @n:=@n+sum(n)) thismonthcumulative from
(
select date,
ifnull((select cumsales from ytdsales b where name='A' and b.date =
a.date)-(select max(cumsales) from ytdsales b where b.date < a.date and
name='A'),0) +
ifnull((select cumsales from ytdsales b where name='B' and b.date =
a.date)-(select max(cumsales) from ytdsales b where b.date < a.date and
name='B'),0) +
ifnull((select cumsales from ytdsales b where name='C' and b.date =
a.date)-(select max(cumsales) from ytdsales b where b.date < a.date and
name='C'),0) AS N
from ytdsales a
) t2
group by date;

which gives:

2011-04-30  0
2011-05-06  20
2011-05-08  30
2011-05-09  40
2011-05-10  45
2011-05-14  60
2011-05-17  85
2011-05-20  110

which is what I wanted.
0
 

Author Closing Comment

by:ShanghaiD
ID: 35767632
Thanks for earlier help from theGhost_k8 which helped me to find my solution to this problem.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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