Please help win my bet with our DBA
in [DB2]
|
From: lenygold via DBMonster.com on 13 Jun 2008 16:33 Hi everyboidy. My DBA is saying that this problem can not be done by single query and it should be done in a COBOL programm. I bet him a dinner that it could be done in at least 3 different ways in a single query. So far i am done with 2 queries. Very intresting problem: Input Table: ---------------------------------------------- CASINO_ID | CUST_ID | TRAN_DATE | DEBT_AMNT| --------- -|----------|-----------|------------ CAS1 | 1 |2005-01-01 | 10 | CAS1 | 1 |2005-02-01 | 12 | CAS1 | 1 |2005-03-01 | 09 | CAS1 | 1 |2005-04-01 | 10 | CAS1 | 1 |2005-07-01 | 10 | CAS1 | 1 |2005-08-03 | 08 | CAS1 | 2 |2005-05-03 | 13 | CAS1 | 2 |2005-05-11 | 07 | CAS1 | 2 |2005-06-14 | 15 | CAS1 | 2 |2005-06-30 | 11 | CAS1 | 1 |2006-01-01 | 11 | CAS1 | 1 |2006-02-01 | 22 | CAS2 | 1 |2005-01-04 | 05 | CAS2 | 1 |2005-02-22 | 05 | CAS2 | 1 |2005-03-11 | 09 | CAS2 | 1 |2005-07-23 | 10 | CAS2 | 1 |2005-08-21 | 08 | CAS2 | 2 |2005-05-12 | 10 | CAS2 | 2 |2005-06-11 | 15 | CAS2 | 2 |2006-01-05 | 20 | CAS2 | 2 |2006-02-09 | 22 | CAS3 | 4 |2005-01-01 | 14 | CAS3 | 4 |2005-04-01 | 12 | CAS3 | 4 |2005-05-11 | 29 | CAS3 | 1 |2005-07-01 | 02 | CAS3 | 1 |2005-08-03 | 08 | CAS3 | 4 |2005-11-03 | 03 | CAS3 | 4 |2005-12-11 | 27 | CAS3 | 2 |2005-06-14 | 15 | CAS3 | 2 |2005-06-30 | 11 | CAS3 | 1 |2005 11-08 | 10 | CAS3 | 1 |2006-01-01 | 11 | CAS3 | 1 |2006-02-01 | 22 | ---------------------------------------------- EXPLANATION: WRITE A QUERY TO CALCULATE PLAYERS DEBT_POOLS AND SET UP PRIORITES FOR PAY- OFF BASED ON DATES OF LENDING. DEPT_POOL IS TOTAL LENDED AMOUNT BORROWED FROM CASINO DURING PERIOD WITHIN DATE RANGE 1 MONTH OR LESS. EVERY AT LEAST 1 MONTH GAPS MARKS END OF ONE DEBT_POOL AND THE BEGINING OF ANOTHER. THE ROOLS FOR ASSIGNING PRIORITIES: THE DEPT_POOL WITH EARLIEST DATE RANGE FOR ANY COMBINATION CASINO/PLAYER MUST HAVE PRIORITY 1. THE DEPT_POOL WITH LATEST DATE RANGE FOR ANY COMBINATION CASINO/PLAYER MUST HAVE LOWEST PRIORITY. THE USER MUST BE ABLE TO RETIRIEVE ANY PRIORITY DEBT_POOLS FOR ANY COMBINATION CASINO/PLAYER AS WELL ALL DEBT_POOLS FOR ALL CASINO NETWORK 1st query: WITH TA(CASINO_ID,CUST_ID,TRAN_DATE,DEBT_AMNT) AS (VALUES('CAS1', 1,'2005-01-01',10), ('CAS1', 1,'2005-02-01',12), ('CAS1', 1,'2005-03-01',9), ('CAS1', 1,'2005-04-01',10), ('CAS1', 1,'2005-07-01',10), ('CAS1', 1,'2005-08-03', 8), ('CAS1', 2,'2005-05-03',13), ('CAS1', 2,'2005-05-11',07), ('CAS1', 2,'2005-06-14',15), ('CAS1', 2,'2005-06-30',11), ('CAS1', 1,'2006-01-01',11), ('CAS1', 1,'2006-02-01',22), ('CAS2', 1,'2005-01-04',05), ('CAS2', 1,'2005-02-22',05), ('CAS2', 1,'2005-03-11',09), ('CAS2', 1,'2005-07-23',10), ('CAS2', 1,'2005-08-21',08), ('CAS2', 2,'2005-05-12',10), ('CAS2', 2,'2005-06-11',15), ('CAS2', 2,'2006-01-05',20), ('CAS2', 2,'2006-02-09',22), ('CAS3', 4,'2005-01-01',14), ('CAS3', 4,'2005-02-01',15), ('CAS3', 4,'2005-04-01',12), ('CAS3', 4,'2005-05-11',29), ('CAS3', 1,'2005-07-01',02), ('CAS3', 1,'2005-08-03',08), ('CAS3', 4,'2005-11-03',03), ('CAS3', 4,'2005-12-11',27), ('CAS3', 2,'2005-06-14',15), ('CAS3', 2,'2005-06-30',11), ('CAS3', 1,'2005-12-11',10), ('CAS3', 1,'2006-01-01',11), ('CAS3', 1,'2006-02-01',22)), TB (CASINO_ID,CUST_ID,TRAN_DATE,DEBT_AMNT) AS (SELECT CASINO_ID,CUST_ID,INT(SUBSTR(TRAN_DATE,1,4)||SUBSTR(TRAN_DATE,6,2)), SUM(DEBT_AMNT) FROM TA GROUP BY CASINO_ID,CUST_ID,INT(SUBSTR(TRAN_DATE,1,4)||SUBSTR(TRAN_DATE,6,2) )), TC(RN,CASINO_ID,CUST_ID,STARTDT,ENDDATE,DEBT_AMNT) AS (select ROW_NUMBER() OVER(PARTITION BY GAPFREE.CASINO_ID,GAPFREE.CUST_ID) AS RN, gapfree.CASINO_ID , gapfree.CUST_ID , gapfree.STARTDT , gapfree.ENDDATE , sum(data.DEBT_AMNT) as DEBT_AMNT from ( select r1.CASINO_ID, r1.CUST_ID, r1.TRAN_DATE as STARTDT , r2. TRAN_DATE as ENDDATE from TB as r1 inner join TB as r2 on r2.CASINO_ID = r1.CASINO_ID and r2.CUST_ID = r1.CUST_ID and r2.TRAN_DATE >= r1.TRAN_DATE WHERE not exists (select 1 from TB where CASINO_ID = r1.CASINO_ID and CUST_ID = r1.CUST_ID and TRAN_DATE IN ( r1.TRAN_DATE - 1 , r2.TRAN_DATE + 1 ) ) and (select count(*) from TB where CASINO_ID = r1.CASINO_ID and CUST_ID = r1.CUST_ID and TRAN_DATE between r1.TRAN_DATE and r2.TRAN_DATE ) = r2.TRAN_DATE - r1.TRAN_DATE + 1 ) as gapfree inner join TB as data on data.CASINO_ID = gapfree.CASINO_ID and data.CUST_ID = gapfree.CUST_ID and data.TRAN_DATE between gapfree.STARTDT and gapfree.ENDDATE group by gapfree.CASINO_ID , gapfree.CUST_ID , gapfree.STARTDT , gapfree. ENDDATE order by 2,3,4) SELECT CASINO_ID,CUST_ID,STARTDT,ENDDATE,DEBT_AMNT, REPLACE(STRIP(NUM_TO_CHAR(RN),B,'0'),'.','')||'PRIORITY DEBT' AS DEPT_PRIORITY FROM TC; CASINO_ID CUST_ID STARTDT ENDDATE DEBT_AMNT DEPT_PRIORITY --------- ----------- ----------- ----------- ----------- -------------------- --- CAS1 1 200501 200504 41 1PRIORITY DEBT CAS1 1 200507 200508 18 2PRIORITY DEBT CAS1 1 200601 200602 33 3PRIORITY DEBT CAS1 2 200505 200506 46 1PRIORITY DEBT CAS2 1 200501 200503 19 1PRIORITY DEBT CAS2 1 200507 200508 18 2PRIORITY DEBT CAS2 2 200505 200506 25 1PRIORITY DEBT CAS2 2 200601 200602 42 2PRIORITY DEBT CAS3 1 200507 200508 10 1PRIORITY DEBT CAS3 1 200511 200511 10 2PRIORITY DEBT CAS3 1 200601 200602 33 3PRIORITY DEBT CAS3 2 200506 200506 26 1PRIORITY DEBT CAS3 4 200501 200502 29 1PRIORITY DEBT CAS3 4 200504 200505 41 2PRIORITY DEBT CAS3 4 200511 200512 30 3PRIORITY DEBT 15 record(s) selected. 2nd query WITH T1(CASINO_ID,TRAN_DATE,DEBT_AMNT) AS (VALUES('CAS1 1','2005-01-01',10), ('CAS1 1','2005-02-01',12), ('CAS1 1','2005-03-01',9), ('CAS1 1','2005-04-01',10), ('CAS1 1','2005-07-01',10), ('CAS1 1','2005-08-03', 8), ('CAS1 2','2005-05-03',13), ('CAS1 2','2005-05-11',07), ('CAS1 2','2005-06-14',15), ('CAS1 2','2005-06-30',11), ('CAS1 1','2006-01-01',11), ('CAS1 1','2006-02-01',22), ('CAS2 1','2005-01-04',05), ('CAS2 1','2005-02-22',05), ('CAS2 1','2005-03-11',09), ('CAS2 1','2005-07-23',10), ('CAS2 1','2005-08-21',08), ('CAS2 2','2005-05-12',10), ('CAS2 2','2005-06-11',15), ('CAS2 2','2006-01-05',20), ('CAS2 2','2006-02-09',22), ('CAS3 4','2005-01-01',14), ('CAS3 4','2005-02-01',15), ('CAS3 4','2005-04-01',12), ('CAS3 4','2005-05-11',29), ('CAS3 1','2005-07-01',02), ('CAS3 1','2005-08-03',08), ('CAS3 4','2005-11-03',03), ('CAS3 4','2005-12-11',27), ('CAS3 2','2005-06-14',15), ('CAS3 2','2005-06-30',11), ('CAS3 1','2005-12-11',10), ('CAS3 1','2006-01-01',11), ('CAS3 1','2006-02-01',22)), T2 (KEY,BGN_DT,END_DT) AS (SELECT CASINO_ID,TRAN_DATE,TRAN_DATE FROM T1 ORDER BY 1,2), T3 (C1,C3,C4,C5,C6,C7) AS (SELECT A.KEY,A.BGN_DT,A.END_DT, B.BGN_DT,B.END_DT, DAYS(DATE(B.BGN_DT)) - DAYS(DATE(A.END_DT)) AS DIFF_IN_DAYS FROM T2 A, T2 B WHERE A.KEY = B.KEY AND DATE(A.END_DT) < DATE(B.BGN_DT) - 1 DAY AND NOT EXISTS (SELECT * FROM T2 Z WHERE Z.KEY = A.KEY AND Z.KEY = B.KEY AND Z.BGN_DT > A.BGN_DT AND Z.BGN_DT < B.BGN_DT) ORDER BY 1,3), T4(C1,C3,C4,C5,C6,C7) AS (SELECT C1 AS CASIN_ID,C3 AS BEG_DATE, QUARTER(DATE(C3)) AS BEG_Q,C6 AS END_DATE,QUARTER(DATE(C6)) AS END_Q, C7 AS DAY_DIFF FROM T3), T5(C1,C3,C5,C6) AS (SELECT C1,MIN(C3),MAX(C5), MAX(C6) FROM T4 WHERE C7 < 58 GROUP BY C1,SUBSTR(C3,1,4),C4), T6(RN,C1,C3,C5) AS (select ROW_NUMBER() OVER(PARTITION BY C1 ORDER BY C1 ),C1,C3,C5 FROM T5) SELECT C1 AS CASINO_CUST_ID, C3 AS STARTDT, C5 AS ENDDATE,DEBT_AMNT, REPLACE(STRIP(NUM_TO_CHAR(RN),B,'0'),'.','')||'PRIORITY DEBT' AS DEPT_PRIORITY FROM T6 A, TABLE(SELECT SUM(B.DEBT_AMNT) AS DEBT_AMNT FROM T1 B WHERE A.c1 = B.CASINO_ID AND B.TRAN_DATE BETWEEN A.c3 AND A.c5 GROUP BY B.CASINO_ID) AS T ORDER BY 1; CASINO_CUST_ID STARTDT ENDDATE DEBT_AMNT DEPT_PRIORITY -------------- ---------- ---------- ----------- ------------------------ CAS1 1 2005-01-01 2005-04-01 41 1 PRIORITY DEBT CAS1 1 2005-07-01 2005-08-03 18 2 PRIORITY DEBT CAS1 1 2006-01-01 2006-02-01 33 3 PRIORITY DEBT CAS1 2 2005-05-03 2005-06-30 46 1 PRIORITY DEBT CAS2 1 2005-01-04 2005-03-11 19 1 PRIORITY DEBT CAS2 1 2005-07-23 2005-08-21 18 2 PRIORITY DEBT CAS2 2 2005-05-12 2005-06-11 25 1 PRIORITY DEBT CAS2 2 2006-01-05 2006-02-09 42 2 PRIORITY DEBT CAS3 1 2005-07-01 2005-08-03 10 1 PRIORITY DEBT CAS3 1 2005-11-08 2006-01-01 21 2 PRIORITY DEBT CAS3 1 2006-01-01 2006-02-01 33 3 PRIORITY DEBT CAS3 2 2005-06-14 2005-06-30 26 1 PRIORITY DEBT CAS3 4 2005-01-01 2005-02-01 29 1 PRIORITY DEBT CAS3 4 2005-04-01 2005-05-11 41 2 PRIORITY DEBT CAS3 4 2005-11-03 2005-12-11 30 3 PRIORITY DEBT 15 record(s) selected. Any suggestion for another solution? By the way to partisipate in this player club input fee is 50mln. One of my Russian friends offered me this project is a member of this club. Thank's in advance Leny G. -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200806/1
From: --CELKO-- on 16 Jun 2008 18:22 Write a query to calculate players debt_pools and set up priorities for pay-off based on dates of lending. Debt_pool is total lent amount borrowed from casino during period within date range one month or less. Every at least one month gaps marks end of one debt_pool and the beginning of another. The rules for assigning priorities: The dept_pool with earliest date range for any combination casino/ player must have priority 1. The dept_pool with latest date range for any combination casino/player must have lowest priority. Let's use the gambling term Book for this data. CREATE TABLE Book (casino_id CHAR (4) NOT NULL, cust_id INTEGER NOT NULL, tran_date DATE DEFAULT CURRENT_DATE NOT NULL, PRIMARY KEY (casino_id, cust_id, tran_date), -- wild guess!! No specifications debt_amt DECIMAL (8,2) NOT NULL CHECK(debt_amt > 0.00); INSERT INTO Book VALUES('Cas1', 1, '2005-01-01', 10.00), ('Cas1', 1, '2005-02-01', 12.00), ('Cas1', 1, '2005-03-01', 9.00), ('Cas1', 1, '2005-04-01', 10.00), ('Cas1', 1, '2005-07-01', 10.00), ('Cas1', 1, '2005-08-03', 8.00), ('Cas1', 2, '2005-05-03', 13.00), ('Cas1', 2, '2005-05-11', 07.00), ('Cas1', 2, '2005-06-14', 15.00), ('Cas1', 2, '2005-06-30', 11.00), ('Cas1', 1, '2006-01-01', 11.00), ('Cas1', 1, '2006-02-01', 22.00), ('Cas2', 1, '2005-01-04', 05.00), ('Cas2', 1, '2005-02-22', 05.00), ('Cas2', 1, '2005-03-11', 09.00), ('Cas2', 1, '2005-07-23', 10.00), ('Cas2', 1, '2005-08-21', 08.00), ('Cas2', 2, '2005-05-12', 10.00), ('Cas2', 2, '2005-06-11', 15.00), ('Cas2', 2, '2006-01-05', 20.00), ('Cas2', 2, '2006-02-09', 22.00), ('Cas3', 4, '2005-01-01', 14.00), ('Cas3', 4, '2005-02-01', 15.00), ('Cas3', 4, '2005-04-01', 12.00), ('Cas3', 4, '2005-05-11', 29.00), ('Cas3', 1, '2005-07-01', 02.00), ('Cas3', 1, '2005-08-03', 08.00), ('Cas3', 4, '2005-11-03', 03.00), ('Cas3', 4, '2005-12-11', 27.00), ('Cas3', 2, '2005-06-14', 15.00), ('Cas3', 2, '2005-06-30', 11.00), ('Cas3', 1, '2005-12-11', 100.00), ('Cas3', 1, '2006-01-01', 11.00), ('Cas3', 1, '2006-02-01', 22.00)); Let's create a Calendar table with 100 years of data, starting in some year, say 2005. You should already have this in your schema, but add a column which contains a sequential number for the year and month of each date: (i.e. '2005-01-01' thru '2005-01-31' has year_month_seq = 1, '2005-02-01' thru '2005-02-28' has year_month_seq = 2, etc,) CREATE TABLE Calendar (cal_date DATE NOT NULL PRIMARY KEY, -- 2005-01-01 .. year_month_seq INTEGER NOT NULL UNIQUE -- 1 CHECK (year_month_seq > 0), year_month_name CHAR(7) NOT NULL, -- '2005-01', etc. for display ..); Now consolidate the monthly totals with a CTE or VIEW. I would use a VIEW, since it might be useful for other reports. CREATE VIEW BookMonthlySummary (casino_id, cust_id, year_month_seq, monthly_debt_amt) AS SELECT B1.casino_id, B1.cust_id, C1.year_month_seq, SUM(B1.debt_amt) FROM Book AS B1, Calendar AS C1 WHERE B1.cal_date = C1.cal_date GROUP BY B1.casino_id, B1.cust_id, C1.year_month_seq Each month is now one row in the VIEW. We can find contiguous groups with this query: CREATE VIEW BookGroupings (casino_id, cust_id, year_month_seq, monthly_debt_amt, grp_nbr) AS SELECT casino_id, cust_id, monthly_debt_amt (year_month_seq - ROW_NUMBER() OVER (PARTITION BY casino_id, cust_id ORDER BY year_month_seq) ) AS grp_nbr FROM BookMonthlySummary; Finally, we get the contiguous ranges of debt. The grouping numbers will give the priority using a DENSERANK() function and the year_month_seq can be translated back with Calendar.year_month_name. This is the ugly query. SELECT casino_id, cust_id, grp_nbr, MIN(year_month_seq) AS "start", MAX(year_month_seq) AS "end", SUM(monthly_debt_amt) AS contigous_debt_tot FROM BookGroupings GROUP BY grp_nbr; This is untested. I will try to get time to play with it later.
|
Pages: 1 Prev: Lock waits during online backup Next: How to get list of database? |