# `Sp_MaterialHistoricalComparison` (procedure)


- **Type:** PROCEDURE
- **Deterministic:** NO
- **SQL data access:** CONTAINS SQL

## Parameters

| # | Mode | Name | Type |
|---|---|---|---|
| 1 | IN | `sStartDate` | `varchar(32)` |
| 2 | IN | `sEndDate` | `varchar(32)` |
| 3 | IN | `sBrId` | `varchar(100)` |
| 4 | IN | `sSuId` | `varchar(100)` |

## Body

_Body is not pre-cached. To inspect: `mysql --defaults-file=~/.my.cnf -e 'SHOW CREATE PROCEDURE `Sp_MaterialHistoricalComparison`'`._

## Narrative

**Business context:** 采购管理 → 分析报表 → 主材历史数据对比报表 — month-over-month price/quantity comparison for non-paper materials. Pulls two month-buckets of audited production-material consumption (`viw_mitproductionmaterials` with `bCheck=1`, excluding `sMaterialsType='纸张'`), computes per-material average price and total qty/money for each side, and reports the price delta — used by procurement to surface materials whose unit cost has drifted between two months.

**What it does:** Creates temp `MaterialHistorical` (one row per material × style per month) and `MaterialHistoricalComparison` (the comparison grid). `insert into MaterialHistorical` from `viw_mitproductionmaterials LEFT JOIN elematerials` keyed on `date_format(tCheckDate,'%Y-%m') IN (sStartDate, sEndDate)`. Two `update MaterialHistoricalComparison` passes splice each side's `dDosage / dMoney / dPrice` into `dBeforeDosage / dBeforeMoney / dBeforeAveragePrice` and `dEnd*`. Final update sets `dTotalPriceDifference = (dEndAveragePrice - dBeforeAveragePrice) * dBeforeDosage`. Returns the comparison grid as the result-set. Note: the proc takes `sBrId / sSuId` and defaults them to `'1111111111'` but never filters by tenant downstream — every tenant's data is mixed in the comparison.

**Invocation:** Bound as the data-source of two report forms via `gdsconfigformmaster.sSqlStr`: 采购主材价格浮动表 (`gfm.sId 16147620210008611994372989400000`, under 物料采购流程 → 采购主材价格浮动表 module `101251240115016147537123820`) and 主材历史数据对比报表 (`gfm.sId 101251240115016204642443080`, under 采购管理 → 主材历史数据对比报表 module `101251240115016204642442770`). Install script ships at `script/标版/30100101/Sp_MaterialHistoricalComparison.sql`.