-- 解决一个月多次入库，跨月多次入库，研发成本取数重复统计问题
WITH year_ratio as (
SELECT
1 n,
cast(1.0 as float) ratio

UNION ALL

SELECT
year_ratio.n + 1 n,
ratio + power(0.7, year_ratio.n) ratio
FROM year_ratio
WHERE year_ratio.n < 10
),
raw_records as (
    SELECT
    rd08_m.dDate rd08_ddate,
    rd08_m.cCode rd08_code,
    rd08_d.AutoID,
    rd08_d.cInvCode,
    rd08_d.cBatch,
    rd08_d.cItemCode,
    rd08_d.iQuantity,
    rd08_d.iPrice,
    rd08_d.cDefine30,
    rd08_d.iQuantity / (SUM(rd08_d.iQuantity) OVER(partition by DATEADD(month, DATEDIFF(month, 0, rd08_m.dDate), 0), rd08_d.cItemCode)) ratio, -- 项目当月入库分摊比例
    row_number() OVER(partition by rd08_d.cInvCode, rd08_d.cBatch ORDER BY rd08_m.dDate asc) batch_r,  -- 批次排序
    row_number() OVER(partition by rd08_d.cItemCode ORDER BY rd08_m.dDate asc) prj_r  -- 项目编码排序
    FROM rdrecords08_U8_009 rd08_d with(nolock)

    LEFT JOIN rdrecord08_U8_009 rd08_m with(nolock)
    ON rd08_d.ID = rd08_m.ID

    WHERE cRdCode = '0110'
    AND rd08_m.dnverifytime IS NOT NULL
), mid_records as (

SELECT
raw_records.*,
last_prj_record.rd08_ddate last_prj_rd08_ddate,
SUM(prj_fee.total_fee) OVER(partition by DATEADD(month, DATEDIFF(month, 0, raw_records.rd08_ddate), 0), raw_records.cItemCode) prj_total_fee,
SUM(prj_fee.raw_material_cost) OVER(partition by DATEADD(month, DATEDIFF(month, 0, raw_records.rd08_ddate), 0), raw_records.cItemCode)  * raw_records.ratio raw_material_cost, --按当月同项目入库数量分摊后的原料费用
SUM(prj_fee.depreciation_fee) OVER(partition by DATEADD(month, DATEDIFF(month, 0, raw_records.rd08_ddate), 0), raw_records.cItemCode)  * raw_records.ratio depreciation_fee, --按当月同项目入库数量分摊后的折旧费用
SUM(prj_fee.rent_utility_cost) OVER(partition by DATEADD(month, DATEDIFF(month, 0, raw_records.rd08_ddate), 0), raw_records.cItemCode)  * raw_records.ratio rent_utility_cost, --按当月同项目入库数量分摊后的租金水电费用
SUM(prj_fee.labor_cost) OVER(partition by DATEADD(month, DATEDIFF(month, 0, raw_records.rd08_ddate), 0), raw_records.cItemCode)  * raw_records.ratio labor_cost, --按当月同项目入库数量分摊后的人工费用
SUM(prj_fee.other_fee) OVER(partition by DATEADD(month, DATEDIFF(month, 0, raw_records.rd08_ddate), 0), raw_records.cItemCode)  * raw_records.ratio other_fee, --按当月同项目入库数量分摊后的其他费用

SUM(prj_fee.total_fee) OVER(partition by DATEADD(month, DATEDIFF(month, 0, raw_records.rd08_ddate), 0), raw_records.cItemCode)  * raw_records.ratio total_fee --按当月同项目入库数量分摊后的研发费用
FROM raw_records

-- 查询上次同项目入库时间
OUTER APPLY (
    SELECT
    rd08_ddate
    FROM raw_records lst_record
    WHERE lst_record.cItemCode = raw_records.cItemCode
    AND lst_record.prj_r = raw_records.prj_r - 1
)last_prj_record

OUTER APPLY (
    SELECT
    sum(case when item_type.type = '原料费用' then ledger.md else 0 end) raw_material_cost,
    sum(case when item_type.type = '人工费' then ledger.md else 0 end) labor_cost,
    sum(case when item_type.type = '租金水电费' then ledger.md else 0 end) rent_utility_cost,
    sum(case when item_type.type = '折旧费用' then ledger.md else 0 end) depreciation_fee,
    sum(case when item_type.type = '其他费用' then ledger.md else 0 end) other_fee,
    sum(ledger.md) total_fee
    FROM AuxiliaryLedger_U8_009 ledger with(nolock)

    OUTER APPLY (
        SELECT TOP 1
        c.type
        FROM params_prj_item_cls c
        WHERE c.code = ledger.ccode
    ) item_type

    WHERE ledger.citem_class = '00'
    AND ledger.citem_id = raw_records.cItemCode
    AND ledger.cdigest not like '研发产品入库%'
    AND ledger.dbill_date < DATEADD(month, DATEDIFF(month, 0, raw_records.rd08_ddate) + 1, 0) -- 取当月入库前的凭证
    AND (
        last_prj_record.rd08_ddate IS NULL
        OR ledger.dbill_date >= DATEADD(month, DATEDIFF(month, 0, last_prj_record.rd08_ddate) + 1, 0)
    )
)prj_fee

), records as (

SELECT
mid_records.rd08_ddate,
mid_records.rd08_code,
mid_records.cDefine30,
mid_records.AutoID,
mid_records.cInvCode,
mid_records.cBatch,
mid_records.cItemCode,
mid_records.ratio,
-- mid_records.iQuantity,
-- mid_records.iPrice,
count(*) OVER(partition by mid_records.cInvCode, mid_records.cBatch) rd08_count,
sum(mid_records.iQuantity) OVER(partition by mid_records.cInvCode, mid_records.cBatch) iQuantity,
sum(mid_records.iPrice) OVER(partition by mid_records.cInvCode, mid_records.cBatch) iPrice,

sum(mid_records.raw_material_cost) OVER(partition by mid_records.cInvCode, mid_records.cBatch) raw_material_cost,
sum(mid_records.labor_cost) OVER(partition by mid_records.cInvCode, mid_records.cBatch) labor_cost,
sum(mid_records.rent_utility_cost) OVER(partition by mid_records.cInvCode, mid_records.cBatch) rent_utility_cost,
sum(mid_records.other_fee) OVER(partition by mid_records.cInvCode, mid_records.cBatch) other_fee,
sum(mid_records.depreciation_fee) OVER(partition by mid_records.cInvCode, mid_records.cBatch) depreciation_fee,

sum(mid_records.total_fee) OVER(partition by mid_records.cInvCode, mid_records.cBatch) total_fee
FROM mid_records
WHERE mid_records.batch_r = 1
)

