locked
How to calculative cumulative totals within a script component RRS feed

  • Question

  • How to calculative cumulative totals within a script component.

    Here is a problem I am trying to work through.

    1. An OLE DB Source Data Flow Source uses the following SQL:

    SELECT Customer_ID, Date_Key, Dollars
    ORDER BY Customer_ID, Date_Key;

    The following would be a sample result set:

    Customer_ID, Date_Key, Dollars
    A, 20130527, 1000
    A, 20130528, 1000
    A, 20130529, 1000
    A, 20130530, 1000
    A, 20130531, 1000
    B, 20130527, 1000
    B, 20130528, 1000
    B, 20130529, 1000
    B, 20130530, 1000
    B, 20130531, 1000

    2. Then, using a Script Component Data Flow Transformation, I would like to do the following:

    For each record, calculate the cumulative dollars starting on the first date_key for a customer.

    So the output of the script task would look something like this:

    Customer_ID, Date_Key, Dollars, Cumulative_Dollars
    A, 20130527, 1000, 1000
    A, 20130528, 1000, 2000
    A, 20130529, 1000, 3000
    A, 20130530, 1000, 4000
    A, 20130531, 1000, 5000
    B, 20130527, 1000, 1000
    B, 20130528, 1000, 2000
    B, 20130529, 1000, 3000
    B, 20130530, 1000, 4000

    I have no idea how to do step #2 in a Script Component. Any ideas?  

    Thanks!!!!!!!
    Friday, May 31, 2013 9:48 PM

Answers

All replies

  • Hi,

    Why do you want use script component for this,try like below t-sql code sample

    DECLARE @Customer TABLE (Customer_ID CHAR(1),Date_Key VARCHAR(20),Dollars BIGINT)
    INSERT @Customer SELECT  'A', '20130527', 1000
    INSERT @Customer SELECT  'A', '20130528', 1000
    INSERT @Customer SELECT  'A', '20130529', 1000
    INSERT @Customer SELECT  'A', '20130530', 1000
    INSERT @Customer SELECT  'A', '20130531', 1000
    INSERT @Customer SELECT  'B', '20130527', 1000
    INSERT @Customer SELECT  'B', '20130528', 1000
    INSERT @Customer SELECT  'B', '20130529', 1000
    INSERT @Customer SELECT  'B', '20130530', 1000
    INSERT @Customer SELECT  'B', '20130531', 1000
    ; WITH mycte AS
    (SELECT *, MAX(Customer_ID) OVER(ORDER BY Customer_ID) RN FROM @Customer)
    SELECT Customer_ID,
    Date_Key,
    Dollars,
    SUM(Dollars)OVER(partition BY RN ORDER BY Date_Key) AS Cumulative_Dollars
    FROM   mycte 


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Saturday, June 1, 2013 3:34 AM
  • Here is an example with a Script Component that you could use:
    http://microsoft-ssis.blogspot.com/2011/04/compare-values-of-two-rows.html



    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    • Edited by SSISJoostMVP Saturday, June 1, 2013 12:29 PM
    • Proposed as answer by Mike Yin Tuesday, June 4, 2013 9:27 AM
    • Marked as answer by Mike Yin Sunday, June 9, 2013 4:50 PM
    Saturday, June 1, 2013 12:28 PM
  • Hi Satya,

    The ans you have proposed will only work if the Requestor has SQL 2012 installed. This new new addition of ORDER BY clause in window functions really helps in performing running totals with single select statment but the user has requested for script component specifically.

    Lets assume, if he/she doesnt have SQL 2012, it will not work. May be you/we can give another T-SQL code for runnig totals that can run on SQL2005/2008/2008r2.


    Thanks, hsbal

    Wednesday, June 5, 2013 5:09 PM