set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[Search_TongJiTime] @date DateTime, --开始时间
@dateend DateTime --结束时间AS
BEGIN
SET NOCOUNT ON
DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,Monday,23)) ,
@S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,Monday,23))+',0)'+quotename(convert(VARCHAR,Monday,23))
FROM VIEW_XuQiuGongYingTongji
where Monday>=@date and Monday<=@dateend --这里加时间限制
GROUP BY [Monday]
DECLARE @SQL VARCHAR(MAX)
SET @SQL='
SELECT CASE TYPE WHEN ''XuQiuNumber'' THEN ProductName ELSE '''' END ProductName,
CASE TYPE WHEN ''XuQiuNumber'' THEN SupplyName ELSE '''' END SupplyName,
Date = CASE TYPE WHEN ''XuQiuNumber'' THEN ''Order Demand'' WHEN ''AlreadyNumber'' THEN ''Schedule'' ELSE ''Balance'' END,
'+@S1+',Total
FROM (
SELECT [ProductName],[SupplyName],
CONVERT(VARCHAR,Monday,23) Monday,VALUE,TYPE,
SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total
FROM (SELECT * FROM VIEW_XuQiuGongYingTongji WHERE Monday>='''+convert(CHAR,@date,23)+''' and Monday<='''+convert(CHAR,@dateend,23)+''') VIEW_XuQiuGongYingTongji
UNPIVOT(VALUE FOR TYPE IN(XuQiuNumber,AlreadyNumber,Balance)) B
) A
PIVOT(MAX(VALUE) FOR Monday IN('+@S+')) B
ORDER BY B.ProductName,CASE TYPE WHEN ''XuQiuNumber'' THEN 1 WHEN ''AlreadyNumber'' THEN 2 ELSE 3 END
'
EXEC(@SQL)
SET NOCOUNT OFF
END里面的Balance字段为AlreadyNumber-XuQiuNumber 的差,因此我这样:(AlreadyNumber-XuQiuNumber)as Balance 但是调用绑定时出错,单独用查询分析器没出错。应该怎么改?
这个要怎么加,我用的是sql 2005 ?