Sql-server – Update incoming rows based on % calculation

sql-server-2008

Below is the business requirement which I have to achieve…

As a Sales Data Operations process/product owner, I want to be able to identify share class conversions (aka "wash trades") coded as purchases and redemption and research whether the transaction types of the trades need to be updated to exchanges in and exchanges out, respectively.

Share class conversion ("wash trades") is defined as a purchase and redemption trade where the system source, account number, product name, and settle date match and the trade amount is within +/- 5% threshold. The scope pertains to non-omnibus trades.

Rule:

If 'Channel' is not equal to "Omnibus" where the 'Trans Description' is equal to "Purchase" and "Redemption" for one purchase and one redemption that match on 'System' , 'Account TA Number' , 'Product Name' , 'Settled Date' , and where the 'Trade Amount' of the purchase and redemption is within 5%, then display the potential wash trades on the dashboard for NB Sales Data Analyst review.

— Display the above set of records

If deemed wash trades, allow user to update the purchase and redemption pair 'Transaction Code Override Description' from "Purchase" to "Exchange In" and 'Trans Description' from "Redemption" with "Exchange Out".

— In case business agrees that they are washed, then I have to update those records.

CREATE TABLE [dbo].[fact](
[System] [nvarchar](255) NULL,
[Channel] [nvarchar](255) NULL,
[Product Name] [nvarchar](255) NULL,
[Product Share Class] [nvarchar](255) NULL,
[Trade ID] [float] NULL,
[Settled Date] [datetime] NULL,
[Account TA Number] [nvarchar](50) NULL,
[Trans Description] [nvarchar](255) NULL,
[Trade Amount] [money] NULL,
) ON [PRIMARY]

INSERT INTO [SRDB].[dbo].[fact]
           ([System]
           ,[Channel]
           ,[Product Name]
           ,[Product Share Class]
           ,[Trade ID]
           ,[Settled Date]
           ,[Account TA Number]
           ,[Trans Description]
           ,[Trade Amount]
        )
     VALUES
           ('SCHWAB'
           ,'US-ASG'
           ,'US MF'
           ,'Trust'
           ,'123456'
           ,'2015-01-30'
           ,'1111111'
           ,'Purchase'
           ,$ 2568,458.15)
GO

Sample Data Set.

System  Channel Product Name    Product Share Class Trade ID    Settled Date    Account TA Number   Trans Description    Trade Amount 
SCHWABPORTAL    US - ASG    Strategic Income Fund   A   29806259    30-Jan-15   000BY00F2RW Redemption   $        2,568,458.15
SCHWABPORTAL    US - ASG    Strategic Income Fund   Institutional   29806263    30-Jan-15   000BY00F2RW Purchase     $        2,568,488.04
DST             US - ASG    Socially Responsive Fund    Institutional   29711418    21-Jan-15   8010000418  Redemption   $        9,982,249.13
DST             US - ASG    Socially Responsive Fund    R6  29712099    21-Jan-15   1283100037  Purchase     $      10,057,328.32
DST             US - ASG    Socially Responsive Fund    Trust   29459917    2-Jan-15    5870002374  Redemption   $      13,985,707.98
DST             US - ASG    Socially Responsive Fund    Institutional   29459703    2-Jan-15    8010000338  Purchase     $      13,296,637.40
SCHWABPORTAL    US - ASG    High Income Bond Fund   Institutional   25615910    16-Jan-14   000BY00F351 Redemption   $      83,711,868.09
SCHWABPORTAL    US - ASG    High Income Bond Fund   R6  25615963    16-Jan-14   000BY00F351 Purchase     $      83,711,868.09

Desired Result Set

