mysql - 查询以计算日期范围内已购买数据包的使用量

Question

我有一个查询返回 ticket_id、company_id、ticket create_time 用于解决该工单的时间。

SELECT `time_accounting`.`ticket_id`, 
       `ticket`.`customer_id`, 
       `ticket`.`title`, 
       `ticket`.`create_time`, 
       SUM(`time_accounting`.`time_unit`) AS `time_done`
FROM   `time_accounting` 
       INNER JOIN `ticket` 
               ON `ticket`.`id` = `time_accounting`.`ticket_id` 
       INNER JOIN `customer_company` 
               ON `customer_company`.`customer_id` = `ticket`.`customer_id` 
GROUP BY `time_accounting`.`ticket_id`

我还有一个表，用于存储 company_id 购买的电话支持分钟数以及它们何时过期。

表格:

[门票] ticket_id, company_id, time_done, create_time

[time_accounting_bought] packet_id, company_id, amount, starting_time, ending_time

预期结果

packet_id, company_id, time_used, time_remaining, time_exceeded

NULL | 1 | 20 | 0 | 20 # => company with no bought packets or no packets for the time of that ticket, exceeded 20h
1    | 2 | 10 | 5 | 0  # => with a packet and not used
2    | 3 | 55 | 5 | 5  # => with a packet and exceeded the available time, or a ticket is not included in the packet dates range

查询是否为同一家公司返回多行无关紧要。

问题是，在 time_accounting_bought 中，我可以拥有为同一家公司插入或包含自身的时间范围。例如。我购买了 1 年的支持(从 01/2015 开始)，我最终在 3 个月后完成它，然后我购买了 3 个月的支持(从 04/2015 开始)，所以日期可以相交。 查询必须足够智能，以了解在票证时间哪些事件数据包中有一些未使用的时间，并在将时间标记为超出时间之前使用它。

Answer 1

正要出发，所以如果您无法理解它，我明天会添加更多详细信息 - 我将一些代码发布给了另一个最近在“库存”系统上遇到类似问题的用户。

WITH ordertemp AS (
select ord.dealercode, 
      ord.partcode, 
      ord.orderqty, 
      ord.ordertype,
      -- use rownumber below if you may have duplicates
      RANK() OVER (ORDER BY case ord.ordertype when 'HotLine' then 1 when 'Urgent' then 2 else 3 end, ord.partcode, ord.dealercode) 'StockPriority',
        sto.stockqty 'InitialStock'
    from tblorder ord
    left outer join tblstock sto
    on ord.partcode = sto.partcode )
    SELECT
        Orders.dealercode,
        Orders.partcode,
        CASE    WHEN Backlog.PriorQty > Orders.InitialStock THEN 0
                ELSE Orders.InitialStock - Backlog.PriorQty END 'Stock',
        Orders.orderqty,
        CASE    WHEN Backlog.PriorQty + Orders.OrderQty < Orders.InitialStock THEN Orders.OrderQty 
                WHEN Backlog.PriorQty > Orders.InitialStock THEN 0
                ELSE Orders.InitialStock - Backlog.PriorQty END 'Allocated',
        Orders.ordertype
    FROM 
        ordertemp Orders
        INNER JOIN
        (
        SELECT A.stockpriority, A.partcode, ISNULL(SUM(B.orderqty),0) 'PriorQty'
        from ordertemp A
        LEFT OUTER JOIN ordertemp B
        on A.partcode = B.partcode
        and A.stockpriority > B.stockpriority 
        group by A.stockpriority, A.partcode ) Backlog
        ON Orders.stockpriority = Backlog.stockpriority
    ORDER BY Orders.StockPriority

当您有空时，请确认数据库，即 sqlserver。本质上，您正在查看一个系统，在该系统中，您根据每个客户的到期日期确定其支持时间的优先级，然后取消按该顺序使用的支持时间。

编辑:最有可能采用类似的方法，但我会再看一下您的示例。

明天见