SELECT
records.rd08_ddate 最早研发入库时间,
records.rd08_code 最早入库单号,
records.rd08_count 入库次数,
records.AutoID 入库ID,
records.cInvCode "研发入库货号-原料",
SUBSTRING(records.cInvCode, 3, len(records.cInvCode)) 研发入库货号,
inv_cls.prd_line 产品线,
records.cBatch 入库批次,
records.iQuantity 批次总入库数量,
records.iPrice 批次总入库成本,
records.cDefine30 备货类型,
records.ratio ratio,

records.raw_material_cost 原料成本,
records.labor_cost 人工成本,
records.rent_utility_cost 租金水电成本,
records.depreciation_fee 折旧成本,
records.other_fee 其他成本,
records.total_fee 研发总成本,

inq_summary.last_year_inq_count 近一年询价次数,
inq_summary.so_inq_rate 产品成单率,
inq_summary.last_year_so_inq_rate 近一年成单率,
inq_summary_after_instock.total_inq_count 研发入库后总询价次数,
inq_summary_after_instock.last_year_inq_count 研发入库后近一年询价次数,
inq_summary_after_instock.so_inq_rate 研发入库后产品成单率,
inq_summary_after_instock.last_year_so_inq_rate 研发入库后近一年成单率,

so_summary.last_year_so_count 近一年销售次数,
so_summary_after_instock.so_count 研发入库后销售次数,
so_summary_after_instock.so_sum 研发入库后销售含税合计,
so_summary_after_instock.last_year_so_count 研发入库后近一年销售次数,
so_summary_after_instock.last_year_so_sum 研发入库后近一年销售含税合计,
batch_so_summary.so_count 批次销售次数,
batch_so_summary.rd11_iquantity "批次出库数量(mg)",
batch_so_summary.rd32_iSum 批次出库金额,
batch_so_summary.cur_month_rd32_isum 批次入库当月出库金额,
batch_so_summary.rd32_min_ddate 批次最早销售出库日期,

