CREATE PROCEDURE [dbo].[RPT_产品分组销售明细]
@StartTime datetime,
@CloesTime datetime,
@TID int
AS
SELECT c.产品编号, c.产品名称_1, e.单价级别名称_1,
d.主要级别名称_1, a.单价, SUM(a.数量 * a.份数 / a.份数分割) AS 数量, SUM(a.金额)
AS 金额, SUM(a.折扣金额) AS 折扣金额, SUM(a.成本) AS 成本, f.分组名称_1, SUM(a.服务费金额) AS 服务费
FROM n_V_库明细 a
INNER JOIN 产品表 c ON a.产品_ID = c.产品_ID
INNER JOIN 主要级别字典 d ON a.主要级别_ID = d.主要级别_ID
INNER JOIN 产品单价级别字典 e ON a.单价级别_ID = e.单价级别_ID
INNER JOIN n_V_库订单 b ON a.订单_ID = b.订单_ID AND a.日结日期 = b.日结日期
INNER JOIN 产品分组字典 f ON a.销售分组 = f.分组编号
WHERE (a.项目类型 IN (0, 3))
AND (f.统计类型 = 1)
AND (a.日结日期 >= @StartTime)
AND (a.日结日期 <= @CloesTime)
and ([email protected])
GROUP BY c.产品编号, c.产品名称_1, e.单价级别名称_1, a.单价, f.分组名称_1,
d.主要级别名称_1
ORDER BY c.产品编号
GO
------解决方案--------------------
------解决方案--------------------
- SQL code
CREATE PROCEDURE [dbo].[RPT_产品分组销售明细]@StartTime datetime,@CloesTime datetime,@TID int,@paixu varchar(20) ASselect @paixu=case @paixu when '1' then '产品编号' when '2' then '数量' when '3' then 金额'' else '' enddeclare @str varchar(max)set @str='SELECT c.产品编号, c.产品名称_1, e.单价级别名称_1, d.主要级别名称_1, a.单价, SUM(a.数量 * a.份数 / a.份数分割) AS 数量, SUM(a.金额) AS 金额, SUM(a.折扣金额) AS 折扣金额, SUM(a.成本) AS 成本, f.分组名称_1, SUM(a.服务费金额) AS 服务费FROM n_V_库明细 a INNER JOIN 产品表 c ON a.产品_ID = c.产品_ID INNER JOIN 主要级别字典 d ON a.主要级别_ID = d.主要级别_ID INNER JOIN 产品单价级别字典 e ON a.单价级别_ID = e.单价级别_ID INNER JOIN n_V_库订单 b ON a.订单_ID = b.订单_ID AND a.日结日期 = b.日结日期 INNER JOIN 产品分组字典 f ON a.销售分组 = f.分组编号WHERE (a.项目类型 IN (0, 3)) AND (f.统计类型 = 1)AND (a.日结日期 >='+ @StartTime+') 'AND (a.日结日期 <='+ @CloesTime+') 'and ([email protected]+')GROUP BY c.产品编号, c.产品名称_1, e.单价级别名称_1, a.单价, f.分组名称_1, d.主要级别名称_1ORDER BY [email protected]exec(@str)GO