Consumable product management storage process for table 3 and table 4 queries

 

--- By year, department, consumption material type, total amount of purchased materials,
--- Total amount of consumed materials to query the purchased materials and materials consumed

Create Proc [SearchDetail]
@ DepartmentName Nvarchar (50) = '',
@ SearchDate DateTime, -- Query time
@ DateTag Int = 0, -- indicates the year or month, 0 months, and 1 year.
@ ArticleSortName Nvarchar (50) =''
-- @ BuyTatal float,
-- @ Consumetotal float

As
--- Process query time
Declare @ c_LessDate DateTime
Declare @ c_GreaterDate DateTime
Declare @ l_LessDate DateTime
Declare @ l_GreaterDate DateTime
If (@ dateTag = 0) -- query by month
Begin
Set @ c_LessDate = DATEADD (mm, DATEDIFF (mm, 0, @ searchDate), 0) -- the first day of the month
Set @ c_GreaterDate = dateadd (MS,-3, DATEADD (mm, DATEDIFF (m, 0, @ searchDate) + 1, 0) -- last day of the month
Set @ l_LessDate = DATEADD (mm,-1, @ c_LessDate)
Set @ l_GreaterDate = DATEADD (mm,-1, @ c_GreaterDate)
End
If (@ dateTag = 1) -- query by year
Begin
Set @ c_LessDate = DATEADD (yy, DATEDIFF (yy, 0, @ searchDate), 0) -- the first day of the year
Set @ c_GreaterDate = dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, @ searchDate) + 1, 0) -- last day of the year
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), @ maid, 120)
Set @ p_GreaterDate = convert (varchar (30), @ maid, 120)

--- Query
Select 'item category '= Case
When Buy. ArticleSortName is null Then Consume. ArticleSortName
Else Buy. ArticleSortName
End,
Isnull (Buy. prefix, 0) 'purchase last month', isnull (Buy. nonce, 0) 'buy this month', isnull (Buy. buyTotal, 0) 'total purchases ', isnull (Buy. buyCompare, 0) 'purchase comparison ', isnull (Buy. buyPercent, 0) 'purchase change % ',
Isnull (Consume. prefix, 0) 'Consumed last month', isnull (Consume. nonce, 0) 'Consumed this month', isnull (Consume. buyTotal, 0) 'total consumed ', isnull (Consume. buyCompare, 0) 'consumption comparison ', isnull (Consume. buyPercent, 0) 'consumption increase/decrease %'
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)
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)
On a. ArticleSortName = bc. ArticleSortName) As Consume
On Buy. ArticleSortName = Consume. ArticleSortName

GO

 

 

--- By year, department, consumption material type, total amount of purchased materials,
--- Total amount of consumed materials to query the purchased materials and materials consumed

Create Proc [SearchStatistics]
@ DepartmentName Nvarchar (50) = '',
@ SearchDate DateTime, -- Query time
@ DateTag Int = 0, -- indicates the year or month, 0 months, and 1 year.
@ ArticleSortName Nvarchar (50) =''
-- @ BuyTatal float,
-- @ Consumetotal float

As
--- Process query time
Declare @ c_LessDate DateTime
Declare @ c_GreaterDate DateTime
Declare @ l_LessDate DateTime
Declare @ l_GreaterDate DateTime
If (@ dateTag = 0) -- query by month
Begin
Set @ c_LessDate = DATEADD (mm, DATEDIFF (mm, 0, @ searchDate), 0) -- the first day of the month
Set @ c_GreaterDate = dateadd (MS,-3, DATEADD (mm, DATEDIFF (m, 0, @ searchDate) + 1, 0) -- last day of the month
Set @ l_LessDate = DATEADD (mm,-1, @ c_LessDate)
Set @ l_GreaterDate = DATEADD (mm,-1, @ c_GreaterDate)
End
If (@ dateTag = 1) -- query by year
Begin
Set @ c_LessDate = DATEADD (yy, DATEDIFF (yy, 0, @ searchDate), 0) -- the first day of the year
Set @ c_GreaterDate = dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, @ searchDate) + 1, 0) -- last day of the year
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), @ maid, 120)
Set @ p_GreaterDate = convert (varchar (30), @ maid, 120)

--- Query
Select 'department name' = Case
When Buy. DepartmentName is null Then Consume. DepartmentName
Else Buy. DepartmentName
End,
Isnull (Buy. prefix, 0) 'purchase last month', isnull (Buy. nonce, 0) 'buy this month', isnull (Buy. buyTotal, 0) 'total purchases ', isnull (Buy. buyCompare, 0) 'purchase comparison ', isnull (Buy. buyPercent, 0) 'purchase change % ',
Isnull (Consume. prefix, 0) 'Consumed last month', isnull (Consume. nonce, 0) 'Consumed this month', isnull (Consume. buyTotal, 0) 'total consumed ', isnull (Consume. buyCompare, 0) 'consumption comparison ', isnull (Consume. buyPercent, 0) 'consumption increase/decrease %'
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)
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)
On a. DepartmentName = bc. DepartmentName) As Consume
On Buy. DepartmentName = Consume. DepartmentName

GO