Such issues can also be extended to invoicing-like batch calculations, which also focus on other historical records to determine the status of a current record.
SQL statements cannot easily implement similar functions of an MDX statement, and must be contrasted with a crosstab association. Here we use CLR functions to implement similar syntax for MDX statements. It's OK to make a cache of what you've got when you select it. Efficiency should be improved a lot.
The code for the CLR is compiled as TestFun.dll and copied to the SQL Server's file directory.
--------------------------------------------------------------------------------
Copy Code code as follows:
Using System;
Using System.Data;
Using System.Data.SqlClient;
Using System.Data.SqlTypes;
Using Microsoft.SqlServer.Server;
public partial class Userdefinedfunctions
{
Save current Group Present value
private static System.Collections.Generic.Dictionary <string, SqlString > _listvalue = new System.Collections.Generic.Dictionary <string, SqlString > ();
Save Current Group
private static System.Collections.Generic.Dictionary <string, string > _listgroup = new System.Collections.Generi C.dictionary <string, String > ();
<summary>
Gets the record value of the current group
</summary>
<param name= "key" > Concurrency key </param>
<param name= "CurrentGroup" > Current group </param>
<param name= "CurrentValue" > Current group Current value </param>
<returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Getprevmembervalue (SqlString key,sqlstring currentgroup,sqlstring currentvalue)
{
if (key. IsNull | | Currentgroup.isnull) return SqlString. Null;
Try
{
SqlString Prevmembervalue = _listvalue[key. Value];
Group changes
if (_listgroup[key. Value]!= Currentgroup.value)
{
Prevmembervalue = SqlString. Null;
_listgroup[key. Value] = Currentgroup.value;
}
Value change
_listvalue[key. Value] = CurrentValue;
return prevmembervalue;
}
Catch
{
Return SqlString. Null;
}
}
<summary>
Initializing concurrent Keys
</summary>
<param name= "Key" ></param>
<returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean Initkey (SqlString key)
{
Try
{
_listvalue.add (key. Value, SqlString. Null);
_listgroup.add (key. Value, String. Empty);
return true;
}
Catch
{
return false;
}
}
<summary>
Releasing the Concurrency key
</summary>
<param name= "Key" ></param>
<returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean Disposekey (SqlString key)
{
Try
{
_listvalue.remove (key. Value);
_listgroup.remove (key. Value);
return true;
}
Catch
{
return false;
}
}
};
--------------------------------------------------------------------------------
Deployment and generation of custom functions, which take into account concurrency, we still need a concurrency key to express the current query
--------------------------------------------------------------------------------
Copy Code code as follows:
CREATE ASSEMBLY testforsqlclr from ' e:/sqlclrdata/testfun.dll ' with permission_set = UnSAFE;
--
Go
The CREATE FUNCTION dbo. Xfn_getprevmembervalue
(
@key nvarchar (255),
@initByDim nvarchar (255),
@currentValue nvarchar (255)
)
RETURNS nvarchar (255)
As EXTERNAL NAME testforsqlclr. [Userdefinedfunctions]. Getprevmembervalue
Go
The CREATE FUNCTION dbo. Xfn_initkey
(
@key nvarchar (255)
)
RETURNS bit
As EXTERNAL NAME testforsqlclr. [Userdefinedfunctions]. Initkey
Go
The CREATE FUNCTION dbo. Xfn_disposekey
(
@key nvarchar (255)
)
RETURNS bit
As EXTERNAL NAME testforsqlclr. [Userdefinedfunctions]. Disposekey
--------------------------------------------------------------------------------
So we can use the test script as follows, Xfn_getprevmembervalue is the function that gets the price last month.
--------------------------------------------------------------------------------
--Establish a test environment
Copy Code code as follows:
DECLARE @t table (
Regional [varchar] (4) COLLATE chinese_prc_ci_as NULL,
[Trademonth] [varchar] (7) COLLATE chinese_prc_ci_as NULL,
[Trademoney] [Float] Null
[Tradearea] [Float] Null
[Tradeprice] [Float] Null
)
INSERT INTO @t
Select ' Minhang ', ' 2007-03 ', ' 2125714.91 ', ' 241.65 ', ' 8796.67 ' union
Select ' Minhang ', ' 2007-04 ', ' 8408307.64 ', ' 907.32 ', ' 9267.19 ' union
Select ' Minhang ', ' 2007-05 ', ' 10230321.95 ', ' 1095.88 ', ' 9335.26 ' union
Select ' Pudong ', ' 2007-01 ', ' 12738432.25 ', ' 1419.05 ', ' 8976.73 ' union
Select ' Pudong ', ' 2007-02 ', ' 4970536.74 ', ' 395.49 ', ' 12568.05 ' union
Select ' Pudong ', ' 2007-03 ', ' 5985405.76 ', ' 745.94 ', ' 8023.98 ' union
Select ' Pudong ', ' 2007-04 ', ' 21030788.61 ', ' 1146.89 ', ' 18337.23 ' union
Select ' Putuo ', ' 2007-01 ', ' 1863896 ', ' 161.39 ', ' 11549.02 ' union
Select ' Putuo ', ' 2007-02 ', ' 1614015 ', ' 119.59 ', ' 13496.24 ' union
Select ' Putuo ', ' 2007-03 ', ' 1059235.19 ', ' 135.21 ', ' 7834 '
--Test statement
Copy Code code as follows:
DECLARE @key varchar (40)
DECLARE @b bit
Set @key = NEWID ()
Select @b= dbo. Xfn_initkey (@key)
Select area, Trademonth, Tradeprice, Lastmonthprice,
Cast (Round (tradeprice-lastmonthprice) * 100/lastmonthprice, 2) as varchar) + '% ' as chain from (
SELECT *, CAST (dbo. Xfn_getprevmembervalue (@key, area, Tradeprice) as float) as Lastmonthprice from @t
) T
Select @b= dbo. Xfn_disposekey (@key)
--The result
/*
Regional Trademonth Tradeprice Lastmonthprice chain
---- ---------- ---------------------- ---------------------- -----------
Minhang 2007-03 8796.67 NULL NULL
Minhang 2007-04 9267.19 8796.67 5.35%
Minhang 2007-05 9335.26 9267.19 0.73%
Pudong 2007-01 8976.73 NULL NULL
Pudong 2007-02 12568.05 8976.73 40.01%
Pudong 2007-03 8023.98 12568-36.16%
Pudong 2007-04 18337.23 8023.98 128.53%
Putuo 2007-01 11549.02 NULL NULL
Putuo 2007-02 13496.24 11549 16.86%
Putuo 2007-03 7834 13496.2-41.95%
*/
--------------------------------------------------------------------------------
This function is still relatively coarse, and if further refinement can be used to define how to obtain the method of the previous dimension. This is only cached in terms of query order. Interested friends can be perfected.
