|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-06-29 : 08:07:37
|
| Shiraz writes "I have 2 questions that have completely stumped me: Question 1: You have a sequential set of data as follows cust_# : 001, date: 04/03/1998, amt: 5.00, desc: depositcust_# : 001, date: 04/04/1998, amt: -1.00, desc: witdrawlcust_# : 001, date: 04/26/1998, amt: 25.00, desc: depositcust_# : 001, date: 12/26/1998, amt: 25.00, desc: depositcust_# : 007, date: 05/29/2000, amt: 87.45, desc: depositcust_# : 007, date: 06/05/2000, amt: 87.45, desc: witdrawlWhat I would like is to create dummy data between each of date gaps by each customer. For example for customer 001 there is date gap between his transaction on 04/04/1998 and 04/26/1998. In between that gap I want to create the following dummy datacust_# : 001, date: 04/05/1998, amt: 0, desc: dummy_datacust_# : 001, date: 04/06/1998, amt: 0, desc: dummy_datacust_# : 001, date: 04/07/1998, amt: 0, desc: dummy_data..cust_# : 001, date: 04/25/1998, amt: 0, desc: dummy_data[skip over 04/26/1998 since a record already exists]cust_# : 001, date: 04/27/1998, amt: 0, desc: dummy_dataThen this process would go on until customer 001 last transaction on 12/26/1998. Then you would start fresh with customer 007Please note: that each customer has to have a seperate dummy data for his transactions and you should see all customer transactions with dummy data inserted as a full tableQuestion 2: After completing work in Question 1: I need to find the average amount of the customer. By taking the first day of the month's amount of a transaction plus the last day of the months transaction and dividing by the number of days in that month.For examplecust_# : 001, date: 04/01/1998, amt: 20, desc: depositcust_# : 001, date: 04/02/1998, amt: 0, desc: dummy_datacust_# : 001, date: 04/30/1998, amt: 30, desc: depositIn this case 20+30/30. There might be another case where there customer may have passed away by the end of that month. Lets say this is customer 001 final transactions before he passes on 06/06/07cust_# : 001, date: 06/05/2050, amt: 34.95, desc: depositcust_# : 001, date: 06/06/2050, amt: -4.95, desc: depositIn this case You would take 2/30 x 34.95-4.95. Please note: that each customer has to have a separate calculation for his transaction and you should see all customer transactions as full table" |
|
Inserting Dummy data and Average Balance 