prd_rd32_summary_after_instock.total_count 研发入库后产品出库次数,
prd_rd32_summary_after_instock.rd11_cost 研发入库后产品出库成本,
prd_rd32_summary_after_instock.total_sum 研发入库后产品出库金额,
prd_rd32_summary_after_instock.total_mg "研发入库后产品出库数量(mg)",
prd_rd32_summary_after_instock.last_year_count 研发入库后近一年产品出库次数,
prd_rd32_summary_after_instock.last_year_sum 研发入库后近一年产品出库金额,
prd_rd32_summary_after_instock.last_year_mg_sum "研发入库后近一年产品出库数量(mg)",

po_summary.last_year_po_avg_unit_price 上一年采购平均单价,
po_summary.po_avg_unit_price 采购平均单价,

case when inq_summary_after_instock.total_inq_count > 0 or so_summary_after_instock.so_count>0 then 1 else 0 end 是否触碰,
case when so_summary_after_instock.so_count > 0 then 1 else 0 end 是否销售,
case when batch_so_summary.rd32_iSum > 0 then 1 else 0 end 是否出库,
case when batch_so_summary.cur_month_rd32_isum > 0 then 1 else 0 end 当月是否出库,
history_so.cFree1 历史常卖规格,
dbo.normalize_mg(history_so.cFree1) "历史常卖规格(mg)",
history_so.iTaxUnitPrice 历史常卖规格含税单价,
history_so.so_count_1year 入库前一年销售次数,
history_so.so_count_1year * history_so.iTaxUnitPrice 预计一年销售金额,
processed.predict_sale_year * history_so.so_count_1year 预计销售次数,
processed.predict_sale_year * history_so.so_count_1year * history_so.iTaxUnitPrice "预计销售金额(旧)",
coalesce(year_ratio.ratio, processed.predict_sale_year) * history_so.so_count_1year * history_so.iTaxUnitPrice 预计销售金额,

records.iPrice / records.iQuantity * batch_so_summary.rd11_iquantity 批次累计销售成本,
records.total_fee / records.iQuantity * batch_so_summary.rd11_iquantity 批次累计销售研发成本,
current_stock.iQuantity 剩余库存

FROM records

-- 产品线
OUTER apply (
    SELECT TOP 1
    case inv.cInvCCode
    when '01021005' then '药物线'
    when '01011006' then '非药线'
    when '01031001' then '中间体'
    when '03031001' then '中间体'
    when '0901' then '原料'
    else '其他' END prd_line
    FROM Inventory_U8_009 inv with(nolock)
    WHERE inv.cInvCode = records.cInvCode
)inv_cls

-- 询价统计
OUTER apply (
    SELECT
    count(*) inq_count,
    case count(*) when 0 then null else cast(count(inq_so.id) as float) / count(*) end so_inq_rate,
    sum(case when inq.inq_created_at >= DATEADD(year, -1, getdate()) then 1 else 0 end) last_year_inq_count,
    case sum(case when inq.inq_created_at >= DATEADD(year, -1, getdate()) then 1 else 0 end) when 0 then null
    else cast(sum(case when inq_so.id is not null AND inq.inq_created_at >= DATEADD(year, -1, getdate()) then 1 else 0 end) as float) / sum(case when inq.inq_created_at >= DATEADD(year, -1, getdate()) then 1 else 0 end) end last_year_so_inq_rate

    FROM prod_inq_result inq with(nolock)
    OUTER APPLY (
        SELECT TOP 1
        inq_sync.id,
        inq_sync.update_time

        FROM cato_platform.t_inq_sync_crm_detail inq_sync with(nolock)
        INNER JOIN SO_SODetails_U8_009 so_d with(nolock)
        ON so_d.iSOsID = inq_sync.sal_order_detail_id
        WHERE inq_sync.inq_order_detail_id = inq.inq_id
    )inq_so
    WHERE inq.new_cat_no = substring(records.cinvcode, 3, LEN(records.cinvcode))
) inq_summary

