开发者

Sum over Timerange overlapping to next day over several days

I am desperately trying to get sum of values from range of time over several days, the problem is the range overlaps a day e.g from 15:00 to 10:00 but unfortunately I cant come up with another solution than a loop over all days but there sure is a more elegant way to do this all in one query. For a single day I have something like this

    SELECT 
    (Date(`Date`)) AS `Date`, SUM(`Val`), `Ld_id`
FROM
(SELECT 
    `Date`, SUM(`Val`) AS `Val`, `Ld_id`
FROM
    `tblVals`
INNER JOIN (SELECT 
    *
FROM
    `tblDate`
WHERE
    `Date` BETWEEN (SELECT CONCAT('2011-08-26 ', '14:31:00'))
     AND (SELECT CONCAT('2011-08-27 ', '10:01:00'))
ORDER BY `Date` ASC) AS `A` ON `tblVals`.`date_id` = `A`.`date_id`
WHERE
    `Ld_id` BETWEEN (SELECT 
        MIN(`Ld_id`)
    FROM
        `tblLr`
    WHERE
        `s_id` = '1') AND (SELECT 
        MAX(`Ld_id`)
    FROM
        `tblLr`
    WHERE
        `s_id` = '1')
开发者_开发知识库GROUP BY ((60/30)*HOUR(`Date`)+FLOOR(MINUTE(`Date`)/30)),`Ld_id`
ORDER BY `Ld_id` ASC ,`Date` ASC) AS `A`
Group by `Ld_id`

Many thanks in advance for any hint`


If you want 15:00 to 14:59 the next day:
- subtract 15 hours from all timestamps
- group by the date part of the timestamp

If you want 15:00 to 10:00 the next day:
- subtract 15 hours from all timestamps
- discard all records where the timepart is now greater than 19:00
- group by the date part of the timestamp


In MySQL, I think it's something like this...

SELECT
  DATE(DATE_SUB('Date', INTERVAL 15 HOUR)),
  SUM(VAL)
FROM
  tblVals
WHERE
  TIME(DATE_SUB('Date', INTERVAL 15 HOUR)) < '19:00'
GROUP BY
  DATE(DATE_SUB('Date', INTERVAL 15 HOUR))
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