62,046
社区成员
发帖
与我相关
我的任务
分享
-- =============================================
-- Author:
-- Create date: 2017-08-08
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[SP_QUERYDATAFROM_WFT_DATAFROM_SRMTOEIP]
@projecttimestart NVARCHAR(50) = NULL ,
@projecttimeend NVARCHAR(50) = NULL ,
@sapproductid NVARCHAR(50) = NULL ,
@productname NVARCHAR(50) = NULL
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='
SELECT o.ProjectTime
,o.SapProductID
,ProductName
,sum(cast(o.ApplyAmount as int)) as ApplyAmount
,sum(cast(o.Amount as int)) as Amount
,SUM(cast(o.AwardingAmount as int) ) as AwardingAmount
,sum(cast(sap.MENGE as float)) as MENGE
FROM dbo.WFT_DATAFROM_SRMTOEIP AS O
left join WFT_DATAFROM_SAP sap on o.EBELN=sap.EBELN WHERE 1=1
';
IF (
@projecttimestart IS NOT NULL
AND @projecttimeend IS NOT NULL
)
BEGIN
SET @projecttimestart = CONVERT(NVARCHAR, @projecttimestart, 23)
SET @projecttimeend = CONVERT(NVARCHAR, @projecttimeend, 23)
SET @SQL = @SQL + ' AND O.ProjectTime BETWEEN @projecttimestart AND @projecttimeend'''
END;
IF (@sapproductid IS NOT NULL)
BEGIN
SET @SQL = @SQL + ' AND O.sapproductid=@sapproductid'
END;
IF (@productname IS NOT NULL)
BEGIN
SET @SQL = @SQL + ' AND O.productname like ''%@productname%'' ';
END;
SET @SQL = @SQL + ' group by o.ProjectTime, o.SapProductID, ProductName ';
PRINT @SQL
EXEC sp_executesql @SQL
,N'@projecttimestart NVARCHAR(50),
@projecttimeend NVARCHAR(50),
@sapproductid NVARCHAR(50),
@productname NVARCHAR(50)'
,@projecttimestart
,@projecttimeend
,@sapproductid
,@productname
END;
EXEC [dbo].[SP_QUERYDATAFROM_WFT_DATAFROM_SRMTOEIP] NULL,
NULL,
NULL,
N'测试'