OUTER apply (
    SELECT
    count(*) total_inq_count,
    case count(*) when 0 then null else cast(count(inq_so.id) as float) / count(*) end so_inq_rate,
    sum(case when inq.inq_created_at >= DATEADD(year, -1, getdate()) then 1 else 0 end) last_year_inq_count,
    case sum(case when inq.inq_created_at >= DATEADD(year, -1, getdate()) then 1 else 0 end) when 0 then null
    else cast(sum(case when inq_so.id is not null AND inq.inq_created_at >= DATEADD(year, -1, getdate()) then 1 else 0 end) as float) / sum(case when inq.inq_created_at >= DATEADD(year, -1, getdate()) then 1 else 0 end) end last_year_so_inq_rate
    FROM prod_inq_result inq with(nolock)

    OUTER APPLY (
        SELECT TOP 1
        inq_sync.id,
        inq_sync.update_time

        FROM cato_platform.t_inq_sync_crm_detail inq_sync with(nolock)
        INNER JOIN SO_SODetails_U8_009 so_d with(nolock)
        ON so_d.iSOsID = inq_sync.sal_order_detail_id
        WHERE inq_sync.inq_order_detail_id = inq.inq_id
    )inq_so

    WHERE inq.new_cat_no = substring(records.cinvcode, 3, LEN(records.cinvcode))
    AND inq.inq_created_at >= records.rd08_ddate
) inq_summary_after_instock

-- 销售订单统计
OUTER apply (
    SELECT
    SUM(case when so_m.dverifysystime >= DATEADD(year, -1, getdate()) then 1 else 0 end) last_year_so_count
    FROM SO_SODetails_U8_009 so_d with(nolock)
    LEFT JOIN SO_SOMain_U8_009 so_m with(nolock)
    ON so_d.ID = so_m.ID
    WHERE 1=1
    AND so_m.dverifysystime is not null
    AND (so_d.cSCloser is null or so_d.cSCloser = 'asuser')
    AND so_d.cInvCode = substring(records.cinvcode, 3, LEN(records.cinvcode))
) so_summary

OUTER apply (
    SELECT
    count(*) so_count,
    SUM(so_d.iSum) so_sum,
    SUM(case when so_m.dverifysystime >= DATEADD(year, -1, getdate()) then 1 else 0 end) last_year_so_count,
    SUM(case when so_m.dverifysystime >= DATEADD(year, -1, getdate()) then so_d.iSum else 0 end) last_year_so_sum
    FROM SO_SODetails_U8_009 so_d with(nolock)
    LEFT JOIN SO_SOMain_U8_009 so_m with(nolock)
    ON so_d.ID = so_m.ID
    WHERE 1=1
    AND (so_d.cSCloser is null or so_d.cSCloser = 'asuser')
    AND so_d.cInvCode = substring(records.cinvcode, 3, LEN(records.cinvcode))
    AND so_m.dverifysystime >= records.rd08_ddate
) so_summary_after_instock

