Ms SQL: funcation split row

2007 Years 7 , 8 Month , My job is to complete the requirement analysis and design of the vehicle management system. , During this period, a problem had plagued me for a long time. At this moment, I finally had time to record the problem and solution.

Let's talk about how to solve the problem generated by the demand (problem. In the vehicle management system, fixed fees and variable fees are involved. My definition of these fees is:Fixed fees, that is, vehicle fees that do not change during a fixed period of time, such as insurance, annual inspection,GPSFees. The variable fee is the fee generated according to the actual situation during the routine maintenance and operation of the vehicle, such as the tire replacement fee, fuel installation fee, and freight fee. The decision-making layer of the company needs to have a good understanding of the profitability of the vehicle, that is, the gross profit analysis of the vehicle, the demand will be generated, the variable costs can be well calculated according to the dependent documents, such as maintenance costs, according to the repair ticket number (such Mt200708003 ) Use SQL In Case The syntax can easily display the scheduling cost in the form of a list (that is, the service cost generated by the vehicle operation, such as the freight, maintenance fee, and fuel fee), or according to the dispatch ticket (such At200708002 ) UseSQLInCaseSyntax to easily represent it in the form of a list . Apparently, the gross profit (freight - Other variable fees) can be clearly displayed in the list, but for the complete vehicle cost analysis, it is clear that the fixed cost is not considered, and the fixed cost accounts for a large part of the vehicle cost.

how can we make a good performance of fixed fees in the gross profit analysis of vehicles?
solution 1. allocate fixed fees to the fees involved in each ticket? I personally think this is not necessary. Regardless of the technical implementation complexity, as for business documents, the number of documents increases with the business, 100 , so this month, due to the increase in the number of documents, is the insurance fee per ticket last month less than 100 so this month, is the response to the fixed cost per ticket last month inaccurate?
solution 2: allocate the fixed cost to each vehicle on a monthly basis? I think this is reasonable, because the number of months per year is fixed, all of which are 12 months, and all fixed fees are calculated by an integer multiple of the months, for example, the annual insurance fee is 12 the monthly and quarterly health check fees are calculated by 3 months. Therefore, the fixed cost is apportioned to the vehicle's monthly cost, and the monthly fixed cost of the vehicle remains unchanged, ensuring the accuracy of the gross profit analysis of the vehicle.

the solution is fixed. How can we solve this problem technically, that is, how to allocate the fixed cost to the vehicle monthly cost analysis list? Use select statistics are a good choice. You can use the fixed fee/The number of months. However, How are fixed fees and variable fees displayed in a list? SQL : Union this problem can be solved, that is,

Select null,Case whenFixed Cost="Fixed cost items"ThenFixed Cost/Number of monthsElse o end

Union

SelectCase whenVariable fee="Variable feeProject"Then... Else o end,Null

At this point, all the problems encountered in the gross profit analysis of vehicles seem to have been solved.SQL:FanctionWhat about it?

We continue to analyze,Vehicle gross profit analysis must be able to be filtered by time! It is necessary to query by month, so it seems that there is no problem, just inSelectAddWhereTime=A month is acceptable. However, this is unacceptable to the decision-making layer, because what they want is to analyze the gross profit of a vehicle in a list not only in a specific month, however, a vehicle has been operating for more than a month. InSelectPay-as-you-goAddWhereTime=Won't the problem be solved in a month? The answer is no. Because the fixed cost is only the start time and end time, such2007/01/01To2007/12/30, Then we start at the start time.WhereTime= 5Monthly, fixed fees cannot be found !? At this point, the problem seems to have entered an unsolved environment, and I am confused....

Give up what you don't want, if you canThe fixed charge is apportioned to the continuous month data based on its start time and End Time, and placed in a table. Can this fully meet the vehicle query needs by any month?The answer is yes, but if the same data is stored in the two tables, there is always something wrong, because it will bring unnecessary trouble to the implementation of other functions. Then SQL : Fanction is amazing Is it OK?Use fixed feesFanctionDynamically generate continuous month data when vehicle costs are formed What about it? Yes, I personally think this method is wonderful ( ^-^ Narcissism), because FanctionThe tables returned from the existing data are temporary. You do not need to change the structure of the existing table or add a table. This does not affect the design of other functions. In addition, the problem is solved and the demand is met. Of course, Fanction Dynamic generation of data sacrifices certain performance, and as the fixed cost data increases, the negative impact of performance increases, because dynamic generation of tables must traverse all the fixed costs. But only let Fanction does the calculation of data over the past two years solve the problem of increasing fixed fees? Note that if the database is migrated by ms SQL Server, fanction cannot be used because fanction is only supported by ms SQL Server.
conclusion: When the select statistic data is used, multiple rows of data are summarized in most cases, but the rows are the opposite here, splitting the row data into multiple rows, and solved the problem. This is the so-called reverse thinking. As a movie said: Gambling horses do not analyze which horses will win, but count which ones will not win. Therefore, when thinking about a problem, it makes sense to find out why you think so, what is the basis for thinking, and what is the idea, because only when we know the line we are thinking about can we clearly reverse the line.