Geek Answers Handbook

MySQL query to show total per week

I have a table where the closing date will be updated after closing ... until this time the field is null. I need to show a close trend, grouped every 7 days from the start. So, for example, if during the first week 10 lines and 2 were closed, then the total for this week should be 8. I was able to create a query that works to show how many of them were closed every week, but I'm afraid to find a way accounting for the results of previous weeks.

Table

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `test_data`;
CREATE TABLE `test_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `close_date` date DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;
INSERT INTO `test_data` VALUES ('1', '2015-02-02', 'one');
INSERT INTO `test_data` VALUES ('2', '2015-02-02', 'one');
INSERT INTO `test_data` VALUES ('3', '2015-02-09', 'one');
INSERT INTO `test_data` VALUES ('4', '2015-02-09', 'one');
INSERT INTO `test_data` VALUES ('5', '2015-02-09', 'one');
INSERT INTO `test_data` VALUES ('6', '2015-02-16', 'one');
INSERT INTO `test_data` VALUES ('7', '2015-02-16', 'one');
INSERT INTO `test_data` VALUES ('8', '2015-02-16', 'one');
INSERT INTO `test_data` VALUES ('9', '2015-02-16', 'one');
INSERT INTO `test_data` VALUES ('10', '2015-02-16', 'one');
INSERT INTO `test_data` VALUES ('11', '2015-02-02', 'two');
INSERT INTO `test_data` VALUES ('12', '2015-02-02', 'two');
INSERT INTO `test_data` VALUES ('13', '2015-02-09', 'two');
INSERT INTO `test_data` VALUES ('14', '2015-02-09', 'two');
INSERT INTO `test_data` VALUES ('15', '2015-02-09', 'two');
INSERT INTO `test_data` VALUES ('16', '2015-02-16', 'two');
INSERT INTO `test_data` VALUES ('17', '2015-02-16', 'two');
INSERT INTO `test_data` VALUES ('18', '2015-02-16', 'two');
INSERT INTO `test_data` VALUES ('19', '2015-02-16', 'two');
INSERT INTO `test_data` VALUES ('20', '2015-02-16', 'two');

Request so far

select
'2015-02-02' + INTERVAL (DATEDIFF(test_data.close_date, '2015-02-02') DIV 7) WEEK as start_week,
(SELECT COUNT(*) FROM test_data WHERE location = 'one') - COUNT(a.id) AS one,
(SELECT COUNT(*) FROM test_data WHERE location = 'two') - COUNT(b.id) AS two
from test_data
left join test_data as a on a.id = test_data.id and a.location = 'one'
left join test_data as b on b.id = test_data.id and b.location = 'two'
where test_data.close_date >= '2015-02-02'
group by DATEDIFF(test_data.close_date,'2015-02-02') DIV 7

Output

start_week one two
2015-02-02 8   8
2015-02-09 7   7
2015-02-16 5   5

The result that I am trying to achieve

start_week one two
2015-02-02 8   8
2015-02-09 5   5
2015-02-16 0   0

Pushing in the right direction is appreciated as I continue to work on it.

EDIT: A more detailed explanation of the expected results.

"", . "" 10 , 1- 2 ( 8 1), 2 3 , 5 2 5 .

, .

start_week one 
2015-02-02  8  <-- Total 10 - 2 closed in this week = 8
2015-02-09  5  <-- Total 10 - (3 closed in this week + the 2 in week 1) = 5
2015-02-16  0  <-- Total 10 - (5 closed in this week + the previous weeks) = 0
+4
1

a1 a2 - , . , . mysql . .

    SELECT
sums.start_week,sums.*,
 sums.tot1-sums.cur1-sums.back1 a1,sums.tot2-sums.cur1-sums.back2 a2
  FROM
( /* Derived table cf counts */
SELECT DATEDIFF(test_data.close_date,'2015-02-02') DIV 7 AS sd,
'2015-02-02' + INTERVAL (DATEDIFF(test_data.close_date, '2015-02-02') DIV 7) WEEK as start_week,
(select COUNT(*) from test_data d1 where d1.location='one') tot1,
(select COUNT(*) from test_data d2 where d2.location='two') tot2,
count(CASE WHEN test_data.location='one' then 1 else null end )  cur1,
count(CASE WHEN test_data.location='two' then 1 else null end
    )    cur2,

(select count(*) from test_data d1 where d1.location='one' AND d1.close_date<
    ('2015-02-02' + INTERVAL (DATEDIFF(test_data.close_date, '2015-02-02') DIV 7) WEEK) ) as back1,
(select count(*) from test_data d2 where d2.location='two' AND d2.close_date<
    ('2015-02-02' + INTERVAL (DATEDIFF(test_data.close_date, '2015-02-02') DIV 7) WEEK) ) as back2

from test_data

where test_data.close_date >= '2015-02-02'
group by DATEDIFF(test_data.close_date,'2015-02-02') DIV 7 ,
'2015-02-02' + INTERVAL (DATEDIFF(test_data.close_date, '2015-02-02') DIV 7) WEEK 
) sums
+1

More articles:


All Articles