New_Calculation Field

Database
Highlighted
Enthusiast

New_Calculation Field

Hi All

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:

NEW_CALCUALTION

Sinario 1

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?

Sinario 2

When ACC_NET_BAL_FOR_PRNCPL_DR = 0 and SUM(WHO_CUST_NO) = 1 and and Attrib_3 = on

Then FAC_SUSP_INT_AMT_EUR

END NEW_CALCULATION(DECiMAL(18,2))




WH_ACC_NO  (Integer)
ACC_NO          (Char)
WH_CUST_NO  (Char)
FACILITY_NO  (Char)
ATTRIB_3   (Char)
FAC_SUSP_INT_AMT_EUR (Decimal)
ACC_SRCE_PROD_CDE_FK (Char)
FACILITY_BAL_SHEET_IND (Char)
SRCE_SYS (Char)
ACC_NET_BAL_FOR_PRNCPL_DR (Decimal) 
ACC_NET_BAL_PR_DR_ADJ_SUSP_INT (Decimal)

NEW_CALCULATION (Decimal)

Any help on  this would be very much apprceiated

Donal

6 REPLIES
Enthusiast

Re: New_Calculation Field

CSUM ?

Enthusiast

Re: New_Calculation Field

Thanks for your reply Jigar, I,m not too familar with how CSUM works do you have any examples of this similar to my query?

Junior Contributor

Re: New_Calculation Field

CSUM is deprecated since years, but OLAP functions are probably the right way.

Could you post the DDL, some INSERTs and the desired output?

Dieter

Enthusiast

Re: New_Calculation Field

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

Donal









WH_CUST_NO FACILITY_NO ACC_NET_BAL_FOR_PRNCPL_DR NO_OF_ACCOUNTS_IN_FACILITY FAC_SUSP_INT_AMT_EUR CUMULATIVE_ACC_NET_BAL_FOR_PRNCPL_DR +FAC_SUSP_INT_AMT_EUR
300076227 A0059108     -1,867,758.89   508,507.71  
300005655 I0009802     -8,694.64   414,713.87  
300069318 A0130604     -1,049,473.30   4,230.35  
300061662 D0037902     -760,039.08   10,664.97  
300061660 H0008503     -381,900.38   2,536.85  
300060988 I0025712     -67,722.53   -27,819.79  
300054700 H0004802     -17,718.36   815,709.41  
300073737 A0130707     -208,000.00   19,528.00  
300058425 C0003401     -12,150,687.96   467,680.80  
300004455 E0007305     -4,433,980.31   43,853.84  
300066648 A0086709     -119,390.24   376,947.27  
300062873 E0019805     -65,317.85   43,853.84  
300079442 E0021602     -5,089,751.47   35,421.36  
300058726 E0005102     -1,278,800.20   2,815.95  
300074275 A0093803     -506,810.67   78,940.36  
300054700 H0004802     -1,122.93   132,257.87  
300007141 E0011304     -17,957,620.02   83,708.20  
300059489 A0067103     -9,500,000.00   4,230.35  
300004781 A0032004     -240,000.00   43,823.42  
300006851 I0012702     -22,583.49   46,850.76  
300054700 H0004802     -1,187.73   4,781.46  
300005073 A0025502     -6,348.70   1,189,132.83  
300056988 A0062803     -325,000.00   14,365.71  
300002288 A0129805     -82,000.00   11,816.86  
400002081 C0012002     -8,000,000.00   2,066.66  
Junior Contributor

Re: New_Calculation Field

Hi Donal,

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:

SELECT 
WH_CUST_NO,
FACILITY_NO,
ACC_NET_BAL_FOR_PRNCPL_DR,
COUNT(*) OVER (PARTITION BY FACILITY_NO) AS NO_OF_ACCOUNTS_IN_FACILITY, -- number of rows per facility
FAC_SUSP_INT_AMT_EUR,
(ACC_NET_BAL_FOR_PRNCPL_DR
/ SUM(ACC_NET_BAL_FOR_PRNCPL_DR)
OVER (PARTITION BY FACILITY_NO) -- group sum of all rows per facility
) + FAC_SUSP_INT_AMT_EUR
FROM dropme

Dieter

Enthusiast

Re: New_Calculation Field

Hi Dieter

Yes this is exactly what I,m looking for, thanks so much for your help

Regards

Donal