/*--=============================
功能:产品销量统计
输入:
返回:
作者:dzh
时间:2006/06/02
说明:创建
*/--=============================
CREATE Procedure StatProductOut_F
@UserId int,
@datepart char(1), -- y(null)-年;m-月;q-季。
@field varchar(8), -- money(null)-金额;qty-数量。
@StartDate datetime,
@EndDate datetime,
@CustomerId int,
@InventoryTypeId int,
@ProductGroupId varchar(50),
@ProductName varchar(200)
AS
declare @strwhr varchar(8000) --查询条件
select @strwhr = ' WHERE (O.User_ID = '+convert(varchar(50),@UserId)+')'
select @strwhr = @strwhr + ' AND O.ProductOUT_Date Between '''+convert(varchar(10),@StartDate,120)+''' And '''+convert(varchar(10),@EndDate,120) +''''
if(@CustomerId>0)
select @strwhr = @strwhr + ' AND O.Customer_ID='+Convert(varchar(50),@CustomerId)
if(@InventoryTypeId>0)
select @strwhr = @strwhr + ' AND O.InventoryType_Id='+Convert(varchar(50),@InventoryTypeId)
If(rtrim(@ProductGroupId)!='')
select @strwhr = @strwhr + ' AND P.ProductGroup_ID='+@ProductGroupId
if(rtrim(@ProductName)!='')
select @strwhr = @strwhr + ' AND P.UserProduct_Name='''+@ProductName+''''
declare @str varchar(8000) --联接字符串
select @str = ''
declare @i int
select @i = 0
select @datepart = lower(rtrim(@datepart))
select @field = lower(rtrim(@field))
declare @tmpDate datetime
WHILE (@StartDate>'1900-1-1')
BEGIN
select @tmpDate = DateAdd(month,@i,@StartDate)
IF(@field='qty') --///统计数量
begin
If(@datepart='m') --/按月统计
Begin
select @i = @i + 1
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '/'
+ Convert(varchar(2),Month(@tmpDate)) + ']
= SUM(CASE Year(O.ProductOUT_Date)*100+Month(O.ProductOUT_Date)
WHEN '+ Convert(varchar(8),year(@tmpDate)*100+Month(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0) ELSE 0 END)'
IF year(@tmpDate)*100+month(@tmpDate) >= year(@EndDate)*100+month(@EndDate)
BREAK
ELSE
CONTINUE
End
Else If(@datepart='q') --/按季统计
Begin
select @i = @i + 3
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年'
+ Convert(varchar(1),dbo.GetQuarter(@tmpDate)) + '季度]
= SUM(CASE Year(O.ProductOUT_Date)*100+dbo.GetQuarter(O.ProductOUT_Date)
WHEN '+ Convert(varchar(8),year(@tmpDate)*100+dbo.GetQuarter(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0) ELSE 0 END)'
IF year(@tmpDate)*100+dbo.GetQuarter(@tmpDate) >= year(@EndDate)*100+dbo.GetQuarter(@EndDate)
BREAK
ELSE
CONTINUE
End
Else --/按年统计
Begin
select @i = @i + 12
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年]
= SUM(CASE Year(O.ProductOUT_Date)
WHEN '+ Convert(varchar(4),year(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0) ELSE 0 END)'
IF year(@tmpDate) >= year(@EndDate)
BREAK
ELSE
CONTINUE
End
end
ELSE --///统计金额
begin
If(@datepart='m') --/按月统计
Begin
select @i = @i + 1
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '/'
+ Convert(varchar(2),Month(@tmpDate)) + ']
= SUM(CASE Year(O.ProductOUT_Date)*100+Month(O.ProductOUT_Date)
WHEN '+ Convert(varchar(8),year(@tmpDate)*100+Month(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0)*ISNULL(O.ProductOUT_Price,0.0) ELSE 0 END)'
IF year(@tmpDate)*100+month(@tmpDate) >= year(@EndDate)*100+month(@EndDate)
BREAK
ELSE
CONTINUE
End
Else If(@datepart='q') --/按季统计
Begin
select @i = @i + 3
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年'
+ Convert(varchar(1),dbo.GetQuarter(@tmpDate)) + '季度]
= SUM(CASE Year(O.ProductOUT_Date)*100+dbo.GetQuarter(O.ProductOUT_Date)
WHEN '+ Convert(varchar(8),year(@tmpDate)*100+dbo.GetQuarter(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0)*ISNULL(O.ProductOUT_Price,0.0) ELSE 0 END)'
IF year(@tmpDate)*100+dbo.GetQuarter(@tmpDate) >= year(@EndDate)*100+dbo.GetQuarter(@EndDate)
BREAK
ELSE
CONTINUE
End
Else --/按年统计
Begin
select @i = @i + 12
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年]
= SUM(CASE Year(O.ProductOUT_Date)
WHEN '+ Convert(varchar(4),year(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0)*ISNULL(O.ProductOUT_Price,0.0) ELSE 0 END)'
IF year(@tmpDate) >= year(@EndDate)
BREAK
ELSE
CONTINUE
End
end
END
select @str = substring(@str,2,len(@str))
--Print @str
EXEC('
SELECT --SUM(O.ProductOUT_Qty) AS SumQty,
--SUM(O.ProductOUT_Price*O.ProductOUT_Qty) AS SumMoney
'+@str+'
FROM dbo.CU_Customer C RIGHT OUTER JOIN
dbo.PR_ProductOUT O INNER JOIN
dbo.PR_UserProduct P ON O.Product_ID = P.UserProduct_ID AND
O.User_ID = P.User_Id ON C.Customer_ID = O.Customer_ID AND
C.User_ID = O.User_ID' + @strwhr
)
GO
功能:产品销量统计
输入:
返回:
作者:dzh
时间:2006/06/02
说明:创建
*/--=============================
CREATE Procedure StatProductOut_F
@UserId int,
@datepart char(1), -- y(null)-年;m-月;q-季。
@field varchar(8), -- money(null)-金额;qty-数量。
@StartDate datetime,
@EndDate datetime,
@CustomerId int,
@InventoryTypeId int,
@ProductGroupId varchar(50),
@ProductName varchar(200)
AS
declare @strwhr varchar(8000) --查询条件
select @strwhr = ' WHERE (O.User_ID = '+convert(varchar(50),@UserId)+')'
select @strwhr = @strwhr + ' AND O.ProductOUT_Date Between '''+convert(varchar(10),@StartDate,120)+''' And '''+convert(varchar(10),@EndDate,120) +''''
if(@CustomerId>0)
select @strwhr = @strwhr + ' AND O.Customer_ID='+Convert(varchar(50),@CustomerId)
if(@InventoryTypeId>0)
select @strwhr = @strwhr + ' AND O.InventoryType_Id='+Convert(varchar(50),@InventoryTypeId)
If(rtrim(@ProductGroupId)!='')
select @strwhr = @strwhr + ' AND P.ProductGroup_ID='+@ProductGroupId
if(rtrim(@ProductName)!='')
select @strwhr = @strwhr + ' AND P.UserProduct_Name='''+@ProductName+''''
declare @str varchar(8000) --联接字符串
select @str = ''
declare @i int
select @i = 0
select @datepart = lower(rtrim(@datepart))
select @field = lower(rtrim(@field))
declare @tmpDate datetime
WHILE (@StartDate>'1900-1-1')
BEGIN
select @tmpDate = DateAdd(month,@i,@StartDate)
IF(@field='qty') --///统计数量
begin
If(@datepart='m') --/按月统计
Begin
select @i = @i + 1
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '/'
+ Convert(varchar(2),Month(@tmpDate)) + ']
= SUM(CASE Year(O.ProductOUT_Date)*100+Month(O.ProductOUT_Date)
WHEN '+ Convert(varchar(8),year(@tmpDate)*100+Month(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0) ELSE 0 END)'
IF year(@tmpDate)*100+month(@tmpDate) >= year(@EndDate)*100+month(@EndDate)
BREAK
ELSE
CONTINUE
End
Else If(@datepart='q') --/按季统计
Begin
select @i = @i + 3
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年'
+ Convert(varchar(1),dbo.GetQuarter(@tmpDate)) + '季度]
= SUM(CASE Year(O.ProductOUT_Date)*100+dbo.GetQuarter(O.ProductOUT_Date)
WHEN '+ Convert(varchar(8),year(@tmpDate)*100+dbo.GetQuarter(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0) ELSE 0 END)'
IF year(@tmpDate)*100+dbo.GetQuarter(@tmpDate) >= year(@EndDate)*100+dbo.GetQuarter(@EndDate)
BREAK
ELSE
CONTINUE
End
Else --/按年统计
Begin
select @i = @i + 12
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年]
= SUM(CASE Year(O.ProductOUT_Date)
WHEN '+ Convert(varchar(4),year(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0) ELSE 0 END)'
IF year(@tmpDate) >= year(@EndDate)
BREAK
ELSE
CONTINUE
End
end
ELSE --///统计金额
begin
If(@datepart='m') --/按月统计
Begin
select @i = @i + 1
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '/'
+ Convert(varchar(2),Month(@tmpDate)) + ']
= SUM(CASE Year(O.ProductOUT_Date)*100+Month(O.ProductOUT_Date)
WHEN '+ Convert(varchar(8),year(@tmpDate)*100+Month(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0)*ISNULL(O.ProductOUT_Price,0.0) ELSE 0 END)'
IF year(@tmpDate)*100+month(@tmpDate) >= year(@EndDate)*100+month(@EndDate)
BREAK
ELSE
CONTINUE
End
Else If(@datepart='q') --/按季统计
Begin
select @i = @i + 3
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年'
+ Convert(varchar(1),dbo.GetQuarter(@tmpDate)) + '季度]
= SUM(CASE Year(O.ProductOUT_Date)*100+dbo.GetQuarter(O.ProductOUT_Date)
WHEN '+ Convert(varchar(8),year(@tmpDate)*100+dbo.GetQuarter(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0)*ISNULL(O.ProductOUT_Price,0.0) ELSE 0 END)'
IF year(@tmpDate)*100+dbo.GetQuarter(@tmpDate) >= year(@EndDate)*100+dbo.GetQuarter(@EndDate)
BREAK
ELSE
CONTINUE
End
Else --/按年统计
Begin
select @i = @i + 12
select @str = @str + ',[' + Convert(varchar(4),year(@tmpDate)) + '年]
= SUM(CASE Year(O.ProductOUT_Date)
WHEN '+ Convert(varchar(4),year(@tmpDate)) +'
THEN ISNULL(O.ProductOUT_Qty,0)*ISNULL(O.ProductOUT_Price,0.0) ELSE 0 END)'
IF year(@tmpDate) >= year(@EndDate)
BREAK
ELSE
CONTINUE
End
end
END
select @str = substring(@str,2,len(@str))
--Print @str
EXEC('
SELECT --SUM(O.ProductOUT_Qty) AS SumQty,
--SUM(O.ProductOUT_Price*O.ProductOUT_Qty) AS SumMoney
'+@str+'
FROM dbo.CU_Customer C RIGHT OUTER JOIN
dbo.PR_ProductOUT O INNER JOIN
dbo.PR_UserProduct P ON O.Product_ID = P.UserProduct_ID AND
O.User_ID = P.User_Id ON C.Customer_ID = O.Customer_ID AND
C.User_ID = O.User_ID' + @strwhr
)
GO
/*--=============================
功能:获得当前日期所在的季度
输入:@date
返回:
作者:dzh
时间:2006/06/02
说明:创建
*/--=============================
CREATE function dbo.GetQuarter
(
@date datetime
)
Returns int
AS
Begin
Declare @quarter int
Select @quarter=1
SELECT @quarter=CASE month(@date)
WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 1
WHEN 4 THEN 2 WHEN 5 THEN 2 WHEN 6 THEN 2
WHEN 7 THEN 3 WHEN 8 THEN 3 WHEN 9 THEN 3
ELSE 4 END
Return @quarter
End
功能:获得当前日期所在的季度
输入:@date
返回:
作者:dzh
时间:2006/06/02
说明:创建
*/--=============================
CREATE function dbo.GetQuarter
(
@date datetime
)
Returns int
AS
Begin
Declare @quarter int
Select @quarter=1
SELECT @quarter=CASE month(@date)
WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 1
WHEN 4 THEN 2 WHEN 5 THEN 2 WHEN 6 THEN 2
WHEN 7 THEN 3 WHEN 8 THEN 3 WHEN 9 THEN 3
ELSE 4 END
Return @quarter
End