System  Channel Product Name    Product Share Class Trade ID    Settled Date    Account TA Number   Trans Description    Trade Amount   Wash Trades
SCHWABPORTAL    US - ASG    Strategic Income Fund   A   29806259    30-Jan-15   000BY00F2RW Redemption   $        2,568,458.15  Yes
SCHWABPORTAL    US - ASG    Strategic Income Fund   Institutional   29806263    30-Jan-15   000BY00F2RW Purchase     $        2,568,488.04  Yes
DST US - ASG    Socially Responsive Fund    Institutional   29711418    21-Jan-15   8010000418  Redemption   $        9,982,249.13  NO
DST US - ASG    Socially Responsive Fund    R6  29712099    21-Jan-15   1283100037  Purchase     $      10,057,328.32   NO
DST US - ASG    Socially Responsive Fund    Trust   29459917    2-Jan-15    5870002374  Redemption   $      13,985,707.98   NO
DST US - ASG    Socially Responsive Fund    Institutional   29459703    2-Jan-15    8010000338  Purchase     $      13,296,637.40   NO
SCHWABPORTAL    US - ASG    High Income Bond Fund   Institutional   25615910    16-Jan-14   000BY00F351 Redemption   $      83,711,868.09   Yes
SCHWABPORTAL    US - ASG    High Income Bond Fund   R6  25615963    16-Jan-14   000BY00F351 Purchase     $      83,711,868.09   Yes

Best Answer

The desired output can be achieved using a union of recursive joins on the same table. It's a bit ugly, but the following query will do it. The first SELECT returns Purchase transactions, the second SELECT returns Redemption transactions, and the third SELECT returns the rows that aren't part of a transaction pair.

SELECT
    fact.[System]
    ,fact.[Channel]
    ,fact.[Product Name]
    ,fact.[Product Share Class]
    ,fact.[Trade ID]
    ,fact.[Settled Date]
    ,fact.[Account TA Number]
    ,fact.[Trans Description]
    ,fact.[Trade Amount]
    ,CASE WHEN rfact.[Trade Amount]/fact.[Trade Amount] BETWEEN 0.95 AND 1.05 THEN 'Yes' ELSE 'No' END
        AS [Wash Trades]
FROM [dbo].fact
INNER JOIN [dbo].fact AS rfact
ON fact.[System] = rfact.[System]
AND fact.[Account TA Number] = rfact.[Account TA Number]
AND fact.[Product Name] = rfact.[Product Name]
AND fact.[Settled Date] = rfact.[Settled Date]
WHERE fact.[Channel] != 'Omnibus'
AND rfact.[Channel] != 'Omnibus'
AND fact.[Trans Description] = 'Purchase'
AND rfact.[Trans Description] = 'Redemption'
UNION ALL
SELECT
    fact.[System]
    ,fact.[Channel]
    ,fact.[Product Name]
    ,fact.[Product Share Class]
    ,fact.[Trade ID]
    ,fact.[Settled Date]
    ,fact.[Account TA Number]
    ,fact.[Trans Description]
    ,fact.[Trade Amount]
    ,CASE WHEN pfact.[Trade Amount]/fact.[Trade Amount] BETWEEN 0.95 AND 1.05 THEN 'Yes' ELSE 'No' END
        AS [Wash Trades]
FROM [dbo].fact
INNER JOIN [dbo].fact AS pfact
ON pfact.[System] = fact.[System]
AND pfact.[Account TA Number] = fact.[Account TA Number]
AND pfact.[Product Name] = fact.[Product Name]
AND pfact.[Settled Date] = fact.[Settled Date]
WHERE fact.[Channel] != 'Omnibus'
AND pfact.[Channel] != 'Omnibus'
AND fact.[Trans Description] = 'Redemption'
AND pfact.[Trans Description] = 'Purchase'
UNION ALL
SELECT
    fact.[System]
    ,fact.[Channel]
    ,fact.[Product Name]
    ,fact.[Product Share Class]
    ,fact.[Trade ID]
    ,fact.[Settled Date]
    ,fact.[Account TA Number]
    ,fact.[Trans Description]
    ,fact.[Trade Amount]
    ,'No' AS [Wash Trades]
FROM [dbo].fact
WHERE fact.[Trade ID] IN (
    SELECT
        MAX(fact.[Trade ID]) AS [Trade ID]
    FROM [dbo].fact
    GROUP BY fact.[Account TA Number], fact.[Settled Date]
    HAVING COUNT(fact.[Trade ID]) = 1 )
