This is my first psot so apologies if I,m a bit unclear or posted in the wrong category. Basically I have a database with 11 fields below and I want to create a new calculation field with the follwoing criteria:
When sum(WHO_CUST_NO) > 1 and Attrib_3 = on
then ACC 1(ACC_NET_BAL_FOR_PRNCPL_DR/sum(Acc 1 + Acc 2) + FAC_SUSP_INT_AMT_EUR)
AND ACC 2(ACC_NET_BAL_FOR_PRNCPL_DR/sum(Acc 1 + Acc 2) + FAC_SUSP_INT_AMT_EUR)
Note: above is assuming that there are 2 entries with the same WHO_CUST_NO but there are sinarios where there maybe numerous entries with the same WHO_CUST_NO and these will have to be aggregated out as above. I'm guessing I will have to create another table to do this and maybe join it with my original table?? I have created the originak table so I,m guessing that the 2 sinarios will be CASES?
When ACC_NET_BAL_FOR_PRNCPL_DR = 0 and SUM(WHO_CUST_NO) = 1 and and Attrib_3 = on
Any help on this would be very much apprceiated
CSUM is deprecated since years, but OLAP functions are probably the right way.
Could you post the DDL, some INSERTs and the desired output?
Hi Dieter and thanks for your reply, I have posted a snipet of the DB below as I,m not sure how to attach the DDL and the blank fields are the ones I want populated. Basically i want the number of WH_CUST_NOs attached to a particular FACILITY_NO as there are cases where there is more than one WH_CUST_NO related to a FACILITY_NO.
Where this is the case I would like each WH_CUST_NOs ACC_NET_BAL_FOR_PRNCPL_DR to be tfke as a percentage of the overall cumulative sum of ACC_NET_BAL_FOR_PRNCPL_DR in these accounts and then added to the the FAC_SUSP_INT_AMT_EUR in each case
I hope i've explained this Okay if not let me know and I will elaborate further and thanks again for your help its much appreciated
i still don't fully understand your requirements:
- a "cumulative sum" usually means a running total, but you probably want just a sum
- adding a value to a percentage is strange
But this should be close:
COUNT(*) OVER (PARTITION BY FACILITY_NO) AS NO_OF_ACCOUNTS_IN_FACILITY, -- number of rows per facility
OVER (PARTITION BY FACILITY_NO) -- group sum of all rows per facility
) + FAC_SUSP_INT_AMT_EUR