易耗品管理 第三四表 查询的存储过程
---按年月、部门、消耗材料类别、累计购买材料总和、
---累计消耗材料总和来查询所购买材料和消耗材料的情况
Create Proc [SearchDetail]
@departmentName Nvarchar(50) ='',
@searchDate DateTime , --查询时间
@dateTag Int =0, --标识年或月，0月，1年
@articleSortName Nvarchar(50) =''
--@buyTatal float,
--@consumetotal float
As
---处理查询时间
Declare @c_LessDate DateTime
Declare @c_GreaterDate DateTime
Declare @l_LessDate DateTime
Declare @l_GreaterDate DateTime
if(@dateTag=0)--按月查询
Begin
Set @c_LessDate=DATEADD(mm, DATEDIFF(mm,0,@searchDate), 0)--当月第一天
Set @c_GreaterDate=dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@searchDate)+1, 0))--当月最后一天
Set @l_LessDate=DATEADD(mm,-1,@c_LessDate)
Set @l_GreaterDate=DATEADD(mm,-1,@c_GreaterDate)
End
if(@dateTag=1)--按年查询
Begin
Set @c_LessDate=DATEADD(yy, DATEDIFF(yy,0,@searchDate), 0)--当年第一天
Set @c_GreaterDate=dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,@searchDate)+1, 0))--当年最后一天
Set @l_LessDate=DATEADD(yyyy,-1,@c_LessDate)
Set @l_GreaterDate=DATEADD(yyyy,-1,@c_GreaterDate)
End
Declare @lessDate Nvarchar(50)
Set @lessDate=convert(varchar(30),@c_LessDate,120)
Declare @GreaterDate Nvarchar(50)
Set @GreaterDate=convert(varchar(30),@c_GreaterDate,120)
Declare @p_lessDate Nvarchar(50)
Declare @p_GreaterDate Nvarchar(50)
Set @p_lessDate=convert(varchar(30),@l_LessDate,120)
Set @p_GreaterDate=convert(varchar(30),@l_GreaterDate,120)
---查询
Select '物品类别'=Case
When Buy.ArticleSortName is null Then Consume.ArticleSortName
Else Buy.ArticleSortName
End,
isnull(Buy.Prefix,0) '上月购买', isnull(Buy.Nonce,0) '本月购买',isnull(Buy.BuyTotal,0) '购买总额',isnull(Buy.BuyCompare,0) '购买比较',isnull(Buy.BuyPercent,0) '购买升降%',
isnull(Consume.Prefix,0) '上月消耗', isnull(Consume.Nonce,0) '本月消耗',isnull(Consume.BuyTotal,0) '消耗总额',isnull(Consume.BuyCompare,0) '消耗比较',isnull(Consume.BuyPercent,0) '消耗升降%'
From
(Select bc.ArticleSortName , bc.Prefix, bc.Nonce,a.BuyTotal, BuyCompare, BuyPercent
From
(Select ArticleSortName=Case
When b.ArticleSortName is null Then c.ArticleSortName
Else b.ArticleSortName
End,
isnull(c.Prefix,0) Prefix, isnull(b.Nonce,0) Nonce,(isnull(b.Nonce,0)-isnull(c.Prefix,0)) BuyCompare,
BuyPercent =Case
When c.Prefix is null and b.Nonce is not null then '∞'
When c.Prefix is not null and b.Nonce is null then '0'
When c.Prefix is not null and b.Nonce is not null then Cast((b.Nonce*100/c.Prefix) As nvarchar(10))
End
From
(Select ArticleSortName, Sum(Price*Quantity) Nonce
From vwStorage
Where (DepartmentName Like '%'+ @departmentName + '%') And
(BuyDate Between @lessDate And @GreaterDate) And
ArticleSortName Like '%' + @articleSortName + '%'
Group By ArticleSortName ) b
full join
(Select ArticleSortName, Sum(Price*Quantity) Prefix
From vwStorage
Where (DepartmentName Like '%'+ @departmentName + '%') And
(BuyDate Between @p_lessDate And @p_GreaterDate) And
ArticleSortName Like '%' + @articleSortName + '%'
Group By ArticleSortName ) c
on b.ArticleSortName=c.ArticleSortName) bc
Left join
(Select ArticleSortName,Sum(Price*Quantity) BuyTotal From vwStorage
Group By ArticleSortName ) a
on a.ArticleSortName=bc.ArticleSortName) As Buy
full join
(Select bc.ArticleSortName , bc.Prefix, bc.Nonce,a.BuyTotal, BuyCompare, BuyPercent
From
(Select ArticleSortName=Case
When b.ArticleSortName is null Then c.ArticleSortName
Else b.ArticleSortName
End,
isnull(c.Prefix,0) Prefix, isnull(b.Nonce,0) Nonce,(isnull(b.Nonce,0)-isnull(c.Prefix,0)) BuyCompare,
BuyPercent =Case
When c.Prefix is null and b.Nonce is not null then '∞'
When c.Prefix is not null and b.Nonce is null then '0'
When c.Prefix is not null and b.Nonce is not null then Cast((b.Nonce*100/c.Prefix) As nvarchar(50))
End
From
(Select ArticleSortName, Sum(Price*Quantity) Nonce
From vwDelivery
Where (DepartmentName Like '%'+ @departmentName + '%') And
(OutDate Between @lessDate And @GreaterDate) And
ArticleSortName Like '%' + @articleSortName + '%'
Group By ArticleSortName ) b
full join
(Select ArticleSortName, Sum(Price*Quantity) Prefix
From vwDelivery
Where (DepartmentName Like '%'+ @departmentName + '%') And
(OutDate Between @p_lessDate And @p_GreaterDate) And
ArticleSortName Like '%' + @articleSortName + '%'
Group By ArticleSortName ) c
on b.ArticleSortName=c.ArticleSortName) bc
Left join
(Select ArticleSortName,Sum(Price*Quantity) BuyTotal From vwDelivery
Group By ArticleSortName ) a
on a.ArticleSortName=bc.ArticleSortName) As Consume
on Buy.ArticleSortName=Consume.ArticleSortName
GO
---按年月、部门、消耗材料类别、累计购买材料总和、
---累计消耗材料总和来查询所购买材料和消耗材料的情况
Create Proc [SearchStatistics]
@departmentName Nvarchar(50) ='',
@searchDate DateTime , --查询时间
@dateTag Int =0, --标识年或月，0月，1年
@articleSortName Nvarchar(50) =''
--@buyTatal float,
--@consumetotal float
As
---处理查询时间
Declare @c_LessDate DateTime
Declare @c_GreaterDate DateTime
Declare @l_LessDate DateTime
Declare @l_GreaterDate DateTime
if(@dateTag=0)--按月查询
Begin
Set @c_LessDate=DATEADD(mm, DATEDIFF(mm,0,@searchDate), 0)--当月第一天
Set @c_GreaterDate=dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,@searchDate)+1, 0))--当月最后一天
Set @l_LessDate=DATEADD(mm,-1,@c_LessDate)
Set @l_GreaterDate=DATEADD(mm,-1,@c_GreaterDate)
End
if(@dateTag=1)--按年查询
Begin
Set @c_LessDate=DATEADD(yy, DATEDIFF(yy,0,@searchDate), 0)--当年第一天
Set @c_GreaterDate=dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,@searchDate)+1, 0))--当年最后一天
Set @l_LessDate=DATEADD(yyyy,-1,@c_LessDate)
Set @l_GreaterDate=DATEADD(yyyy,-1,@c_GreaterDate)
End
Declare @lessDate Nvarchar(50)
Set @lessDate=convert(varchar(30),@c_LessDate,120)
Declare @GreaterDate Nvarchar(50)
Set @GreaterDate=convert(varchar(30),@c_GreaterDate,120)
Declare @p_lessDate Nvarchar(50)
Declare @p_GreaterDate Nvarchar(50)
Set @p_lessDate=convert(varchar(30),@l_LessDate,120)
Set @p_GreaterDate=convert(varchar(30),@l_GreaterDate,120)
---查询
Select '部门名称'=Case
When Buy.DepartmentName is null Then Consume.DepartmentName
Else Buy.DepartmentName
End,
isnull(Buy.Prefix,0) '上月购买', isnull(Buy.Nonce,0) '本月购买',isnull(Buy.BuyTotal,0) '购买总额',isnull(Buy.BuyCompare,0) '购买比较',isnull(Buy.BuyPercent,0) '购买升降%',
isnull(Consume.Prefix,0) '上月消耗', isnull(Consume.Nonce,0) '本月消耗',isnull(Consume.BuyTotal,0) '消耗总额',isnull(Consume.BuyCompare,0) '消耗比较',isnull(Consume.BuyPercent,0) '消耗升降%'
From
(Select bc.DepartmentName , bc.Prefix, bc.Nonce,a.BuyTotal, BuyCompare, BuyPercent
From
(Select DepartmentName=Case
When b.DepartmentName is null Then c.DepartmentName
Else b.DepartmentName
End,
isnull(c.Prefix,0) Prefix, isnull(b.Nonce,0) Nonce,(isnull(b.Nonce,0)-isnull(c.Prefix,0)) BuyCompare,
BuyPercent =Case
When c.Prefix is null and b.Nonce is not null then '∞'
When c.Prefix is not null and b.Nonce is null then '0'
When c.Prefix is not null and b.Nonce is not null then Cast((b.Nonce*100/c.Prefix) As nvarchar(50))
End
From
(Select DepartmentName, Sum(Price*Quantity) Nonce
From vwStorage
Where (DepartmentName Like '%'+ @departmentName + '%') And
(BuyDate Between @lessDate And @GreaterDate) And
ArticleSortName Like '%' + @articleSortName + '%'
Group By DepartmentName ) b
full join
(Select DepartmentName, Sum(Price*Quantity) Prefix
From vwStorage
Where (DepartmentName Like '%'+ @departmentName + '%') And
(BuyDate Between @p_lessDate And @p_GreaterDate) And
ArticleSortName Like '%' + @articleSortName + '%'
Group By DepartmentName ) c
on b.DepartmentName=c.DepartmentName) bc
Left join
(Select DepartmentName,Sum(Price*Quantity) BuyTotal From vwStorage
Group By DepartmentName ) a
on a.DepartmentName=bc.DepartmentName) As Buy
full join
(Select bc.DepartmentName , bc.Prefix, bc.Nonce,a.BuyTotal, BuyCompare, BuyPercent
From
(Select DepartmentName=Case
When b.DepartmentName is null Then c.DepartmentName
Else b.DepartmentName
End,
isnull(c.Prefix,0) Prefix, isnull(b.Nonce,0) Nonce,(isnull(b.Nonce,0)-isnull(c.Prefix,0)) BuyCompare,
BuyPercent =Case
When c.Prefix is null and b.Nonce is not null then '∞'
When c.Prefix is not null and b.Nonce is null then '0'
When c.Prefix is not null and b.Nonce is not null then Cast((b.Nonce*100/c.Prefix) As nvarchar(10))
End
From
(Select DepartmentName, Sum(Price*Quantity) Nonce
From vwDelivery
Where (DepartmentName Like '%'+ @departmentName + '%') And
(OutDate Between @lessDate And @GreaterDate) And
ArticleSortName Like '%' + @articleSortName + '%'
Group By DepartmentName ) b
full join
(Select DepartmentName, Sum(Price*Quantity) Prefix
From vwDelivery
Where (DepartmentName Like '%'+ @departmentName + '%') And
(OutDate Between @p_lessDate And @p_GreaterDate) And
ArticleSortName Like '%' + @articleSortName + '%'
Group By DepartmentName ) c
on b.DepartmentName=c.DepartmentName) bc
Left join
(Select DepartmentName,Sum(Price*Quantity) BuyTotal From vwDelivery
Group By DepartmentName ) a
on a.DepartmentName=bc.DepartmentName) As Consume
on Buy.DepartmentName=Consume.DepartmentName
GO
