MySQL summing value across date range
I have a table with each row containing a date and some arbitrary, numeric value. I need to sum this value for a specific but dynamic date interval.
SELECT VERSION();
5.0.51a-24+lenny5
CREATE TABLE IF NOT EXISTS `work_entries` (
`entry_id` int(10) unsigned NOT NULL auto_increment,
`employee_id` tinyint(3) unsigned NOT NULL,
`work_date` date NOT NULL,
`hour_co开发者_开发技巧unt` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`entry_id`)
);
INSERT INTO `work_entries` (`entry_id`, `employee_id`, `work_date`, `hour_count`) VALUES
(1, 1, '2011-04-25', 2),
(2, 1, '2011-04-26', 3),
(3, 1, '2011-04-27', 1),
(4, 2, '2011-04-25', 2),
(5, 2, '2011-04-27', 4),
(6, 1, '2011-05-08', 2),
(7, 2, '2011-05-06', 8),
(8, 2, '2011-05-08', 9),
(9, 2, '2011-05-09', 1),
(10, 1, '2011-05-29', 3),
(11, 1, '2011-05-30', 1),
(12, 2, '2011-05-30', 2),
(13, 1, '2011-06-02', 2),
(14, 1, '2011-06-04', 3),
(15, 1, '2011-06-14', 1),
(16, 2, '2011-06-14', 2),
(17, 2, '2011-06-17', 4),
(18, 1, '2011-06-18', 2),
(19, 2, '2011-06-19', 8),
(20, 2, '2011-06-26', 9),
(21, 2, '2011-07-01', 1),
(22, 1, '2011-07-03', 3),
(23, 1, '2011-07-03', 1),
(24, 2, '2011-07-16', 2);
The following query returns the correct output for the above data set and should illustrate what I'm trying to do, however, I need to generate the year and month values based on work_date
. The days (16, 15) never change. Specifically, this query will produce two rows, one for the specified interval and one for the rest, where I need one row for each period (16th of month N to 15th of month N+1 for all months with values).
SELECT
SUM(hour_count) AS res
FROM `work_entries`
GROUP BY work_date
BETWEEN '2011-04-16' AND '2011-05-15';
-- Outputs
res
----
44
32
-- Should give
res
----
32
14
28
2
An alternative example that works correctly but on the wrong interval (days 01-31):
SELECT
SUM(hour_count) AS res
FROM `work_entries`
GROUP BY MONTH(work_date);
-- Outputs
res
---
12
26
31
7
Additionally, if there is a way to output the from- and to- dates at the same time I'd like that as well, but that's not very important.
lovely:
SELECT
SUM(hour_count)
FROM `work_entries`
GROUP BY
YEAR(work_date - INTERVAL 15 DAY),
MONTH(work_date - INTERVAL 15 DAY);
SELECT
YEAR(grouping_date) AS year,
MONTH(grouping_date) AS month,
SUM(hour_count) AS hour_count_total
FROM (
SELECT
work_date,
hour_count,
work_date - INTERVAL 15 DAY AS grouping_date
FROM work_entries
) x
GROUP BY
YEAR(grouping_date),
MONTH(grouping_date)
SELECT
YEAR(work_date) AS year,
MONTH(work_date) AS month,
case
when right(work_date,2) between '01' and '15' then 1
when right(work_date,2) between '16' and '31' then 2
end
as partofmonth,
SUM(hour_count) AS totalper15period
FROM work_entries
GROUP BY year, month, partofmonth
You could use another table where you store all period ranges.
create table `periods` (
`id` int(11) not null auto_increment,
`start` date default null,
`end` date default null,
primary key (`id`)
) engine=myisam auto_increment=4 default charset=latin1;
insert into `periods`(`id`,`start`,`end`) values (1,'2011-04-16','2011-05-15');
insert into `periods`(`id`,`start`,`end`) values (2,'2011-05-16','2011-06-15');
insert into `periods`(`id`,`start`,`end`) values (3,'2011-06-16','2011-07-15');
and make a join with it
select
p.start,p.end,
sum(w.hour_count) as total
from periods as p
inner join work_entries as w
on w.work_date between p.start and p.end
group by p.start
+------------+------------+-------+
| start | end | total |
+------------+------------+-------+
| 2011-04-16 | 2011-05-15 | 32 |
| 2011-05-16 | 2011-06-15 | 6 |
+------------+------------+-------+
2 rows in set (0.00 sec)
You can then put a where clause when you need it.
Somewhat hackish, but
SELECT
SUM(hour_count),
STR_TO_DATE(
CONCAT(
EXTRACT(YEAR_MONTH FROM work_date),
'16'
),
'%Y%m%d'
) AS startperiod,
DATE_ADD(
STR_TO_DATE(
CONCAT(
EXTRACT(YEAR_MONTH FROM work_date),
'15'
),
'%Y%m%d'
),
INTERVAL 1 MONTH
) AS endperiod
FROM
work_entries
GROUP BY work_date BETWEEN startperiod
AND endperiod;
Thinking about it, the above wouldn't work at all. It'd only ever return two rows, based on if the work_date field falls inside the generated start/end periods, or outside.
Basically your desired query requires a group interval that's more of a "fiscal month" than a calendar month.
精彩评论