USE [gdss3.0]
GO
/****** Object: StoredProcedure [dbo].[UNI_OVERALLVIEW_WAREHOUSE_TREND] Script Date: 12/25/2014 13:38:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 综合展示-库存走势查询
ALTER PROC [dbo].[UNI_OVERALLVIEW_WAREHOUSE_TREND]
@DateRange INT,
@QueryVarietiese INT,
@IsVarietieseQuery INT,
@MainTitle VARCHAR(64) OUTPUT,
@SubTitle VARCHAR(64) OUTPUT,
@FootTitle VARCHAR(64) OUTPUT,
@Max INT OUTPUT,
@Min INT OUTPUT,
@Scale INT OUTPUT
AS
IF (@IsVarietieseQuery=1)
SET @MainTitle='分品种统计查询'
ELSE
SET @MainTitle='全部查询'
SET @FootTitle=''
SET @Scale=10DECLARE @BeginDate SMALLDATETIME
DECLARE @EndDate SMALLDATETIME
SET @EndDate = GETDATE();
IF (@DateRange=0)
SET @BeginDate=@EndDate-6
ELSE IF (@DateRange=1)
SET @BeginDate=@EndDate-14
ELSE
SET @BeginDate=@EndDate-29
SET @SubTitle='['+ CAST(YEAR(@BeginDate) AS CHAR(4)) + '/' + CAST(MONTH(@BeginDate) AS VARCHAR(2)) + '/' + CAST(DAY(@BeginDate) AS VARCHAR(2)) +
'--' + CAST(YEAR(@EndDate) AS CHAR(4)) + '/' + CAST(MONTH(@EndDate) AS VARCHAR(2)) + '/' + CAST(DAY(@EndDate) AS VARCHAR(2)) + ']'DECLARE @RESULT TABLE
(
ChartName VARCHAR(20),
ChartColor VARCHAR(20),
ChartLineWidth INT,
ChartValue DECIMAL(10, 2),
ChartProperty INT DEFAULT 0
)DECLARE @Color CHAR(7)
DECLARE @goods_kind_id INT
DECLARE @goods_kind_name VARCHAR(128)DECLARE @StartDate SMALLDATETIME
DECLARE @StopDate SMALLDATETIME
DECLARE @TradeDate VARCHAR(20)
DECLARE @DateStr VARCHAR(14)
SET @StartDate=@BeginDate-1
SET @StopDate=@EndDate
WHILE(@StartDate < @StopDate)
BEGIN
SET @StartDate = @StartDate + 1
EXEC UNI_DATETIME_CONVER_CHAR @StartDate, @DateStr OUTPUT
SET @TradeDate=SUBSTRING(@DateStr,5,2) + '/' + SUBSTRING(@DateStr,7,2)
INSERT INTO @RESULT (ChartName, ChartProperty) VALUES(@TradeDate, 2)
ENDIF (@IsVarietieseQuery=0)
BEGIN
SET @StartDate=@BeginDate-1
SET @StopDate=@EndDate
INSERT INTO @RESULT(ChartName, ChartColor, ChartLineWidth, ChartProperty) VALUES('库存走势','#EE82EE',2,1)
WHILE(@StartDate < @StopDate)
BEGIN
SET @StartDate = @StartDate + 1
EXEC UNI_DATETIME_CONVER_CHAR @StartDate, @DateStr OUTPUT
SET @TradeDate=SUBSTRING(@DateStr,1,8)
INSERT INTO @RESULT (ChartValue)
SELECT SUM(billincount-billoutcount)/1000 FROM view_billdates WHERE billstatus=1 AND CONVERT(NVARCHAR(8),billdate,112)=@TradeDate
END
INSERT INTO @RESULT(ChartName, ChartColor, ChartLineWidth, ChartProperty) VALUES(' ','#FFFFFF',2,1)
INSERT INTO @RESULT(ChartValue) VALUES(0)
END
ELSE
BEGIN
IF (@QueryVarietiese=0)
BEGIN
DECLARE Cur CURSOR LOCAL SCROLL
FOR
SELECT goods_kind_id, goods_kind_name FROM GoodsKind
OPEN Cur
FETCH FIRST FROM Cur INTO @goods_kind_id, @goods_kind_name
WHILE(@@fetch_status=0)
BEGIN
SET @StartDate=@BeginDate-1
SET @StopDate=@EndDate
EXEC UNI_RANDOM_COLOR @Color OUTPUT
INSERT INTO @RESULT(ChartName, ChartColor, ChartLineWidth, ChartProperty) VALUES(@goods_kind_name,@Color,2,1)
WHILE(@StartDate < @StopDate)
BEGIN
SET @StartDate = @StartDate + 1
EXEC UNI_DATETIME_CONVER_CHAR @StartDate, @DateStr OUTPUT
SET @TradeDate=SUBSTRING(@DateStr,1,8)
INSERT INTO @RESULT (ChartValue)
SELECT SUM(billincount-billoutcount)/1000 FROM view_billdates WHERE billstatus=1 AND SUBSTRING(CONVERT(NVARCHAR,billdate,112),1,8)=@TradeDate AND billkindid=@goods_kind_id
END
FETCH NEXT FROM Cur INTO @goods_kind_id, @goods_kind_name
END
CLOSE Cur
DEALLOCATE Cur
END
ELSE
BEGIN
SELECT @goods_kind_name=goods_kind_name FROM GoodsKind WHERE goods_kind_id=@QueryVarietiese
INSERT INTO @RESULT(ChartName, ChartColor, ChartLineWidth, ChartProperty) VALUES(@goods_kind_name,'#EE82EE',2,1)
SET @StartDate=@BeginDate-1
SET @StopDate=@EndDate
WHILE(@StartDate < @StopDate)
BEGIN
SET @StartDate = @StartDate + 1
EXEC UNI_DATETIME_CONVER_CHAR @StartDate, @DateStr OUTPUT
SET @TradeDate=SUBSTRING(@DateStr,1,8)
INSERT INTO @RESULT (ChartValue)
SELECT SUM(billincount-billoutcount)/1000 FROM view_billdates WHERE billstatus=1 AND SUBSTRING(CONVERT(NVARCHAR,billdate,112),1,8)=@TradeDate AND billkindid=@QueryVarietiese
END
INSERT INTO @RESULT(ChartName, ChartColor, ChartLineWidth, ChartProperty) VALUES(' ','#FFFFFF',2,1)
INSERT INTO @RESULT(ChartValue) VALUES(0)
END
END
UPDATE @RESULT SET ChartValue=0 WHERE ChartValue IS NULL AND ChartProperty=0
SELECT @Max=MAX(ChartValue), @Min=MIN(ChartValue) FROM @RESULT WHERE ChartName IS NULL
IF (@Max IS NULL AND @Min IS NULL)
BEGIN
SET @Max=100
SET @Min=0
END
ELSE IF (@Max IS NULL)
SET @Max = @Min + 100
ELSE IF (@Min IS NULL)
SET @Min = @Max - 100
SELECT * FROM @RESULT
RETURN这是原来的存储过程
你至少应该有个库存表,记录了当前的最新库存吧。
然后建个库存历史表,加上日期字段。开定时任务,每天零点把库存表记录以昨天的日期插入到库存历史表。你要的结果就是:库存历史表的最后6天数据 UNION ALL 库存表