AND fact.[Channel] != 'Omnibus'
ORDER BY [Account TA Number], [Trans Description]

Result set:

System  Channel Product Name    Product Share Class Trade ID    Settled Date    Account TA Number   Trans Description   Trade Amount    Wash Trades
SCHWABPORTAL    US - ASG    Strategic Income Fund   Institutional   29806263    2015-01-30 00:00:00.000 000BY00F2RW Purchase    2568488.04  Yes
SCHWABPORTAL    US - ASG    Strategic Income Fund   A   29806259    2015-01-30 00:00:00.000 000BY00F2RW Redemption  2568458.15  Yes
SCHWABPORTAL    US - ASG    High Income Bond Fund   R6  25615963    2014-01-16 00:00:00.000 000BY00F351 Purchase    83711868.09 Yes
SCHWABPORTAL    US - ASG    High Income Bond Fund   Institutional   25615910    2014-01-16 00:00:00.000 000BY00F351 Redemption  83711868.09 Yes
DST US - ASG    Socially Responsive Fund    R6  29712099    2015-01-21 00:00:00.000 1283100037  Purchase    10057328.32 No
DST US - ASG    Socially Responsive Fund    Trust   29459917    2015-01-02 00:00:00.000 5870002374  Redemption  13985707.98 No
DST US - ASG    Socially Responsive Fund    Institutional   29459703    2015-01-02 00:00:00.000 8010000338  Purchase    13296637.40 No
DST US - ASG    Socially Responsive Fund    Institutional   29711418    2015-01-21 00:00:00.000 8010000418  Redemption  9982249.13  No

This is only my opinion, but I find that viewing data in pairs or groups of rows pyschologically separates the transactions and makes them more difficult to comprehend. If I were viewing this data in a dashboard, I'd like it to be as concise as possible with one row per pair of transactions giving all the necessary details of both. That can be accomplished with the following, which uses a CTE to select Purchases and joins back to the fact table selecting Redemptions:

WITH Purchases AS (
SELECT
    fact.[System]
    ,fact.[Channel]
    ,fact.[Product Name]
    ,fact.[Product Share Class]
    ,fact.[Trade ID] AS [Purchase ID]
    ,fact.[Settled Date]
    ,fact.[Account TA Number]
    ,fact.[Trade Amount] AS [Purchase Amount]
FROM [dbo].fact
WHERE [Channel] != 'Omnibus'
AND [Trans Description] = 'Purchase' )

SELECT
    pfact.[Purchase ID]
    ,rfact.[Trade ID] AS [Redemption ID]    
    ,rfact.[System]
    ,rfact.[Channel]
    ,rfact.[Product Name]
    ,pfact.[Product Share Class] AS [Purchase Share Class]
    ,rfact.[Product Share Class] AS [Redemption Share Class]
    ,rfact.[Settled Date]
    ,rfact.[Account TA Number]
    ,pfact.[Purchase Amount]
    ,rfact.[Trade Amount] AS [Redemption Amount]
    ,pfact.[Purchase Amount]/rfact.[Trade Amount] AS [Transaction Ratio]
FROM [dbo].fact AS rfact
INNER JOIN Purchases AS pfact
ON pfact.[System] = rfact.[System]
AND pfact.[Account TA Number] = rfact.[Account TA Number]
AND pfact.[Product Name] = rfact.[Product Name]
AND pfact.[Settled Date] = rfact.[Settled Date]
AND pfact.[Purchase Amount]/rfact.[Trade Amount] BETWEEN 0.95 AND 1.05
WHERE rfact.[Channel] != 'Omnibus'
AND rfact.[Trans Description] = 'Redemption'

This returns only the trades that meet the requirements for a "wash trade", with each pair as a single row but all necessary data for the update. Output below, formatted with extra tabs for readability:

