3

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      |
 +-----------+--------+-------------+
Craig Howell
  • 1,114
  • 2
  • 12
  • 28

2 Answers2

1

I have added one join with weekstbl. you can check below query. I 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 is 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 to create dim_time table which stores all related information of date.

Explanation:

1) Sub Query Name: abc This will generate week and year based on your input. After that cross join your result with usertbl. That many number of rows you want in your final output. Now we have add values as per your requiremnent.

2) Sub Query Name: sub2
This produces your required result but it is not showing 0 values.

3) Now 1 left join 2 This gives you result as (Grouped by Week then By Salesman). Change order by clause just to get expected output.
This becomes your sub3. This is necessory because we have to sum previous value to next value.

4) Create varialbe @previous and @value1 As we have sorted our result based on userid. Now first row come and it checks below condition. Then it goes to else part because it not matching, store userid in @previous. It excutes second row now it will add previous value to next row because condition is satisfied. Similary it will add your result till new userid comes.

Condtion: 

MySQL (@previous = userid,  @value1 := @value1 + value ,@value1 := value)

if @previous = userid 
then @value1 := @value1 + value 
else @value1 := value;

I hope this will help.

Fahad Anjum
  • 1,246
  • 1
  • 10
  • 19
  • This is returning `[['salesman1',100],['salesman2',100],['salesman1',1300],['salesman2',1070],['salesman1',450]]` This is missing 0's. – Craig Howell Aug 01 '17 at 14:14
  • Means it should show all the weeks value.For your selected input how many 0 should come. – Fahad Anjum Aug 01 '17 at 14:40
  • I have updated the question to include a better result set showing 0's – Craig Howell Aug 01 '17 at 14:41
  • initial inspection looks good, may be too much information but I will take a look now – Craig Howell Aug 01 '17 at 18:55
  • this is exactly what I have been looking for. Thank you for all the time you spent on this. I am a beginner to SQL would you mind unpacking this for me and explaining what you did. If I understand this I can hopefully not ask as many questions on here ;) – Craig Howell Aug 01 '17 at 19:20
  • 1
    @CraigHowell I added explanation please go through it. You can upvote and tick my answer as correct. – Fahad Anjum Aug 02 '17 at 05:15
0
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 above query.

And now make one calendar table using Calendar Table.

Using above query and making join with calendar table, you can achieve what you want. Means 0 value for records for which no entry for that week.

halfer
  • 19,824
  • 17
  • 99
  • 186
Sagar Gangwal
  • 7,544
  • 3
  • 24
  • 38
  • The JOIN is the part that is giving me trouble and I need help with. This query returns the values GROUPed BY weeks but does not return the 0 values which is the part that I am missing. Thanks Sagar. – Craig Howell Aug 01 '17 at 13:39
  • @CraigHowell I know that you just need to do is make one calendar table and make whole this query to join with it. – Sagar Gangwal Aug 01 '17 at 13:40
  • 1
    https://stackoverflow.com/questions/20677472/sql-find-missing-date-ranges see this question to create date range. – Shuddh Aug 01 '17 at 13:41
  • @SagarGangwal here is an sqlfiddle - http://sqlfiddle.com/#!9/02db0/15 showing your code but I am still showing a result with no 0's, what am I missing? – Craig Howell Aug 01 '17 at 13:46
  • This is returning `[['salesman1',100],['salesman2',100],['salesman1',1300],['salesman2',1070],['salesman1',450]]` This is missing 0's. – Craig Howell Aug 01 '17 at 14:13