proc.bak

将excel格式的文件销售订单导入数据库中

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_SaleSat]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SaleSat]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sp_SaleSat
@begindate datetime,
@enddate datetime,
@cCusCode varchar (40),   --客户名称
@cWhCode varchar (40)   --仓库编码
AS
SELECT rdrecord.cdefine12 as cCusCode,RdRecord.cWhCode as cWhCode,RdRecords.cInvCode as cInvCode,   
sum((case when RdRecords.iQuantity is null then 0 else RdRecords.iQuantity end)*(case when RdRecord.bRdFlag < 1 then -1 else 1 end)) as qichusl into #temp1 
FROM RdRecord,RdRecords  
WHERE ( RdRecords.ID = RdRecord.ID ) and csource like '%调拨%' and rdrecord.ddate<@begindate
and rdrecord.cdefine12 like '%'+@cCusCode+'%' and RdRecord.cWhCode like '%'+@cWhCode+'%'
group by rdrecord.cdefine12,RdRecord.cWhCode,RdRecords.cInvCode
 
SELECT rdrecord.cdefine12 as cCusCode,RdRecord.cWhCode as cWhCode,RdRecords.cInvCode as cInvCode,   
sum(case when RdRecords.iQuantity is null then 0 else RdRecords.iQuantity end) as rukusl  into #temp2
FROM RdRecord,RdRecords  
WHERE ( RdRecords.ID = RdRecord.ID ) and csource like '%调拨%' and RdRecord.bRdFlag=1 
and rdrecord.ddate>=@begindate and rdrecord.ddate<=@enddate
group by rdrecord.cdefine12,RdRecord.cWhCode,RdRecords.cInvCode

 

SELECT rdrecord.cdefine12 as cCusCode,RdRecord.cWhCode as cWhCode,RdRecords.cInvCode as cInvCode,   
sum(case when RdRecords.iQuantity is null then 0 else RdRecords.iQuantity end) as chukusl into #temp3
FROM RdRecord,rdRecords  
WHERE ( RdRecords.ID = RdRecord.ID ) and csource like '%调拨%' and RdRecord.bRdFlag=0 
and rdrecord.ddate>=@begindate and rdrecord.ddate<=@enddate
and rdrecord.cdefine12 like '%'+@cCusCode+'%' and RdRecord.cWhCode like '%'+@cWhCode+'%'
group by rdrecord.cdefine12,RdRecord.cWhCode,RdRecords.cInvCode

SELECT rdrecord.cdefine12 as cCusCode,RdRecord.cWhCode as cWhCode,RdRecords.cInvCode as cInvCode,   
sum((case when RdRecords.iQuantity is null then 0 else RdRecords.iQuantity end)*(case when RdRecord.bRdFlag < 1 then -1 else 1 end)) as qimosl  into #temp4
FROM RdRecord,RdRecords  
WHERE ( RdRecords.ID = RdRecord.ID ) and csource like '%调拨%' and rdrecord.ddate<=@enddate
and rdrecord.cdefine12 like '%'+@cCusCode+'%' and RdRecord.cWhCode like '%'+@cWhCode+'%'
group by rdrecord.cdefine12,RdRecord.cWhCode,RdRecords.cInvCode

select #temp4.cCusCode,#temp4.cWhCode,#temp4.cInvCode,
case when #temp1.qichusl is null then 0 else #temp1.qichusl end as qichusl,0 as qcjg,0 as qcje,
case when #temp2.rukusl is null then 0 else #temp2.rukusl end as rukusl,0 as rkjg,0 as rkje,
case when #temp3.chukusl is null then 0 else #temp3.chukusl end as chukusl,0 as ckjg,0 as ckje,
case when #temp4.qimosl is null then 0 else #temp4.qimosl end as qimosl,0 as qmjg,0 as qmje
from #temp4 LEFT OUTER JOIN #temp1 on #temp4.cCusCode = #temp1.cCusCode and #temp4.cWhCode = #temp1.cWhCode and #temp4.cInvCode = dbo.#temp1.cInvCode
            LEFT OUTER JOIN #temp2 on #temp4.cCusCode = #temp2.cCusCode and #temp4.cWhCode = #temp2.cWhCode and #temp4.cInvCode = #temp2.cInvCode
	    LEFT OUTER JOIN #temp3 on #temp4.cCusCode = #temp3.cCusCode and #temp4.cWhCode = #temp3.cWhCode and #temp4.cInvCode = #temp3.cInvCode 
drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO