![]() ![]() Here is a query that I have gotten helped with, thats very close to the answer but is missing the period 0 and the balance of 14 period per partition. Please note that in the image above I have 2 periods missing (13,14) for partition fund = A, FY = 12, type = 4. Here is an example of the expected output. In sudo code these are steps that I would essentially execute in PLSQL.ġ- Get min fy, max fy, min fp, maxfp year per partition.Ģ - if their are period gaps between the min period and 0 than insert 0 amounts and the necessary periods until we reach the minimum period and minum year.ģ - if their is not 14 periods in this min year start a running sum insert the missing periods and keep a running sum of all those periods amounts. So taking a closer look at the data you will notice that in FY 15 the max period is 04 so i must add another 12 periods to have exactly 14 periods, I would need to back fill fp 0-2 with the amount of "0" in addition to 5-14 with the running sum per partition. This is specific to an accounting functions. ![]() Though there are not 14 months in a year fp =14 is simply December 3 times. i.e 2 equals February and 3 equals march etc.) to a hard number of 14. ![]() REM INSERTING into LEDGERSET DEFINE OFF Insert into LEDGER (FY,FP,FUND,TYPE,AMT) values (15,'03','A','03',1) Insert into LEDGER (FY,FP,FUND,TYPE,AMT) values (15,'04','A','03',2) Insert into LEDGER (FY,FP,FUND,TYPE,AMT) values (16,'04','A','03',3) Insert into LEDGER (FY,FP,FUND,TYPE,AMT) values (12,'05','A','04',6) īased on the partition of fy, fp, fund and type I would like to write a query to keep a running sum from the beginning of fp,fy (fp though it is a VARCHAR it represents a number in the month. 1.7K Training / Learning / CertificationĬREATE TABLE "LEDGER" ("FY" NUMBER, "FP" VARCHAR2(20 BYTE), "FUND" VARCHAR2(20 BYTE), "TYPE" VARCHAR2(2 BYTE), "AMT" NUMBER ).165.3K Java EE (Java Enterprise Edition).7.9K Oracle Database Express Edition (XE).3.8K Java and JavaScript in the Database. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |