Complex Windowed Sum - New Requirement

• j-1064772

  SSCertifiable

  Points: 5316

  More actions

  April 12, 2018 at 11:24 am

  #330381

  Greetings,
  Your assistance is kindly requested for the following problem

  Description :
  A series of transactions issuing (I) or redeeming (R) bonds.
  A customer may be issued bonds one or more times. The bonds are entered in an "account" called UnitNo.
  A customer may have more than one UnitNo.
  A customer may have bonds redeemed. The withdrawals are made from a specific UnitNo.
  The ordering of the transactions is critical, otherwise the percentages will mean nothing.

  The requirements are :
  1) A running total of bond quantities issues and redeemed
  2) The total quantities of each customer combing all of the customer's UnitNo's
  3) The percentage of the customers' quantities w/r to the running total of bonds in circulation
  4) Showing for one specific customer (a) the current total of that customer and (b) the percentage of his bond quantities w/r to the total running count.
  5) An additional third column showing in which fiscal year the transactions took place.

  All these have been made to work by Mr Luis Cazares yesterday.

  NEW REQUIREMENTS
  1) Columns C004TotalQty and Columns C004Percentage must be filled for all rows, not just on the lines where Holder is C004
  When a transaction does not involve the C004 Customer, the C004Percentage must be updated to account for the increase in the RunningTotal column.

  2) An additional column giving the running DISTINCT count of Customers with each transaction.
  - a customer with any number of UnitNo's is to be counted as a single customer along as one UnitNo has a non-zero quantity.
  (so if a new transaction creates a new UnitNo for a customer who already had one or more other UnitNo's, this customer still counts as 1 customer)
  - a customer must be removed from the running count when a transaction leaves that customer with ALL empty UnitNo's. i.e. when the CustomerTotal falls to 0.

  Am I being too ambitious trying to do this in the same SELECT statement ?

  Thanks in advance for your help

  Table creation - updated to reflect the new column CustomerRunningCount in the TestResults table
  -------------------
  
CREATE TABLE [dbo].[TestData]
(
    [TrxNo] [int] NOT NULL Primary Key,
    [TrxType] [varchar](1) NOT NULL,
    [UnitNo] [varchar](255) NOT NULL,
    [Customer] [varchar](10) NOT NULL,
    [TrxDate] [date] NOT NULL,
    [TrxQty] [int] NOT NULL
);

  CREATE TABLE [dbo].[TestResults]
  (
      [TrxNo] [int] NOT NULL Primary Key,
      [TrxType] [varchar](1) NOT NULL,
      [UnitNo] [varchar](255) NOT NULL,
      [Customer] [varchar](10) NOT NULL,
      [TrxDate] [date] NOT NULL,
      [TrxQty] [int] NOT NULL,

      [CustomerRunningCount] [int] NOT NULL DEFAULT 0,  -- <<<<<<<<<<< NEW COLUMN

      [CustomerTotal] [int] NOT NULL DEFAULT 0.0,
      [RunningTotal] [int] NOT NULL DEFAULT 0.0,
      [CustomerPercentage] [money] NOT NULL DEFAULT 0.0,
      [C0004TotalQty] [int] NOT NULL DEFAULT 0.0,
      [C0004Percentage] [money] NOT NULL DEFAULT 0.0,
      [FiscalYear] [int] NULL
  );

  -- Data Loading
  INSERT INTO [TestData]
  (
     [TrxNo],
     [TrxType],
     [UnitNo],
     [Customer],
     [TrxDate],
     [TrxQty]
  )
  SELECT 1, 'I', 'XBR-64 ', 'C0001' , '2004-07-06', 50000 UNION
  SELECT 2, 'I', 'XBR-63', 'C0002' , '2004-07-10', 125000 UNION
  SELECT 3, 'I', 'XBR-65', 'C0003' , '2004-07-19', 5000 UNION
  SELECT 4, 'I', 'XBR-66', 'C0004' , '2004-07-19', 70000 UNION
  SELECT 5, 'I', 'XBR-67', 'C0004' , '2004-07-19', 50000 UNION
  SELECT 6, 'I', 'XBR-68', 'C0005' , '2004-07-19', 50000 UNION
  SELECT 7, 'I', 'XBR-69', 'C0004' , '2004-09-25', 200000 UNION
  SELECT 8, 'I', 'XBR-70', 'C0004' , '2004-09-25', 100000 UNION
  SELECT 9, 'I', 'XBR-71 ', 'C0001', '2004-11-15', 80000 UNION
  SELECT 10, 'I', 'XBR-72', 'C0003' , '2004-12-10', 5000 UNION
  SELECT 11, 'I', 'XBR-75', 'C0007' , '2005-03-14', 15000 UNION
  SELECT 12, 'I', 'XBR-57', 'C0009' , '2005-04-01', 30000 UNION
  SELECT 13, 'I', 'XBR-37', 'C0007' , '2005-07-01', 25000 UNION
  SELECT 14, 'I', 'XBR-76', 'C0003' , '2005-07-26', 10000 UNION
  SELECT 15, 'I', 'XBR-77', 'C0010' , '2005-09-21', 25000 UNION
  SELECT 16, 'I', 'XBR-80', 'C0004' , '2005-10-21', 700000 UNION
  SELECT 17, 'I', 'XBR-78', 'C0003' , '2005-10-24', 10000 UNION
  SELECT 18, 'R', 'XBR-79', 'C0007' , '2005-10-24', -20000 UNION
  SELECT 19, 'R', 'XBR-79', 'C0007' , '2005-10-25', -20000 UNION
  SELECT 20, 'I', 'XBR-83', 'C0004' , '2006-03-13', 600000 UNION
  SELECT 21, 'R', 'XBR-57', 'C0009' , '2006-04-01', -30000 UNION
  SELECT 22, 'I', 'XBR-92', 'C0004' , '2008-02-21', 500000 UNION
  SELECT 23, 'I', 'XBR-93', 'C0004' , '2008-02-21', 500000

  -- Loading of the TestResults table
  INSERT INTO [TestResults]
  (
      [TrxNo],
      [TrxType],
      [UnitNo],
      [Customer],
      [TrxDate],
      [TrxQty],
      [CustomerRunningCount],
      [CustomerTotal],
      [RunningTotal],
      [CustomerPercentage],
      [C0004TotalQty],
      [C0004Percentage],
      [FiscalYear]
  )
  SELECT
      [TrxNo],
      [TrxType],
      [UnitNo],
      [Customer],
      [TrxDate],
      [TrxQty],
      -1 --   <<<< CANNOT MAKE THIS WORK    SUM([TrxQty]) OVER (PARTITION BY [Customer] ORDER BY [TrxNo]),
      SUM([TrxQty]) OVER (ORDER BY [TrxNo]),
      (SUM([TrxQty]) OVER (PARTITION BY [Customer] ORDER BY [TrxNo]) * 100) / SUM([TrxQty]) OVER (ORDER BY [TrxNo]),
      SUM(CASE WHEN Customer = 'C0004' THEN TrxQty ELSE 0 END)  OVER (PARTITION BY [Customer] ORDER BY [TrxNo]),
      (SUM(CASE WHEN Customer = 'C0004' THEN TrxQty ELSE 0 END) OVER (PARTITION BY [Customer] ORDER BY [TrxNo]) * 
            100.) / SUM([TrxQty]) OVER (ORDER BY [TrxNo]),
      YEAR(DATEADD(MM,-4,trxDate))
  FROM [TestData]
  ORDER BY [TrxNo]
  
  Expected Results
  
  

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 13, 2018 at 11:44 am

  #1986804

  I'm not sure your expected results are correct.   Please take a look at the following code and let me know where I may have messed up.   I had to make a change to the CustomerTotal column because it appears that the total should be based on the UnitNo as well as the customer, or is that intended to just be the total quantity for the customer to date?   In any case, I think the running count of customers you show isn't quite right.   I derived a column in a CTE that represents when you have to remove that customer from the running count, and the numbers it's generating appear to be right.   Let me know.   Here's the code: CREATE TABLE #TestData (
    TrxNo int NOT NULL PRIMARY KEY,
    TrxType varchar(1) NOT NULL,
  UnitNo varchar(255) NOT NULL,
  Customer varchar(10) NOT NULL,
  TrxDate date NOT NULL,
  TrxQty int NOT NULL
);
CREATE TABLE #TestResults (
    [TrxNo] [int] NOT NULL Primary Key,
    TrxType varchar(1) NOT NULL,
    UnitNo varchar(255) NOT NULL,
    Customer varchar(10) NOT NULL,
    TrxDate date NOT NULL,
    TrxQty int NOT NULL,
    CustomerRunningCount int NOT NULL DEFAULT 0, -- <<<<<<<<<<< NEW COLUMN
    CustomerTotal int NOT NULL DEFAULT 0.0,
    RunningTotal int NOT NULL DEFAULT 0.0,
    CustomerPercentage money NOT NULL DEFAULT 0.0,
    C0004TotalQty int NOT NULL DEFAULT 0.0,
    C0004Percentage money NOT NULL DEFAULT 0.0,
    FiscalYear int NULL
);

  -- Data Loading
