时间戳

首页 > 解决方案 > 如何计算老化报告的月销售额

mysql - 如何计算老化报告的月销售额

问题描述

这相当简单，我有一个包含发票行的表格，包括发票编号、项目编号和数量。我想看看过去 18 个月，并计算出我们以 30 天为增量卖出了多少特定商品。

select invlines.i-no , @days_sold := DATEDIFF(CURDATE(), invlines.inv-date ) AS days_sold, SUM(IF(@days_sold BETWEEN 1 AND 30, invlines.qty , 0)) as Month1, SUM(IF(@days_sold BETWEEN 31 AND 60, invlines.qty , 0)) as Month2, SUM(IF(@days_sold BETWEEN 61 AND 90, invlines.qty , 0)) as Month3, SUM(IF(@days_sold BETWEEN 91 AND 120, invlines.qty , 0)) as Month4, SUM(IF(@days_sold BETWEEN 121 AND 150, invlines.qty , 0)) as Month5, SUM(IF(@days_sold BETWEEN 151 AND 180, invlines.qty , 0)) as Month6, SUM(IF(@days_sold BETWEEN 181 AND 210, invlines.qty , 0)) as Month7, SUM(IF(@days_sold BETWEEN 211 AND 240, invlines.qty , 0)) as Month8, SUM(IF(@days_sold BETWEEN 241 AND 270, invlines.qty , 0)) as Month9, SUM(IF(@days_sold BETWEEN 271 AND 300, invlines.qty , 0)) as Month10, SUM(IF(@days_sold BETWEEN 301 AND 330, invlines.qty , 0)) as Month11, SUM(IF(@days_sold BETWEEN 331 AND 360, invlines.qty , 0)) as Month12, SUM(IF(@days_sold BETWEEN 361 AND 390, invlines.qty , 0)) as Month13, SUM(IF(@days_sold BETWEEN 391 AND 420, invlines.qty , 0)) as Month14, SUM(IF(@days_sold BETWEEN 421 AND 450, invlines.qty , 0)) as Month15, SUM(IF(@days_sold BETWEEN 451 AND 480, invlines.qty , 0)) as Month16, SUM(IF(@days_sold BETWEEN 481 AND 510, invlines.qty i-no inv-date i- , 0)) as Month17, SUM(IF(@days_sold BETWEEN 511 AND 540, invlines.no``, 0)) as Month18 from invlines where invlines.="10 37148" and invlines.between (NOW() - INTERVAL 540 DAY) AND NOW() GROUP BY invlines.

我得到的是 Month18 列中的一个数量，但在任何其他 Month 列中都没有。我在另一份报告中使用了一个非常相似的解决方案，它工作正常，但无法弄清楚为什么这个不工作。

标签： mysql

解决方案

也许你在这样的事情

CREATE TABLE `invlines` (
`i-no` varchar(11) NOT NULL,
`inv-date` datetime NOT NULL,
`qty` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `invlines` (`i-no`, `inv-date`, `qty`) VALUES
('10 37148', '2019-07-07 08:00:00', '1'),
('10 37148', '2019-07-07 07:00:00', '1'),
('10 37148', '2019-06-01 08:00:00', '1'),
('10 37148', '2019-06-02 08:00:00', '1'),
('10  37148', '2019-05-02 08:00:00', '1'),
('10 37148', '2019-05-01 08:00:00', '1'),
('10 37148', '2019-04-07 08:00:00', '1'),
('10 37148', '2019-04-02 08:00:00', '1'),
('10 37148', '2019-03-02 08:00:00', '1'),
('10 37148', '2019-03-01 08:00:00', '1');

Select `i-no`,Sum(month1),SUM(month2),SUM(month3) From
(Select `i-no`,
  CASE WHEN ( DATEDIFF(Now(), `inv-date`) > 0 
         and DATEDIFF(Now(), `inv-date`) < 30) then SUM(qty) END  as month1,
  CASE WHEN ( DATEDIFF(Now(), `inv-date`) >= 30 
         and DATEDIFF(Now(), `inv-date`) < 60) then SUM(qty) END  as month2,
  CASE WHEN ( DATEDIFF(Now(), `inv-date`) >= 60 
         and DATEDIFF(Now(), `inv-date`) < 90) then SUM(qty) END  as month3
 From invlines
 Group by floor(datediff(Now(), `inv-date`) / 30)) a
 Group by `i-no`;

结果

i-no    Sum(month1)     SUM(month2)     SUM(month3)
10 37148    2                2              2

推荐阅读