-- 批次累计出库统计
OUTER apply (
    SELECT
    count(distinct so_union.isosid) so_count,
    SUM(rd32_iSum) rd32_iSum,
    min(so_union.rd32_ddate) rd32_min_ddate,
    SUM(so_union.cur_month_rd32_isum) cur_month_rd32_isum,
    SUM(so_union.rd11_iquantity) rd11_iquantity

    FROM (
    SELECT
    so_d.isosid,
    so_d.iSum / so_d.iQuantity * rd32_d.iQuantity rd32_iSum,
    rd32_m.dDate rd32_ddate,
    case
    when datepart(year, rd32_m.dDate) = datepart(year, records.rd08_ddate) AND datepart(month, rd32_m.dDate) = datepart(month, records.rd08_ddate)
    then so_d.iSum / so_d.iQuantity * rd32_d.iQuantity else 0 end cur_month_rd32_isum,
    rd11_d.iQuantity rd11_iquantity

    FROM rdrecords11_U8_009 rd11_d with(nolock)

    LEFT JOIN rdrecord11_U8_009 rd11_m with(nolock)
    ON rd11_d.ID = rd11_m.ID

    LEFT JOIN mom_moallocate_U8_009 mom_allo with(nolock)
    ON mom_allo.AllocateId = rd11_d.iMPoIds

    LEFT JOIN rdrecords10_U8_009 rd10_d with(nolock)
    ON rd10_d.iMPoIds = mom_allo.MoDId

    LEFT JOIN rdrecords32_U8_009 rd32_d with(nolock)
    ON rd32_d.cInvCode = rd10_d.cInvCode
    AND rd32_d.cBatch = rd10_d.cBatch
    AND rd32_d.cFree1 = rd10_d.cFree1

    LEFT JOIN rdrecord32_U8_009 rd32_m with(nolock)
    ON rd32_m.ID = rd32_d.ID

    LEFT JOIN SO_SODetails_U8_009 so_d with(nolock)
    ON so_d.isosid = rd32_d.iorderdid

    WHERE rd11_m.dDate >= records.rd08_ddate
    AND rd11_d.cInvCode = records.cinvcode
    AND rd11_d.cBatch = records.cBatch

    UNION
    -- 形态转换单后做的销售出库
    SELECT
    so_d.isosid,
    so_d.iSum / so_d.iQuantity * rd32_d.iQuantity rd32_iSum,
    rd32_m.dDate rd32_ddate,
    case
    when datepart(year, rd32_m.dDate) = datepart(year, records.rd08_ddate) AND datepart(month, rd32_m.dDate) = datepart(month, records.rd08_ddate)
    then so_d.iSum / so_d.iQuantity * rd32_d.iQuantity else 0 end cur_month_rd32_isum,
    rd11_d.iQuantity rd11_iquantity

    FROM rdrecords09_U8_009 rd09_d with(nolock)

    LEFT JOIN RdRecord09_U8_009 rd09_m with(nolock)
    ON rd09_m.ID = rd09_d.ID

    LEFT JOIN RdRecord08_U8_009 rd08_m with(nolock)
    ON rd08_m.cBusCode = rd09_m.cBusCode

    LEFT JOIN RdRecords08_U8_009 rd08_d with(nolock)
    ON rd08_m.ID = rd08_d.ID
    AND rd08_d.iGroupNO = rd09_d.iGroupNO

    LEFT JOIN rdrecords11_U8_009 rd11_d with(nolock)
    ON rd08_d.cInvCode = rd11_d.cInvCode
    AND rd08_d.cBatch = rd11_d.cBatch
    AND rd08_d.cFree1 = rd11_d.cFree1

    LEFT JOIN rdrecord11_U8_009 rd11_m with(nolock)
    ON rd11_d.ID = rd11_m.ID

    LEFT JOIN mom_moallocate_U8_009 mom_allo with(nolock)
    ON mom_allo.AllocateId = rd11_d.iMPoIds

    LEFT JOIN rdrecords10_U8_009 rd10_d with(nolock)
    ON rd10_d.iMPoIds = mom_allo.MoDId

    LEFT JOIN rdrecords32_U8_009 rd32_d with(nolock)
    ON rd32_d.cInvCode = rd10_d.cInvCode
    AND rd32_d.cBatch = rd10_d.cBatch
    AND rd32_d.cFree1 = rd10_d.cFree1

    LEFT JOIN rdrecord32_U8_009 rd32_m with(nolock)
    ON rd32_m.ID = rd32_d.ID

    LEFT JOIN SO_SODetails_U8_009 so_d with(nolock)
    ON so_d.isosid = rd32_d.iorderdid

    WHERE rd09_m.cSource = '形态装换'
    AND rd09_d.cInvCode = records.cInvCode
    AND rd09_d.cBatch = records.cBatch

    ) so_union
) batch_so_summary

OUTER APPLY (
    SELECT
    count(*) total_count,
    sum(so_d.iSum / so_d.iQuantity * rd32_d.iQuantity) total_sum,
    sum(dbo.normalize_mg(rd32_d.CFree1) * rd32_d.iQuantity) total_mg,
    sum(rd11_cost.iPrice) rd11_cost,
    sum(case when rd32_m.dnverifytime >= DATEADD(year, -1, getdate()) then 1 else 0 end) last_year_count,
    sum(case when rd32_m.dnverifytime >= DATEADD(year, -1, getdate()) then so_d.iSum / so_d.iQuantity * rd32_d.iQuantity else 0 end) last_year_sum,
    sum(case when rd32_m.dnverifytime >= DATEADD(year, -1, getdate()) then dbo.normalize_mg(rd32_d.CFree1) * rd32_d.iQuantity else 0 end) last_year_mg_sum
    FROM rdrecords32_U8_009 rd32_d with(nolock)

    LEFT JOIN rdrecord32_U8_009 rd32_m with(nolock)
    ON rd32_d.ID = rd32_m.ID

    LEFT JOIN SO_SODetails_U8_009 so_d with(nolock)
    ON so_d.iSOsID = rd32_d.iorderdid

    OUTER APPLY (
        SELECT
        sum(rd11_d.iPrice) iPrice
        FROM rdrecords10_U8_009 rd10_d with(nolock)

        LEFT JOIN mom_moallocate_U8_009 mom_allo with(nolock)
        ON rd10_d.iMPoIds = mom_allo.MoDId

        LEFT JOIN rdrecords11_U8_009 rd11_d with(nolock)
        ON mom_allo.AllocateId = rd11_d.iMPoIds

        WHERE rd10_d.cInvCode = rd32_d.cInvCode
        AND rd10_d.cBatch = rd32_d.cBatch
    ) rd11_cost

    WHERE 1=1
    AND rd32_m.dnverifytime >= records.rd08_ddate
    AND rd32_d.cInvCode = SUBSTRING(records.cInvCode, 3, len(records.cInvCode))
) prd_rd32_summary_after_instock