INSERT INTO #TestData (TrxNo, TrxType, UnitNo, Customer, TrxDate, TrxQty)
    SELECT 1, 'I', 'XBR-64 ', 'C0001' , '2004-07-06', 50000 UNION
    SELECT 2, 'I', 'XBR-63', 'C0002' , '2004-07-10', 125000 UNION
    SELECT 3, 'I', 'XBR-65', 'C0003' , '2004-07-19', 5000 UNION
    SELECT 4, 'I', 'XBR-66', 'C0004' , '2004-07-19', 70000 UNION
    SELECT 5, 'I', 'XBR-67', 'C0004' , '2004-07-19', 50000 UNION
    SELECT 6, 'I', 'XBR-68', 'C0005' , '2004-07-19', 50000 UNION
    SELECT 7, 'I', 'XBR-69', 'C0004' , '2004-09-25', 200000 UNION
    SELECT 8, 'I', 'XBR-70', 'C0004' , '2004-09-25', 100000 UNION
    SELECT 9, 'I', 'XBR-71 ', 'C0001', '2004-11-15', 80000 UNION
    SELECT 10, 'I', 'XBR-72', 'C0003' , '2004-12-10', 5000 UNION
    SELECT 11, 'I', 'XBR-75', 'C0007' , '2005-03-14', 15000 UNION
    SELECT 12, 'I', 'XBR-57', 'C0009' , '2005-04-01', 30000 UNION
    SELECT 13, 'I', 'XBR-37', 'C0007' , '2005-07-01', 25000 UNION
    SELECT 14, 'I', 'XBR-76', 'C0003' , '2005-07-26', 10000 UNION
    SELECT 15, 'I', 'XBR-77', 'C0010' , '2005-09-21', 25000 UNION
    SELECT 16, 'I', 'XBR-80', 'C0004' , '2005-10-21', 700000 UNION
    SELECT 17, 'I', 'XBR-78', 'C0003' , '2005-10-24', 10000 UNION
    SELECT 18, 'R', 'XBR-79', 'C0007' , '2005-10-24', -20000 UNION
    SELECT 19, 'R', 'XBR-79', 'C0007' , '2005-10-25', -20000 UNION
    SELECT 20, 'I', 'XBR-83', 'C0004' , '2006-03-13', 600000 UNION
    SELECT 21, 'R', 'XBR-57', 'C0009' , '2006-04-01', -30000 UNION
    SELECT 22, 'I', 'XBR-92', 'C0004' , '2008-02-21', 500000 UNION
    SELECT 23, 'I', 'XBR-93', 'C0004' , '2008-02-21', 500000;

  WITH RunningCountValues AS (

      SELECT TD.TrxNo, TD.Customer,
        CASE
            WHEN SUM(TD.TrxQty) OVER(PARTITION BY TD.Customer, TD.UnitNo ORDER BY TD.TrxNo) = 0 AND X.PrevRowCustExists = 1 THEN -1
            WHEN SUM(TD.TrxQty) OVER(PARTITION BY TD.Customer, TD.UnitNo ORDER BY TD.TrxNo) <> 0 AND X.PrevRowCustExists = 0 THEN 1
            ELSE 0
        END AS RunningCustomer,
        X.PrevRowCustExists
    FROM #TestData AS TD
        CROSS APPLY (
            VALUES (CASE WHEN EXISTS (SELECT 1 FROM #TestData AS T2 WHERE T2.Customer = TD.Customer AND T2.TrxNo < TD.TrxNo) THEN 1 ELSE 0 END)
            ) AS X(PrevRowCustExists)
    --ORDER BY TD.TrxNo
)
-- Loading of the TestResults table
INSERT INTO #TestResults (
    TrxNo, TrxType, UnitNo, Customer, TrxDate, TrxQty, CustomerRunningCount, CustomerTotal,
    RunningTotal, CustomerPercentage, C0004TotalQty, C0004Percentage, FiscalYear)
SELECT
    T.TrxNo,
    T.TrxType,
    T.UnitNo,
    T.Customer,
    T.TrxDate,
    T.TrxQty,
    SUM(RCV.RunningCustomer) OVER(ORDER BY T.TrxNo) AS CustomerRunningCount,
    --COUNT(DISTINCT Customer) OVER(ORDER BY TrxNo) AS CustomerRunningCount, -- <<<< CANNOT MAKE THIS WORK - SQL WON'T ALLOW IT.
    SUM(T.TrxQty) OVER (PARTITION BY T.Customer, T.UnitNo ORDER BY T.TrxNo) AS CustomerTotal,    -- CHANGED to add UnitNo into the mix.
    SUM(T.TrxQty) OVER (ORDER BY T.TrxNo) AS RunningTotal,
    (SUM(T.TrxQty) OVER (PARTITION BY T.Customer ORDER BY T.TrxNo) * 100) / SUM(T.TrxQty) OVER (ORDER BY T.TrxNo) AS CustomerPercentage,
    SUM(CASE WHEN T.Customer = 'C0004' THEN T.TrxQty ELSE 0 END) OVER (PARTITION BY T.Customer ORDER BY T.TrxNo) AS C0004TotalQty,
    (SUM(CASE WHEN T.Customer = 'C0004' THEN T.TrxQty ELSE 0 END) OVER (PARTITION BY T.Customer ORDER BY T.TrxNo) * 100.) /
        SUM(T.TrxQty) OVER (ORDER BY T.TrxNo) AS C0004Percentage,
    YEAR(DATEADD(MM, -4, T.trxDate)) AS FiscalYear
FROM #TestData AS T
    INNER JOIN RunningCountValues AS RCV
        ON T.TrxNo = RCV.TrxNo
ORDER BY T.TrxNo;

  SELECT *
FROM #TestResults
ORDER BY TrxNo;

  DROP TABLE #TestData;
DROP TABLE #TestResults;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• j-1064772

  SSCertifiable

  Points: 5316

  More actions

  April 13, 2018 at 12:53 pm

  #1986823

  The CustomerTotal is the "to-date" value, i.e. the current sum of all quantities in all units up till the current transaction.

  If an existing customer acquires a second UnitNo, the customer count is not increased.

  A customer remains in the CustomerCount until the sum of all its UnitNo's is brought down to 0 by a transaction. Then the count has to decrease by 1.

  On line 18, C007 previously had 40,000 units (15,000 + 25,000) so subtracting 20,000 leaves him with 20,000 units
  On line 19 C007 loses again 20,000 and its total falls down to zero.

  Your help is greatly appreciated.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 16, 2018 at 7:00 am

  #1986908

  j-1064772 - Friday, April 13, 2018 12:53 PM

  The CustomerTotal is the "to-date" value, i.e. the current sum of all quantities in all units up till the current transaction.

  If an existing customer acquires a second UnitNo, the customer count is not increased.

  A customer remains in the CustomerCount until the sum of all its UnitNo's is brought down to 0 by a transaction. Then the count has to decrease by 1.

  On line 18, C007 previously had 40,000 units (15,000 + 25,000) so subtracting 20,000 leaves him with 20,000 units
  On line 19 C007 loses again 20,000 and its total falls down to zero.

  Your help is greatly appreciated.

  Yes, but... those are units of different issue.   You can't subtract some number of issue X from the same number of issue Y and say that person actually has 0 units of any issue.  Or is there something about this that I am entirely missing?

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• j-1064772

  SSCertifiable

  Points: 5316

  More actions

  April 16, 2018 at 2:05 pm

  #1987053

  The sum of quantities encompasses all the UnitNo's of a customer in one single Total Qty for each customer, not separate sums for each individual UnitNo.

  I would expect the person who creates the transaction is bright enough to to know better than keeping removing quantities from an already depleted UnitNo, resulting in negative quantities.

  Sorry I could not answer earlier, I've been really sick. How ironic for me your tag line about "health and nutrition" ...
  Regards

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 16, 2018 at 2:23 pm

  #1987058

  j-1064772 - Monday, April 16, 2018 2:05 PM

  The sum of quantities encompasses all the UnitNo's of a customer in one single Total Qty for each customer, not separate sums for each individual UnitNo.

  I would expect the person who creates the transaction is bright enough to to know better than keeping removing quantities from an already depleted UnitNo, resulting in negative quantities.

  Sorry I could not answer earlier, I've been really sick. How ironic for me your tag line about "health and nutrition" ...
  Regards

  Sorry to hear that you've been sick.   Hopefully, things will improve for you soon.

  As to your sample data: Customer C007 adds units to XBR-75 and XBR-37, but then redeems entirely from XBR-79.  Additionally, there aren't enough units in either one that's added to initially, to then be able to make both 20,000 unit redemptions.   Thus my conclusion is that the sample data is either flawed or incomplete, making it inadequate to properly test a solution.  If you can correct that, I can fix it easily enough.

  Interesting coincidence, that irony.   I've had that signature here for quite a few years now.   If that link in my signature generates questions, feel free to private message me here and I'll get you answers.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)