CREATE PROCEDURE dbo.zfyProcureSupplierTotal
@startDate datetime,
@endDate datetime,
@ReceiptType char(1),
@SupplyCode nvarchar(15)='',
@startDate1 datetime=@startDate,
@endDate1 datetime=@endDate
AS
declare @PastStartDate datetime --同期开始时间
declare @PastEndDate datetime --同期结束时间
SET NOCOUNT ON
if (@startDate1 is null) or (@startDate1=@startDate and @endDate1 =@endDate)
BEGIN
SET @startDate1 =DATEADD(Month , -1, @startDate)
SET @endDate1 =DATEADD(Month , -1, @endDate)
END
SET @PastStartDate =@startDate1
SET @PastEndDate =@endDate1 --先删除临时表数据
DELETE FROM tblzfyTemp
--在临时表中插入当前起始时间数据
INSERT INTO tblzfyTemp EXEC zfyProcureOrderTotal @startDate,@endDate,@ReceiptType
--在临时表中插入同期起始时间数据
INSERT INTO tblzfyTemp EXEC zfyProcureOrderTotal @PastStartDate,@PastEndDate,@ReceiptType
SET NOCOUNT OFF
--查询汇总
if (@SupplyCode is null) or (@SupplyCode = '')
SELECT CONVERT(nchar(10),@PastStartDate,120) AS 同期起始日期,CONVERT(nchar(10),@PastEndDate,120) AS 同期结束日期,供货商代码,供货商名称,SUM(采购数量) AS 采购数量,
(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码) AS 同期采购量,
(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @startDate AND @endDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码)/(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码)-1 AS 增长率,
SUM(采购金额) AS 采购金额,
(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码) AS 同期采购额,
(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @startDate AND @endDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码)/(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码)-1 AS 增长率
FROM tblzfytemp
Where 采购日期 BETWEEN @startDate AND @endDate
Group By 供货商代码,供货商名称
else
SELECT CONVERT(nchar(10),@PastStartDate,120) AS 同期起始日期,CONVERT(nchar(10),@PastEndDate,120) AS 同期结束日期,供货商代码,供货商名称,SUM(采购数量) AS 采购数量,
(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=@SupplyCode) AS 同期采购量,
(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @startDate AND @endDate AND tblzfytempEx.供货商代码=@SupplyCode)/(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=@SupplyCode)-1 AS 增长率,
SUM(采购金额) AS 采购金额,
(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=@SupplyCode) AS 同期采购额,
(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @startDate AND @endDate AND tblzfytempEx.供货商代码=@SupplyCode)/(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=@SupplyCode)-1 AS 增长率
FROM tblzfytemp
Where 采购日期 BETWEEN @startDate AND @endDate AND 供货商代码=@SupplyCode
Group By 供货商代码,供货商名称
GO
@startDate datetime,
@endDate datetime,
@ReceiptType char(1),
@SupplyCode nvarchar(15)='',
@startDate1 datetime=@startDate,
@endDate1 datetime=@endDate
AS
declare @PastStartDate datetime --同期开始时间
declare @PastEndDate datetime --同期结束时间
SET NOCOUNT ON
if (@startDate1 is null) or (@startDate1=@startDate and @endDate1 =@endDate)
BEGIN
SET @startDate1 =DATEADD(Month , -1, @startDate)
SET @endDate1 =DATEADD(Month , -1, @endDate)
END
SET @PastStartDate =@startDate1
SET @PastEndDate =@endDate1 --先删除临时表数据
DELETE FROM tblzfyTemp
--在临时表中插入当前起始时间数据
INSERT INTO tblzfyTemp EXEC zfyProcureOrderTotal @startDate,@endDate,@ReceiptType
--在临时表中插入同期起始时间数据
INSERT INTO tblzfyTemp EXEC zfyProcureOrderTotal @PastStartDate,@PastEndDate,@ReceiptType
SET NOCOUNT OFF
--查询汇总
if (@SupplyCode is null) or (@SupplyCode = '')
SELECT CONVERT(nchar(10),@PastStartDate,120) AS 同期起始日期,CONVERT(nchar(10),@PastEndDate,120) AS 同期结束日期,供货商代码,供货商名称,SUM(采购数量) AS 采购数量,
(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码) AS 同期采购量,
(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @startDate AND @endDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码)/(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码)-1 AS 增长率,
SUM(采购金额) AS 采购金额,
(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码) AS 同期采购额,
(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @startDate AND @endDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码)/(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=tblzfytemp.供货商代码)-1 AS 增长率
FROM tblzfytemp
Where 采购日期 BETWEEN @startDate AND @endDate
Group By 供货商代码,供货商名称
else
SELECT CONVERT(nchar(10),@PastStartDate,120) AS 同期起始日期,CONVERT(nchar(10),@PastEndDate,120) AS 同期结束日期,供货商代码,供货商名称,SUM(采购数量) AS 采购数量,
(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=@SupplyCode) AS 同期采购量,
(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @startDate AND @endDate AND tblzfytempEx.供货商代码=@SupplyCode)/(SELECT SUM(采购数量) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=@SupplyCode)-1 AS 增长率,
SUM(采购金额) AS 采购金额,
(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=@SupplyCode) AS 同期采购额,
(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @startDate AND @endDate AND tblzfytempEx.供货商代码=@SupplyCode)/(SELECT SUM(采购金额) FROM tblzfytemp AS tblzfytempEx Where 采购日期 BETWEEN @PastStartDate AND @PastEndDate AND tblzfytempEx.供货商代码=@SupplyCode)-1 AS 增长率
FROM tblzfytemp
Where 采购日期 BETWEEN @startDate AND @endDate AND 供货商代码=@SupplyCode
Group By 供货商代码,供货商名称
GO
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货