OUTER apply (
    SELECT TOP 1
    tmp.cFree1,
    tmp.iTaxUnitPrice,
    tmp.so_count_1year
    FROM (
        SELECT
        so_d.cFree1,
        so_d.iTaxUnitPrice,
        so_m.dverifysystime,
        SUM(so_count.v) over(partition by so_d.cFree1) so_count_1year,  -- 入库前一年销售数量
        count(*) over(partition by so_d.cFree1) c
        FROM SO_SODetails_U8_009 so_d with(nolock)
        LEFT JOIN SO_SOMain_U8_009 so_m with(nolock)
        ON so_d.ID = so_m.ID

        OUTER APPLY(
            SELECT CASE WHEN so_m.dverifysystime >= dateadd(year, -1, records.rd08_ddate) THEN 1 else 0 END v
        )so_count

        WHERE so_m.dverifysystime <= records.rd08_ddate
        AND (so_d.csCloser IS NULL OR so_d.csCloser='asuser')
        AND so_d.cInvCode = SUBSTRING(records.cInvCode, 3, len(records.cInvCode))
    ) tmp
    ORDER BY tmp.c desc, tmp.dverifysystime desc

) history_so

OUTER APPLY (
SELECT
avg(case when po_m.cAuditTime >= dateadd(year, -1, getdate()) then po_d.iSum / po_d.iQuantity else null end) last_year_po_avg_unit_price,
avg(po_d.iSum / po_d.iQuantity) po_avg_unit_price
FROM PO_Podetails_U8_009 po_d with(nolock)
LEFT JOIN PO_Pomain_U8_009 po_m with(nolock)
ON po_d.POID = po_m.POID
WHERE 1=1
AND po_m.cAuditTime is not null
AND (po_d.cbCloser IS NULL OR po_d.cbCloser='asuser')
AND po_d.cInvCode = records.cInvCode

)po_summary

-- 剩余库存(考虑形态转换) current_stock
OUTER APPLY (

    SELECT
    sum(iQuantity) iQuantity
    FROM (
        SELECT
        stock.cInvCode,
        stock.cBatch,
        stock.iQuantity
        FROM InvPositionSum_U8_009 stock WITH(nolock)
        WHERE stock.cInvCode = records.cInvCode
        AND stock.cBatch = records.cBatch

        UNION

        SELECT
        stock.cInvCode,
        stock.cBatch,
        stock.iQuantity

        FROM rdrecords09_U8_009 rd09_d with(nolock)

        LEFT JOIN RdRecord09_U8_009 rd09_m with(nolock)
        ON rd09_m.ID = rd09_d.ID

        LEFT JOIN RdRecord08_U8_009 rd08_m with(nolock)
        ON rd08_m.cBusCode = rd09_m.cBusCode

        LEFT JOIN RdRecords08_U8_009 rd08_d with(nolock)
        ON rd08_m.ID = rd08_d.ID
        AND rd08_d.iGroupNO = rd09_d.iGroupNO

        INNER JOIN InvPositionSum_U8_009 stock WITH(nolock)
        ON stock.cInvCode = rd08_d.cInvCode
        AND stock.cBatch = rd08_d.cBatch
        AND stock.cInvCode LIKE 'M-%'

        WHERE rd09_m.cSource = '形态装换'
        AND rd09_d.cInvCode = records.cInvCode
        AND rd09_d.cBatch = records.cBatch
    )union_stock

) current_stock

OUTER APPLY (
SELECT
case
when dbo.normalize_mg(history_so.cFree1) = 0 OR history_so.so_count_1year = 0 then NULL
when (records.iQuantity / dbo.normalize_mg(history_so.cFree1) / history_so.so_count_1year)>4 then 4
else records.iQuantity / dbo.normalize_mg(history_so.cFree1) / history_so.so_count_1year
end predict_sale_year  -- 预计销售年数
)processed

OUTER APPLY (
SELECT
ratio
FROM year_ratio
WHERE year_ratio.n = floor(processed.predict_sale_year)
)year_ratio


ORDER BY records.rd08_ddate desc