Msg 8728, Level 16, State 1, Server CSR-SQL-01, Procedure AssociateClient, Line 197RANGE 窗口框架的 ORDER BY 列表不能包含 LOB 类型的表达式。Procedure execution failed RANGE 窗口框架的 ORDER BY 列表不能包含 LOB 类型的表达式。 (8728)时间: 0.617s新建文本文档 (3).rarALTER PROCEDURE .
@BusinessCode AS nvarchar(100) , --客户代码
@ShopCode AS nvarchar(100) , --店铺代码
@ThisDate AS NVarchar(50) --年月
AS
BEGIN
set nocount on
-- routine body goes here, e.g.
-- SELECT 'Navicat for SQL Server'
--常规
create table #Temp_Ought --创建临时表#Tmp
(
TakeDate datetime , --日期
Type varchar(MAX), --类型
Remark varchar(MAX), --说明
ExpenseItemName varchar (MAX), --费用项目
BillNo varchar(MAX) , -- 单据号
SourceBillNo varchar(MAX) , -- 来源单号
counts varchar(MAX) , -- 货品总数
AmountOught Numeric(10,2) , -- 本期应收
);
create table #Temp_Ready --创建临时表#Tmp
(
TakeDate datetime, --日期
Type varchar(MAX), --类型
Remark varchar(MAX), --说明
ExpenseItemName varchar (MAX), --费用项目
BillNo varchar(MAX) , -- 单据号
SourceBillNo varchar(MAX) , -- 来源单号
counts varchar(MAX) , -- 货品总数
AmountGathering Numeric(10,2) , -- 本期应收
);
INSERT into #Temp_Ought(TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceBillNo,counts,AmountOught)
select voucher.CheckDate as thisDate,case when convert(nvarchar(10) ,voucher.CheckDate,120) !='' then '应收' end as 类型 , case when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '10' then '第一次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '15' then '第一次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '20' then '第二次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '21' and datepart(dd,cbjm.EndDate) >= '28' then '第三次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '16' and datepart(dd,cbjm.EndDate) >= '28' then '第二次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '1' and datepart(dd,cbjm.EndDate) >= '28' then '第一次'
else voucher.ManualBillNo
end as Remark , expenseItem.ExpenseItemName,voucher.BillNo ,voucher.SourceBillNo
,(select sum(Qty)
from FIRP_Arp_VoucherDetail s
left join Sys_BillType billType on billType.BillTypeID = s.SourcebtID
where s.BillNo = voucher.BillNo) as counts
,voucher.Amount
from FIRP_ARP_VoucherMaster voucher
left join vwSD_CustomerManerage manerag on voucher.ObjectValue = manerag.VendCustID
left join ( SELECT case when isnull(ExpenseItemName,'')='' then '' else ExpenseItemName end as ExpenseItemName ,fav.BillNo
FROM dbo.FIRP_ARP_VoucherOtherExpense fav
LEFT JOIN Bas_ExpenseItem be ON (be.CompanyID='00000000' or be.CompanyID = fav.CompanyID) AND be.ExpenseItemID = fav.ExpenseItemID and ExpenseItemName is not null and ExpenseItemName <> ''
where fav.CompanyID='YM') expenseItem on expenseItem.BillNo = voucher.BillNo
LEFT JOIN vwFIRP_ARP_PlanDetail detail on detail.BillNo = voucher.BillNo
LEFT JOIN SD_Sal_CustBalanceJointMaster cbjm on cbjm.BillNo = voucher.SourceBillNo
where CONVERT(NVARCHAR(10),VOUCHER.CHECKDATE,120) like ''+ @ThisDate +'%'
and manerag.VendCustCode = ''+convert(nvarchar(10),@BusinessCode)+''
ORDER BY voucher.CheckDate, voucher.BillNo
INSERT into #Temp_Ready(TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceBillNo,counts,AmountGathering)
select voucher.CheckDate as thisDate,case when convert(nvarchar(10) ,voucher.CheckDate,120) !='' then '收款' end as 类型 ,
case when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '10' then '第一次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '15' then '第一次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.EndDate) = '20' then '第二次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '21' and datepart(dd,cbjm.EndDate) >= '28' then '第三次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '16' and datepart(dd,cbjm.EndDate) >= '28' then '第二次'
when voucher.SourcebtID = 'CustBalanceJoint' and datepart(dd,cbjm.StartDate) = '1' and datepart(dd,cbjm.EndDate) >= '28' then '第一次'
else voucher.ManualBillNo
end as Remark
, expenseItem.ExpenseItemName,
voucher.BillNo ,
voucher.SourceBillNo
,(select sum(Qty)
from FIRP_Arp_VoucherDetail s
left join Sys_BillType billType on billType.BillTypeID = s.SourcebtID
where s.BillNo = voucher.BillNo) as counts,voucher.Amount
from FIRP_Pre_RPVoucherMaster voucher
left join vwSD_CustomerManerage manerag on voucher.ObjectValue = manerag.VendCustID
left join Bas_Shop shop on shop.CustomerID = manerag.VendCustID
left join ( SELECT case when isnull(ExpenseItemName,'')='' then '' else ExpenseItemName end as ExpenseItemName ,fav.BillNo
FROM dbo.FIRP_ARP_VoucherOtherExpense fav
LEFT JOIN Bas_ExpenseItem be ON (be.CompanyID='00000000' or be.CompanyID = fav.CompanyID) AND be.ExpenseItemID = fav.ExpenseItemID and ExpenseItemName is not null and ExpenseItemName <> ''
where fav.CompanyID='YM') expenseItem on expenseItem.BillNo = voucher.BillNo
LEFT JOIN SD_Sal_CustBalanceJointMaster cbjm on cbjm.BillNo = voucher.SourceBillNo
where convert(nvarchar(10) , voucher.CheckDate,120) like ''+ @ThisDate +'%'
--CONVERT(NVARCHAR(10),VOUCHER.CHECKDATE,120) like ''+CONVERT(NVARCHAR(10),@THISDATE)+'%'
and manerag.VendCustCode = ''+CONVERT(NVARCHAR(10),@BusinessCode)+''
ORDER BY voucher.CheckDate, voucher.BillNo
-- DATEADD(ss,+(rand(checksum(newid()))*10),TakeDate)
SELECT * INTO #end_Temp FROM
(
SELECT TakeDate as TakeDate ,Type,Remark,ExpenseItemName,BillNo,SourceBillNo,counts,AmountOught,CONVERT(decimal(18,2),0) SHOURU ,AmountOught AS HEJI FROM #Temp_Ought
UNION ALL
SELECT TakeDate as TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceBillNo,counts,00, AmountGathering,-AmountGathering AS HEJI FROM #Temp_Ready
) T
IF OBJECT_ID('#end_Temp') IS NOT NULL
declare @AmountOught_sym Numeric(10,2), -- 期初
@SQL_ NVARCHAR(MAX)
-- routine body goes here, e.g.
-- SELECT 'Navicat for SQL Server
create table #Temp_Ought_Previous --创建临时表#Tmp
(
TakeDate datetime, --日期
AmountOught Numeric(10,2) , -- 本期应收
);
create table #Temp_Ready_Previous --创建临时表#Tmp
(
TakeDate datetime, --日期
AmountGathering Numeric(10,2) , -- 本期应收
);
INSERT into #Temp_Ought_Previous(TakeDate,AmountOught)
select voucher.CheckDate as thisDate,
voucher.Amount
from FIRP_ARP_VoucherMaster voucher
left join vwSD_CustomerManerage manerag on voucher.ObjectValue = manerag.VendCustID
where convert(nvarchar(10) , voucher.CheckDate,120) < ''+CONVERT(VARCHAR(10),@ThisDate+'-01')+''
and manerag.VendCustCode = ''+CONVERT(NVARCHAR(10),@BusinessCode)+''
ORDER BY voucher.CheckDate
--voucher.CheckDate
INSERT into #Temp_Ready_Previous(TakeDate,AmountGathering)
select voucher.CheckDate as thisDate,
voucher.Amount
from FIRP_Pre_RPVoucherMaster voucher
left join vwSD_CustomerManerage manerag on voucher.ObjectValue = manerag.VendCustID
where convert(nvarchar(10) , voucher.CheckDate,120) < ''+CONVERT(VARCHAR(10),@ThisDate+'-01')+''
and manerag.VendCustCode = ''+CONVERT(NVARCHAR(10),@BusinessCode)+''
ORDER BY voucher.CheckDate
SELECT * INTO #end_Temp_Previous FROM
(
SELECT TakeDate, AmountOught,CONVERT(decimal(18,2),0) SHOURU ,AmountOught AS HEJI FROM #Temp_Ought_Previous
UNION ALL
SELECT TakeDate, 00, AmountGathering,-AmountGathering AS HEJI FROM #Temp_Ready_Previous
) T
--select * from #end_Temp_Previous
set @AmountOught_sym = (SELECT top 1 SUM(HEJI)OVER( ORDER BY TakeDate ) AS YUE FROM #end_Temp_Previous ORDER BY TakeDate desc)
--select @AmountOught_sym
--计算期初
--SELECT @ThisDate
SET @ThisDate=''+CONVERT(VARCHAR(10),@ThisDate+'-01')+''
--SELECT @ThisDate
/*
INSERT into #end_Temp(TakeDate,Type)
select ''+CONVERT(NVARCHAR(10),@ThisDate)+'','期初'
*/
-- if exists(select * from #end_Temp)
INSERT into #end_Temp(TakeDate,Type,HEJI)
select ''+CONVERT(NVARCHAR(10),@ThisDate)+'','期初', ''+convert(nvarchar(100),@AmountOught_sym)+' '
select * from #end_Temp
SELECT DISTINCT *,SUM(HEJI)OVER(ORDER BY TakeDate,BillNo) AS YUE FROM #end_Temp ORDER BY TakeDate
-- SELECT * FROM #end_Temp ORDER BY TakeDate PARTITION BY TakeDate
-- select TakeDate,Type,Remark,ExpenseItemName,BillNo,SourceBillNo,counts,AmountOught,AmountGathering,Balance from #Temp
END------------- SELECT DISTINCT *,SUM(HEJI)OVER(ORDER BY TakeDate,BillNo) AS YUE FROM #end_Temp ORDER BY TakeDate 把order by 后面的 billNo删除掉就正常了
