I currently have a database that tracks sales for a team of salesman. I have a query that will pull the each salesman and their associated totals but I am looking to have this broken down by week and then if possible show this in aggregate over week.
The current Query I am using is:
SELECT ROUND(SUM(n.newBalance), 2) AS newB, u.username
FROM (
SELECT
j.leadid AS custid,
WEEK(j.convertdate) AS weeks,
j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
FROM jobbooktbl j
WHERE j.convertdate BETWEEN '2017-01-02' AND '2017-07-31'
AND j.status IN (4,6,7,8,11)
) n
JOIN assignmentstbl a USING (custid)
JOIN usertbl u USING (userid)
GROUP BY a.userid
This returns the following (Grouped by Salesman):
+-----------+-------------+
| salesman | Sales Total |
+-----------+-------------+
| salesman1 | 1850 |
| salesman2 | 1170 |
+-----------+-------------+
What I am hoping to accomplish is this to be broken out by week and return the following (Grouped by Week then By Salesman):
+-----------+--------+-------------+
| salesman | weekNo | sales total |
+-----------+--------+-------------+
| salesman1 | 1 | 0 |
| salesman2 | 1 | 0 |
| salesman1 | 2 | 100 |
| salesman2 | 2 | 100 |
| salesman1 | 3 | 1300 |
| salesman2 | 3 | 0 |
| salesman1 | 4 | 450 |
| salesman2 | 4 | 1070 |
| salesman1 | 5 | 0 |
| salesman2 | 5 | 0 |
+-----------+--------+-------------+
And if possible an aggregate as well like this (Grouped by Week by Salesman with Running Total/Aggregate):
+-----------+--------+-------------+
| salesman | weekNo | sales total |
+-----------+--------+-------------+
| salesman1 | 1 | 0 |
| salesman2 | 1 | 0 |
| salesman1 | 2 | 100 |
| salesman2 | 2 | 100 |
| salesman1 | 3 | 1400 |
| salesman2 | 3 | 100 |
| salesman1 | 4 | 1850 |
| salesman2 | 4 | 1170 |
| salesman1 | 5 | 1850 |
| salesman2 | 5 | 1170 |
+-----------+--------+-------------+
Here is the schema so far:
CREATE TABLE weekstbl
(`weekNo` int, `weekStart` date)
;
INSERT INTO weekstbl
(`weekNo`, `weekStart`)
VALUES
(1, '2017-01-02'),
(2, '2017-01-09'),
(3, '2017-01-16'),
(4, '2017-01-23'),
(5, '2017-01-30')
;
CREATE TABLE jobbooktbl
(`leadid` int, `convertdate` date, `price` int, `status` int)
;
INSERT INTO jobbooktbl
(`leadid`, `convertdate`, `price`, `status`)
VALUES
(1, '2017-01-16', 500, 4),
(2, '2017-01-24', 620, 6),
(3, '2017-01-17', 800, 7),
(4, '2017-01-26', 900, 11),
(5, '2017-01-10', 200, 4)
;
CREATE TABLE assignmentstbl
(`custid` int, `userid` int)
;
INSERT INTO assignmentstbl
(`custid`, `userid`)
VALUES
(1, 1),
(2, 2),
(3, 1),
(4, 2),
(4, 1),
(5, 1),
(5, 2)
;
CREATE TABLE usertbl
(`userid` int, `username` varchar(25))
;
INSERT INTO usertbl
(`userid`,`username`)
VALUES
(1,'salesman1'),
(2,'salesman2')
;
Here is an SQLFIDDLE with all of the information above.
I have tried LEFT JOINing the two tables but to no avail. I am really a beginner at SQL so this is a bit out of my wheel house. I am also creating the weekstbl just because I don't know how else to return 0's for the weeks that do not hold any values for a salesman, this may not be necessary.
TRIALS:
Trial 1
SELECT ROUND(SUM(n.newBalance), 2) AS newB, weeks, u.username
FROM (
SELECT
j.leadid AS custid,
w.weekno AS weeks,
j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
FROM jobbooktbl j
LEFT JOIN weekstbl w on w.weekNo=WEEK(j.convertdate)
AND j.convertdate BETWEEN '2017-01-02' AND '2017-07-31'
AND j.status IN (4,6,7,8,11)
) n
JOIN assignmentstbl a USING (custid)
JOIN usertbl u USING (userid)
GROUP BY weeks, a.userid
This returned the following result set which is not including the 0's for week numbers 1, 3 (for salesman2) or 5:
+-----------+--------+-------------+
| salesman | weekNo | sales total |
+-----------+--------+-------------+
| salesman1 | 2 | 100 |
| salesman2 | 2 | 100 |
| salesman1 | 3 | 1300 |
| salesman1 | 4 | 450 |
| salesman2 | 4 | 1070 |
+-----------+--------+-------------+
Trial 2
SELECT ROUND(SUM(n.newBalance), 2) AS newB, weeks, u.username
FROM (
SELECT
j.leadid AS custid,
w.weekno AS weeks,
j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
FROM jobbooktbl j
join weekstbl w on j.convertdate between weekstart and date(weekstart + interval 6 day )
WHERE j.convertdate BETWEEN '2017-01-02' AND '2017-07-31'
AND j.status IN (4,6,7,8,11)
) n
JOIN assignmentstbl a USING (custid)
JOIN usertbl u USING (userid)
GROUP BY weeks, a.userid
This returned the following result set (not including 0's for weeks 1, 3 (for salesman2), or 5):
+-----------+--------+-------------+
| salesman | weekNo | sales total |
+-----------+--------+-------------+
| salesman1 | 2 | 100 |
| salesman2 | 2 | 100 |
| salesman1 | 3 | 1300 |
| salesman1 | 4 | 450 |
| salesman2 | 4 | 1070 |
+-----------+--------+-------------+
Trial 3:
SELECT * FROM (
SELECT ROUND(SUM(n.newBalance), 2) AS newB, u.username,weeks
FROM (
SELECT
j.leadid AS custid,
WEEK(j.convertdate) AS weeks,
j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
FROM jobbooktbl j
WHERE j.convertdate BETWEEN '2017-01-02' AND '2017-07-31'
AND j.status IN (4,6,7,8,11)
) n
JOIN assignmentstbl a USING (custid)
JOIN usertbl u USING (userid)
GROUP BY a.userid,n.weeks
ORDER BY newB DESC
)INNERTABLE
LEFT JOIN weekstbl CL ON CL.weekNo=INNERTABLE.weeks
This returned the following result set (not including 0's for weeks 1, 3 (for salesman2), or 5):
+-----------+--------+-------------+
| salesman | weekNo | sales total |
+-----------+--------+-------------+
| salesman1 | 2 | 100 |
| salesman2 | 2 | 100 |
| salesman1 | 3 | 1300 |
| salesman1 | 4 | 450 |
| salesman2 | 4 | 1070 |
+-----------+--------+-------------+
Trial 4:
Getting a little closer with this one
SELECT
w.weekNo, COALESCE(ROUND(SUM(n.newBalance), 2),0) AS newB, n.username
FROM
weekstbl w
LEFT JOIN (
SELECT
j.leadid AS custid,
j.convertdate AS sold,
u.username AS username,
j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
FROM
jobbooktbl j
JOIN assignmentstbl a ON j.leadid = a.custid
JOIN usertbl u ON u.userid = a.userid
) n
ON
w.weekNo = WEEK(n.sold)
GROUP BY
n.username, w.weekNo
ORDER BY
w.weekNo
This returned the following result set (returned 0's for weeks 1 and 5 but did not recognize the salesman and did not return a 0 for salesman2 on week 3):
+-----------+--------+-------------+
| salesman | weekNo | sales total |
+-----------+--------+-------------+
| (null) | 1 | 0 |
| salesman1 | 2 | 100 |
| salesman1 | 2 | 100 |
| salesman1 | 3 | 1300 |
| salesman1 | 4 | 450 |
| salesman2 | 4 | 1070 |
| (null) | 5 | 0 |
+-----------+--------+-------------+