Purchase ID Redemption ID   System          Channel     Product Name            Purchase Share Class    Redemption Share Class  Settled Date            Account TA Number   Purchase Amount     Redemption Amount   Transaction Ratio
29806263    29806259        SCHWABPORTAL    US - ASG    Strategic Income Fund   Institutional           A                       2015-01-30 00:00:00.000 000BY00F2RW         2568488.04          2568458.15          1.00
25615963    25615910        SCHWABPORTAL    US - ASG    High Income Bond Fund   R6                      Institutional           2014-01-16 00:00:00.000 000BY00F351         83711868.09         83711868.09         1.00

Related Solutions

Sql-server – How to calculate values based on the previous row after skipping the first 12 rows

So this isn't a great answer, this is kind of a starting answer for somebody else to take on and refine this better. But I'll make a stab at it.

First I have a question: Are you trying to retain this in a view? I don't think you can for what you're wanting to do, it's kinda complicated, so let's examine the operations that you need to do to actually do what you want.

You stated that you want the first 12 rows to be static every time, and they should always have their last column set as NULL, and the others should retain their value. So that's a business rule that we need to encode in SQL. But before we encode this as a rule, let's ask if there's a way to ENSURE that those 12 rows are the RIGHT rows every time. If we can make that assumption, then we can do this as part of the next step.

You're next requirement is to do a calculation on each row with the previous row. Since the first 12 rows are static (and I presume not calculated) then we don't have to ask "what about the first row". So the easiest way to do calculations on the previous row is to assign a rownum to each row, then use the rownum ID in a comparison. This meshes with the previous requirement.

So we should start by doing our select and assigning a rownum as well, like this:

SELECT     
    ROW_NUMBER() OVER (ORDER BY in.I_Date) AS rownum,
    in.I_Date  ,--Date
    in.I_O_P   ,--Money
    in.I_O_H   ,--Money
    in.I_O_L   ,--Money
    in.I_C_O   ,--Money 
    c.AMPS12_C  --Money
    CAST(0.0 AS Money) AS C12WR
FROM
    dbo.IC_Raw_In in
INNER JOIN 
    dbo.AMPS12_C c ON in.I_Serial = c.i_serial

But for the way I would do this, I would funnel these values into a temp table, and then use that to work out what I need. That way you can just refer to the columns in subsequent calls, like this:

UPDATE t 
SET C12WR = NULL
FROM temptable t
WHERE t.rownum < 12 -- see how we set the values = null here?

UPDATE t 
SET C12WR = 510.3958
FROM temptable t
WHERE t.rownum = 12 -- see how we set the value to something static? 
                    -- If this were a stored procedure we could use a value passed in here

and then we continue with:

UPDATE t 
SET C12WR = ( ( t2.C12WR * 11.0 ) + t.I_C_O ) / 12.0
FROM temptable t
INNER JOIN temptable t2 ON t.rownum = (t2.rownum - 1) -- this let's us get the previous row
WHERE t.rownum > 12

Using this logic: After the 13th row, the C12WR column = (prevrow.C12WR * 11 + currow.I_C_O Column) / 12

And then you would just return the values that you wanted from the temptable.

Notice: the things I left off. I did not define the temp table, I did not get rid of the temptable. I did not use appropriate syntax for the temptable addressing. I did not validate anything. I presumed that this was going to be used in a stored procedure. I did not illustrate how to use the static value as a stored procedure passed parameter.

Hope this helps. Hope someone else helps make this a better answer ;)

Sql-server – Update Database Fields Based On Date

As Martin Smith said in the comments, there is no Date/Time based trigger, therefore you have two options.

Batch Update - Use a batch process to update the data. This requires determining how often the data needs to change and then creating a batch process that runs at least that often to update the data. If the information changes once a day this might be acceptable, but as the update need gets more frequent this solution becomes less usable.
Dynamic Evaluation - As Martin Smith suggested, determine availability when the information is requested. This can be in the select statement itself or you can use a computed column on the table. In either case the data will be as up to date as it can be.

Best Answer

Related Solutions

Sql-server – How to calculate values based on the previous row after skipping the first 12 rows

Sql-server – Update Database Fields Based On Date

Related Question