CodeCREATE PROCEDURE [dbo].[ComparisonOfContinuousTime_Multiple_DG_ALL_TypeAll]
@UserInfoID AS VARCHAR(21),
@QuantityOrSale AS VARCHAR(50),
@Day_From AS TINYINT,
@Day_To AS TINYINT,
@Year SMALLINT
AS
-- 判断闰年/小月
declare @Day_From_Tmp as TINYINT
declare @Day_To_Tmp as TINYINT
-- 创建一个临时表
CREATE TABLE #ComparisonOfContinuousTime
(
m SMALLINT, -- 月份
v FLOAT -- 值
)
DECLARE @BusinessInfoCode VARCHAR(21)
declare @unitID int
select @unitID=fld_UnitID from t_UserInfo where fld_ID=@UserInfoID
DECLARE MyCursor CURSOR FOR
SELECT fld_Code FROM dbo.T_BusinessInfo WHERE fld_RegulatorID in (select fld_ID from T_UserInfo where fld_UnitID in(select fld_ID from T_UnitInfo where fld_HigherID=@unitID))
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @BusinessInfoCode
WHILE @@FETCH_STATUS = 0
BEGIN
-- 声明循环因子
DECLARE @m SMALLINT
SET @m = 1
-- 建立循环体
WHILE (@m <= 12)
BEGIN
set @Day_From_Tmp = @Day_From
set @Day_To_Tmp = @Day_To
-- 判断闰年
if @m = 2
begin
if dbo.LongYear(@Year) = 1
begin
if @Day_From > 29
set @Day_From_Tmp = 29
if @Day_To > 29
set @Day_To_Tmp = 29
end
else
begin
if @Day_From > 28
set @Day_From_Tmp = 28
if @Day_To > 28
set @Day_To_Tmp = 28
end
end
-- 判断小月
if @m in (4, 6, 9, 11)
begin
if @Day_From > 30
set @Day_From_Tmp = 30
if @Day_To > 30
set @Day_To_Tmp = 30
end
-- 向临时表赋值
INSERT INTO #ComparisonOfContinuousTime
VALUES (
-- 年份
@m,
-- 当年指定时间范围的销量或销售金额
dbo.ContrastTheSamePeriod_GetOneYear(
CAST(
CAST(@Year AS VARCHAR(4))
+ '-' + CAST(@m AS VARCHAR(2))
+ '-' + CAST(@Day_From_Tmp AS VARCHAR(2))
AS DATETIME),
CAST(
CAST(@Year AS VARCHAR(4))
+ '-' + CAST(@m AS VARCHAR(2))
+ '-' + CAST(@Day_To_Tmp AS VARCHAR(2))
AS DATETIME),
@BusinessInfoCode,
'普通',
@QuantityOrSale)
+
dbo.ContrastTheSamePeriod_GetOneYear(
CAST(
CAST(@Year AS VARCHAR(4))
+ '-' + CAST(@m AS VARCHAR(2))
+ '-' + CAST(@Day_From_Tmp AS VARCHAR(2))
AS DATETIME),
CAST(
CAST(@Year AS VARCHAR(4))
+ '-' + CAST(@m AS VARCHAR(2))
+ '-' + CAST(@Day_To_Tmp AS VARCHAR(2))
AS DATETIME),
@BusinessInfoCode,
'一二类',
@QuantityOrSale))
-- 循环因子自增
SET @m = @m + 1
END
FETCH NEXT FROM MyCursor INTO @BusinessInfoCode
END
CLOSE MyCursor
DEALLOCATE MyCursor
-- 将临时表返回给调用者
-- SELECT m,SUM(v) as v FROM #ComparisonOfContinuousTime GROUP BY m
SELECT m,SUM(v) as v FROM #ComparisonOfContinuousTime GROUP BY m order by m
GO
CodeCREATE FUNCTION ContrastTheSamePeriod_GetOneYear
(@From DATETIME, @To DATETIME, @BusinessInfoCode VARCHAR(21), @Type VARCHAR(50), @QuantityOrSale VARCHAR(50))
RETURNS FLOAT AS
BEGIN
DECLARE @Result FLOAT
set @Result = 0
IF @QuantityOrSale = 'Quantity'
BEGIN
SELECT @Result = sum(fld_QuantityOfSale)
FROM dbo.T_Sampling
WHERE fld_BusinessInfoCode = @BusinessInfoCode
AND fld_Type = @Type
AND fld_Time >= @From
AND fld_Time <= @To
END
ELSE IF @QuantityOrSale = 'Sale'
BEGIN
SELECT @Result = sum(fld_ValueOfSale)
FROM dbo.T_Sampling
WHERE fld_BusinessInfoCode = @BusinessInfoCode
AND fld_Type = @Type
AND fld_Time >= @From
AND fld_Time <= @To
END
if @Result is null
set @Result = 0
-- 返回结果
RETURN @Result
END
