CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Eslam Sameh Ahmed
Eslam Sameh Ahmed

Reputation: 4022

Database to track visits vs dynamic targets

I'm designing a new database to track accounts achieved visits vs monthly targets. The final report shall be requested by start/end dates and one account to show the months inbetween with monthly target and sum of visits. The complication started when I knew the number of accounts is more than 10 thousands and the targets should be changed monthly or not for the only changed accounts targets(i.e each target will have start and end date. if no end date then the target is always valid). At this point I lost and I need help

For simplicity I will assume I have table with dates periods and simplest situation as follow

accounts
+----+---------+ 
|id  | name    |
+----+---------+ 
| 1  | account1|
| 2  | account2|
+----+---------+

targets
+---+------------+------------+-----------+----------------+
|id | account_id | start_date | end_date  | monthly_target |
+---+------------+------------+-----------+----------------+
|1  | 1          | 1-1-2016   | 31-1-2016 | 5              |
|2  | 1          | 1-2-2016   | 31-5-2016 | 4              |
|3  | 1          | 1-7-2016   | null      | 7              |
|4  | 2          | 1-1-2016   | null      | 10             |
+---+------------+------------+-----------+----------------+

visits
+---+-----------+------------+
|id | date      | account_id |
+----------------------------+
|1  | 15-1-2016 | 1          |
|2  | 20-1-2016 | 1          |
|3  | 10-5-2016 | 1          |
|3  | 20-5-2016 | 1          |
|4  | 20-5-2016 | 2          |
+---+-----------+------------+

calendar (Optional)
----------+----------+
|start    | end      |
----------+----------+
|1-1-2016 | 31-1-2016|
|1-2-2016 | 29-2-2016|
|1-3-2016 | 31-3-2016|
|1-4-2016 | 30-4-2016|
|1-5-2016 | 31-5-2016|
|1-6-2016 | 30-6-2016|
|1-7-2016 | 31-7-2016|
|1-8-2016 | 31-7-2016|
+---------+----------+

Expected report for account1 coverage from 1-4-2016 to 31-7-2016

+---------+-----------+--------+----+
|start    | end       | target | sum|
+---------+-----------+--------+----+
|1-4-2016 | 30-4-2016 | 4      | 0  |
|1-5-2016 | 31-5-2016 | 4      | 2  |
|1-6-2016 | 30-6-2016 | 0      | 0  |
|1-7-2016 | 31-7-2016 | 7      | 0  |
+---------+-----------+--------+----+

I can accept changing my initial design if it causes problems but assuming the design of targets table is the most practical design for system admin.

I need help in SQL needed to generate the final report.

Upvotes: 0

Views: 59

Answers (2)

Rick James
Rick James

Reputation: 142366

SELECT  c.start,
        c.end,
        t.monthly_target AS target, 
        (
        SELECT  COUNT(*)
            FROM  visits
            WHERE  `date` BETWEEN c.start AND c.end 
              AND  account_id = ?   -- Specify '1'
        ) AS `sum`            -- Correlated subquery for counting visits
    FROM  Calendar AS c
    JOIN  targets AS t  ON c.start_date >= t.start_date
                     AND ( t.end_date IS NULL
                        OR c.start_date < t.end_date )
    WHERE  c.start >= ?       -- Specify date range
      AND  c.end   <= ?

Upvotes: 1

Drew
Drew

Reputation: 24960

I modified the range of dates in targets to have an explicit end date even if that means end of year. This way, avoiding the null, the sql could range ok. It also uses the ISO 8601 Standard for dates. And it is implemented in a Stored Proc that takes 3 parameters: account_id, start and end date.

Alias v, the derived table, prevents double counts versus a flat out LEFT JOIN against the visits table. For instance, that 2 would be an errant 7 without that strategy. So it used the LAST_DAY() function.

Schema:

create table accounts
(   id int not null,
    name varchar(100) not null
);
insert accounts values
(1,'account1'),
(2,'account2');

-- drop table targets;
create table targets
(   id int not null,
    account_id int not null,
    start_date date not null,
    end_date date not null,
    monthly_target int not null
);
-- truncate targets;
insert targets values
(1,1,'2016-01-01','2016-01-31',5),
(2,1,'2016-02-01','2016-05-31',4),
(3,1,'2016-07-01','2016-12-31',7),
(4,2,'2016-01-01','2016-12-31',10);

create table visits
(   id int not null,
    date date not null,
    account_id int not null
);
-- truncate visits;
insert visits values
(1,'2016-01-15',1),
(2,'2016-01-20',1),
(3,'2016-05-10',1),
(4,'2016-05-20',1),
(5,'2016-05-20',2);


create table calendar
(   start date not null,
    end date not null
);
insert calendar values
('2016-01-01','2016-01-31'),
('2016-02-01','2016-02-29'),
('2016-03-01','2016-03-31'),
('2016-04-01','2016-04-30'),
('2016-05-01','2016-05-31'),
('2016-06-01','2016-06-30'),
('2016-07-01','2016-07-31'),
('2016-08-01','2016-08-31'),
('2016-09-01','2016-09-30'),
('2016-10-01','2016-10-31'),
('2016-11-01','2016-11-30'),
('2016-12-01','2016-12-31');

Stored Proc:

DROP PROCEDURE IF EXISTS uspGetRangeReport007;
DELIMITER $$
CREATE PROCEDURE uspGetRangeReport007
(   p_account_id INT,
    p_start DATE,
    p_end DATE
)
BEGIN
    SELECT c.start,c.end,
    IFNULL(t.monthly_target,0) as target,
    -- IFNULL(sum(v.id),0) as visits
    IFNULL(v.theCount,0) as visits
    FROM calendar c
    LEFT JOIN targets t
    ON account_id=p_account_id 
    AND c.start BETWEEN t.start_date AND t.end_date
    AND c.end BETWEEN t.start_date AND t.end_date
    LEFT JOIN 
    (   SELECT LAST_DAY(date) as lastDayOfMonth,
        count(id) as theCount
        FROM VISITS
        WHERE account_id=p_account_id
        GROUP BY LAST_DAY(date)
    ) v
    ON v.lastDayOfMonth BETWEEN c.start AND c.end
    WHERE c.start BETWEEN p_start AND p_end
    AND c.end BETWEEN p_start AND p_end
    GROUP BY c.start,c.end,t.monthly_target
    ORDER BY c.start;
END;$$
DELIMITER ;

Test:

call uspGetRangeReport007(1,'2016-04-01','2016-07-31'); 
+------------+------------+--------+--------+
| start      | end        | target | visits |
+------------+------------+--------+--------+
| 2016-04-01 | 2016-04-30 |      4 |      0 |
| 2016-05-01 | 2016-05-31 |      4 |      2 |
| 2016-06-01 | 2016-06-30 |      0 |      0 |
| 2016-07-01 | 2016-07-31 |      7 |      0 |
+------------+------------+--------+--------+

Upvotes: 2

Related Questions