通过单据流水统计条码总店龄

Question

按单据流水统计条码在门店的总店龄（总店龄是指条码在所有门店里一共有多少天）。
注意事项：
1、从总部发货门店接收入库后开始统计，一直到离开门店之间的天数。
2、从本店调到其他店也要计算。
3、从门店调回总部总部接收入库后开始不计算店龄（库存组织在总部的天数不计算店龄）。
4、如在店里被销售则销售日开始停止计算店龄。

结合以上例子我们可以得到：
1、A店从2021-05-20开始计算店龄（总部发货门店入库），从2021-09-14结束计算店龄（这一天退货回总部），共计2021-09-14减2021-05-20=117天。
2、B店从2021-09-14开始计算店龄（总部发货门店入库），从2021-11-01结束计算店龄（这一天销售了），共计2021-11-01减2021-09-14=48天。
3、B店从2022-03-01开始计算店龄（退货回门店），从2022-03-13结束计算店龄（这一天销售了），共计2022-03-13减2022-03-01=12天。
4、那么条码：980128498的总店龄=117+48+12=177天。通过查询条码980128498的总店龄可以得到177天。
如何使用Oracle SQL 统计总店龄。

Answer 1

SELECT SUM(store_age) AS total_store_age
FROM (
    SELECT 
        CASE 
            WHEN NVL(LEAD(operation_time) OVER (PARTITION BY barcode_number ORDER BY operation_time), 
                     TO_DATE('2023-11-19', 'YYYY-MM-DD')) < operation_time 
            THEN 0 
            ELSE LEAD(operation_time) OVER (PARTITION BY barcode_number ORDER BY operation_time) - operation_time 
        END AS store_age
    FROM (
        SELECT 
            operation_time, 
            barcode_number,
            CASE
                WHEN document_type LIKE '%入库单' AND origin_stock_organization IS NOT NULL THEN 'End'
                WHEN document_type LIKE '%出库单' AND stock_organization = origin_stock_organization THEN 'Start'
                ELSE NULL
            END AS mark
        FROM your_table
        WHERE barcode_number = '980128498'
    )
    WHERE mark = 'Start'
)
WHERE store_age > 0;