MYSQL Join weekly table with Sales and Group seller and weeks
I currently have a database that tracks sales for the sales team. I have a query that will pull each salesperson and its associated totals, but I want this to be broken down by week and then, if possible, show it cumulatively over a week.
The current query I'm using:
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 seller):
+-----------+-------------+
| salesman | Sales Total |
+-----------+-------------+
| salesman1 | 1850 |
| salesman2 | 1170 |
+-----------+-------------+
What I hope to accomplish is to break down by week and return the following (grouped by week then by salesperson):
+-----------+--------+-------------+
| 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, the aggregate also likes (grouped by week by seller with Total / Aggregate launch):
+-----------+--------+-------------+
| 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's the diagram 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 SQLFIDDLE with all the information above.
I tried LEFT JOINing on two tables, to no avail. I'm really new to SQL, so this is a bit out of my wheelhouse. I also create weekstbl just because I don't know how else to return 0 for weeks that do not contain any values for the seller, it may not be necessary.
TESTS:
Challenge 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 returns the following result set, which does not include 0 for week numbers 1, 3 (for salesperson 2), or 5:
+-----------+--------+-------------+
| salesman | weekNo | sales total |
+-----------+--------+-------------+
| salesman1 | 2 | 100 |
| salesman2 | 2 | 100 |
| salesman1 | 3 | 1300 |
| salesman1 | 4 | 450 |
| salesman2 | 4 | 1070 |
+-----------+--------+-------------+
Challenge 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 set of results (not including 0 for weeks 1, 3 (for salesperson 2), or 5):
+-----------+--------+-------------+
| salesman | weekNo | sales total |
+-----------+--------+-------------+
| salesman1 | 2 | 100 |
| salesman2 | 2 | 100 |
| salesman1 | 3 | 1300 |
| salesman1 | 4 | 450 |
| salesman2 | 4 | 1070 |
+-----------+--------+-------------+
Test 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 set of results (not including 0 for weeks 1, 3 (for salesperson 2), or 5):
+-----------+--------+-------------+
| salesman | weekNo | sales total |
+-----------+--------+-------------+
| salesman1 | 2 | 100 |
| salesman2 | 2 | 100 |
| salesman1 | 3 | 1300 |
| salesman1 | 4 | 450 |
| salesman2 | 4 | 1070 |
+-----------+--------+-------------+
Test 4:
Getting a little closer to this
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 returns the following result set (returns 0 for weeks 1 and 5, but did not recognize the seller and did not return 0 for seller 2 in the 3rd week):
+-----------+--------+-------------+
| 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 |
+-----------+--------+-------------+
source to share
I added one connection to weekstbl. you can check below request. Hope this helps.
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)
-- WHERE a.userid=5
GROUP BY weeks, a.userid
ORDER BY newB DESC
Here's an updated answer.
select userid, username, week, year, fvalue
from ( select sub3.*,
if(@previous = userid, @value1 := @value1 + value, @value1 := value ) fvalue,
@previous := userid
from (select distinct ut.userid, ut.username,
week(date) as week,year(date) as year ,coalesce(sub2.newB,0) as value
from ( SELECT (CURDATE() - INTERVAL c.number DAY) AS date
FROM (SELECT singles + tens + hundreds number FROM
( SELECT 0 singles
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) singles JOIN
(SELECT 0 tens
UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30
UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60
UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
) tens JOIN
(SELECT 0 hundreds
UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600
UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
) hundreds
ORDER BY number DESC) c ) abc
cross join usertbl ut
left join (
SELECT ROUND(SUM(n.newBalance), 2) AS newB, weeks, years,a.userid, u.username
FROM (
SELECT
j.leadid AS custid,
w.weekno AS weeks,
year(weekstart) as years,
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 on u.userid = a.userid
GROUP BY weeks, a.userid
) sub2 on sub2.userid = ut.userid
and weeks = week(date)
and years = year(date)
where date between '2017-01-02' AND '2017-07-31'
order by userid,year(date), week(date) ) sub3 ) sub4
order by year, week, userid
Note. I would suggest creating a dim_time table that stores all relevant date information.
Explanation:
1) Sub Query Name: abc This will generate week and year based on your input. After that, the cross is attached to your result using usertbl. That's a lot of lines that you want in your end result. Now we add values as per your requirement.
2) Sub Query Name: sub2
This gives the desired result but does not show 0 values.
3) Now 1 left join 2 This gives you the result as (grouped by week, then by seller). Reorder the order to get the expected result.
This will become your sub3. This is necessary because we have to sum the previous value to the next value.
4) Create varialbe @previous and @ value1 Since we have sorted our result based on userid. Now the first line has come and it checks the status below. Then it goes to another part because it doesn't match, keep the userid to @previous. It emits the second line, now it will add the previous value to the next line because the condition is met. Similary this will add your result before a new user arrives.
Statement:
MySQL (@previous = userid, @value1 := @value1 + value ,@value1 := value)
if @previous = userid
then @value1 := @value1 + value
else @value1 := value;
Hope this helps.
source to share
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 CALENDAR CL ON CL.WEEK=INNERTABLE.weeks
You can try the query above.
Now create one calendar table using the Calendar table .
Using the above query and joining with a calendar table you can achieve what you want. This means 0 for records that have no record for this week.
source to share