SQL multiple table query and calculation statistics code based on Access and ASP

Recently wrote a few about the "project-subitem-task" management system, said the system is still a little exaggerated, basically is a multi-table query call and INSERT, update database operation, but there are a lot of computational and statistical problems, making SQL appear complex. Therefore, it is necessary in a phase after the completion of the task, do a summary, some of the test process of SQL code to summarize, in case later use and forget, but also welcome you db yards together spit groove.

These months have been written several systems, the last system is the credit card management system, but also the accumulation of SQL to a more complex phase, with this as an example to organize the time since access and ASP-based SQL multiple table query and calculation Statistics code is more appropriate.

PS: Because it involves their own bank information security, hidden some information, but the amount of money, bills and so on is true, is not very chop hands.

Back to the point, more credit cards, hoping to use a better plan to statistics credit card bills, quotas, repayment and so on, and do not want to use such as "51 credit card management" and other software, so that people get my financial information, it is best to get a database to solve, put on their own cloud host or virtual server up, The phone is also accessed. This is the reason for this simple system, a lot of data is not 10%, but in the process of constantly adding the need to count the information to improve the code.

To build a creditcard database, I'm using ACCESS2007, extension accdb. A total of three tables were created (credit card information), Bill, Payment (repayment), as much as possible to the most basic data entry, reduce redundancy, more calculations and statistics written on the ASP.

As in the CreditCard information page above, look at the SQL statement:

Select Credit.id as serial number, CREDIT.CCNO as index, Credit.bank as bank, Credit.brand as brand, Credit.card as card number, Format (Credit.limit, ' C Urrency ') as amount, credit.belong as cardholder, Credit.billday as Billing day, credit.consumeday as consumption day, count (BILL.CCNO) as Bill number, Format (Cred It.templimit, ' Currency ') as temporary limit, format (credit.unsettled, ' Currency ') as no billing amount, format ((credit.limit+ credit.templimit-credit.unsettled), ' Currency ') as the remaining amount, credit.newtime as update time from credits left joins Bill on credit.ccno = B Ill. Ccnogroup by Credit.id, Credit.ccno, Credit.bank, Credit.brand, Credit.card,credit.limit, Credit.Belong, Credit.billday, Credit.consumeday,credit.templimit, credit.unsettled, Credit.newtimeorder by Credit.ID

The name after as in the SELECT statement can be used as the name of the field (column), and for the real amount count method and the currency symbol, the ￥12345.67 format is obtained by using format (field or expression, ' Currency '), which can be expressed in SQL, such as "Quota + Temporary limit-unpaid bill amount can get the remaining amount and so on. Because of the number of bills that correspond to the bank in the Count Bill table, a link of two tables was used, namely "credit" and "Bill" two tables. Two table links are still relatively easy, using left, INNER, right can be achieved count, but other usage requirements, the three link way is different, especially in three tables above the application of links is very important.

PS: Hidden to index, bank, card number and so on.

Bill Payment is called at the same time as three tables, with the aim of seeing the information about the credit card's bill, repayment time, repayment status and so on, and looking at the SQL statement:

Select Bill.id as serial number, BILL.CCNO as index, Credit.bank as bank, Credit.card as card number, Bill.blno as Bill number, month (bill.billdate) as billing monthly Bill.paydate as the last repayment date, Format (bill.balance, ' Currency ') as the current bill amount, Max (payment.paydate) as repayment time, IIf (Max ( Payment.paydate) is not Null,iif (Bill.balance-sum (payment.balance) <=0, ' Settle Bill ', ' partial repayment, remainder ' &bill.paydate-date () & ' Day '), IIF (Bill.paydate-date () >0,bill.paydate-date () & ' days ', ' Overdue: ' &date ()-bill.paydate& ' days ')) as The remaining repayment time, format (payment.balance, ' Currency ') as the total repayment amount, Format (IIf (Bill.balance-sum (payment.balance) <=0,0, Bill.balance-sum (payment.balance)), ' Currency ') as unpaid amount, IIf (Bill.balance-sum (payment.balance) <=0, ' √ ', ') as  Bill completion from (credits INNER join bill on credit.ccno = bill.ccno) left JOIN Payment on bill.blno = Payment.blnogroup by Bill.id, Bill.ccno, Credit.bank, Credit.card, Bill.blno, Bill.billdate, Bill.paydate, bill.balancehaving (year (Bill.billdate) = ' and Month ' (bill.billdate) = ' a ') Order by Bill.id Desc

The SELECT statement has month (the Time Type field) to get the months, and the year (Time Type field), week (), and so on to get the relevant time information; use Max, Min to get the largest or smallest number in a field (column), which can be a number or a time type.

This SELECT statement involves an If statement, sentence: IIF (field or expression, the expression is true, the expression is a false result), and the expression can be nested again. Parse the IF statement for the "Remaining repayment time" field above.

iif(Max (payment.paydate)is not Null,iif (Bill.balance-sum (payment.balance) <=0, ' Settle Bill ', ' Partial repayment, remainder ' &bill.paydate-date () & ' days '),IIF (Bill.paydate-date () >0,bill.paydate-date () & ' days ', ' overdue : ' &date ()-bill.paydate& ' Day '))as the remaining repayment time

The first if layer (red), IIf (Max (payment.paydate) is not Null, the expression is a true result, the expression is a false result), when the paydate in the "Payment" table (which refers to the repayment time) is not a null value (blue), That is, there is a month bill in the "Bill" table, but the bill in the table "Payment" has a value (not a null value), then the execution "expression is true"; If the credit card has no repayment record for a month, that is, the repayment time is null (NULL), then the "expression is false" is executed.

So what are the results of these two true and false expressions? Look again at the next if layer (purple), IIf (Bill.balance-sum (payment.balance) <=0, ' Settle Bill ', ' partial repayment, ' &bill.paydate-date ' () & ' days '). That is, there is already a record of repayment, then this is the existence of two States, that is, a one-time payment of a monthly bill should be the total amount, and only a portion of the money, but also the remaining part is not yet. From the expression, the amount of the bill----the sum of the total amount of the payment ≤0, that is already fully paid (can pay more money, because most of the next month will also have a card, certainly have a bill), then the show "closing the bill"; Have not finished (owe too much money, to "turnover" several times to finish), then shows "partial repayment, more than n days."

There is also a first-level if statement expression for false results (green), IIF (Bill.paydate-date () >0,bill.paydate-date () & ' days ', ' Overdue: ' &date ()-bill.paydate & ' Day '). That is, the last repayment time-the current date >0, then calculates the last repayment time-the number of days of the current date; otherwise less than or equal to 0 is overdue (because the last day of repayment, not all banks can be accounted for repayment, will be the second day of accounting), overdue n days.

Tomorrow we will continue to parse the method selection of the links of the three tables, how to use or choose the left link, the inner link (INNER join), and the right link, the difference and collocation between them.

SQL multiple table query and calculation statistics code based on Access and ASP