locked
Cummulative value issue in the Query RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote 
     SELECT 
         a.[ACCOUNT_KEY],
         a.PERIOD_Key,
         a.[COST_CENTRE_KEY],
		 a.[PROFIT_CENTRE_KEY],
         a.[PI-CODE_KEY],
           a.[PROJECT_CODE_KEY],
           a.[ENTRY_DESCRIPTOR_KEY],
           a.[BUSINESS_ENTITY_KEY],
           a.[COST_ITEM_LEVEL_KEY],
           a.[SOURCE_SYSTEM_KEY],
		   a.CURRENCY_KEY,
         ISNULL(a.[NET_AMOUNT],0) AS [NET_AMOUNT],
         (SUM(b.[NET_AMOUNT]) - a.[NET_AMOUNT])   AS [BEGINBALANCE] 
     


		 INTO #RetainedProfit1
             FROM  #Stage_Sage_ADPPCProjectTransactions a
             JOIN  #GLACCOUNTS GL
                      ON a.[ACCOUNT_KEY] = GL.[ACCOUNT_KEY]
          LEFT JOIN   #Stage_Sage_ADPPCProjectTransactions b
         ON  A.[PERIOD_Key] >= B.[PERIOD_KEY]
             AND A.[ACCOUNT_KEY]      = B.[ACCOUNT_KEY]
			 AND A.[COST_CENTRE_KEY]  = B.[COST_CENTRE_KEY]
			 AND A.[PI-CODE_KEY]      = B.[PI-CODE_KEY]
			 AND A.[PROJECT_CODE_KEY] = B.[PROJECT_CODE_KEY]

		 AND A.[BUSINESS_ENTITY_KEY]    = B.[BUSINESS_ENTITY_KEY]
         AND A.[COST_ITEM_LEVEL_KEY]    = B.[COST_ITEM_LEVEL_KEY]
		 AND A.[ENTRY_DESCRIPTOR_KEY]   = B.[ENTRY_DESCRIPTOR_KEY]
		 AND A.[SOURCE_SYSTEM_KEY]      = B.[SOURCE_SYSTEM_KEY]

		  AND A.CURRENCY_KEY             = B.CURRENCY_KEY
		 AND  A.[PROFIT_CENTRE_KEY]      = B.[PROFIT_CENTRE_KEY]
		 

		         	 		 	    JOIN [MGP_BI_MiWorldDW].dbo.DIM_DATE D 
            ON a.[PERIOD_Key] = D.DateKey
     JOIN [MGP_BI_MiWorldDW].dbo.Dim_Date D1 
            ON b.[PERIOD_Key] = D1.DateKey
            AND (D.FiscalYear = D1.FiscalYear )  
                       
        GROUP BY 
         a.[ACCOUNT_KEY],
         a.PERIOD_Key,
         a.[COST_CENTRE_KEY],
         a.[PI-CODE_KEY],
           a.[PROJECT_CODE_KEY],
           a.[ENTRY_DESCRIPTOR_KEY],
           a.[BUSINESS_ENTITY_KEY],
           a.[COST_ITEM_LEVEL_KEY],
           a.[SOURCE_SYSTEM_KEY],
		   a.[NET_AMOUNT],
		   a.CURRENCY_KEY,
	          a.[PROFIT_CENTRE_KEY]

    I am not getting correct values for BeginBalance column... because the same ENTRY_DESCRIPTOR_KEY is not repeating in the next month, When it comes to 2nd month if it can't find the previous month ENTRY_DESCRIPTOR_KEY then it is skipping in the 2nd month sum, in result of this I am getting incorrect value in the beginbalance column for 2nd month.. like this happening to all the following months... Can you please correct me where I am doing wrong.

    Thank you in advance

    Wednesday, October 25, 2017 4:43 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    You need to provide us some sample data to make us understand how sequence is maintained for the data for subsequent months. Otherwise its not easy for someone to help you out

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, October 25, 2017 6:07 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for your reply.

    in the month 20170501 it is missing 7065.37 in the begin balance, in result of that getting incorrect value in the beginbalance. Begin balance is always previous month NetAmount. I hope this help you to help me. Thank you in advance

    Wednesday, October 25, 2017 6:43 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for your reply.

    in the month 20170501 it is missing 7065.37 in the begin balance, in result of that getting incorrect value in the beginbalance. Begin balance is always previous month NetAmount. I hope this help you to help me. Thank you in advance

    Sorry still not clear whats the key columns based on which the two tables are related. Also I dont see all entries in the second table so you need to tell us why it was left and what are the rules behind

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, October 25, 2017 6:55 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Based on your screenshot, they don't have the same ENTRY_DESCRIPTOR_KEY. The ENTRY_DESCRIPTOR_KEY values are 1, 10 and 11. Right? So what did you mean about "because the same ENTRY_DESCRIPTOR_KEY is not repeating in the next month" in your initial post? 

    And your information is not enough for us to understand your requirement. At least, please share us some sample data with Create and Insert statements. Which we can use it to run your query and make some test. It is hard for us to find the issue and provide some solutions with only screenshots.

    Thanks,
    Xi Jin.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 26, 2017 2:31 AM