Petelai project experience small set (3) ___ from the Multi-table statistical amount to a table, petelai __

Petelai project experience small set (3) ___ from the Multi-table statistical amount to a table, petelai __

This project is mainly used by all departments and agents of petelai. The following function of this system is to collect agent fees. The expense statistics are stored in the expense pool (traditionally, the agent fee is called the expense pool) data table. The agent can purchase items based on the amount in the expense pool, the money in the fee pool comes from the service appraisal form filled in by the agent each time.

Let's take a look at the structure of this part of the data table:

The following storage process summarizes the cost of materials and other expenses and summarizes the storage process to the billing pool information table. This code is a bit long and adds various judgments:

 

-- ===================================================== ====== -- Author: ma zhaojuan -- Create date: 15:32:16 -- Description: the statistical material fee and other expenses are collected to the expense pool -- ============================ ===================== create procedure [dbo]. [proc_wxjd_committers] -- Add the parameters for the stored procedure here @ wxjdId int, -- service identification ticket ID @ dlsId int -- Agent IDASDECLARE @ Err1 int, -- declare the variable, the transaction uses @ Err2 int, -- declares the variable, and the transaction uses @ glf decimal, -- Management Fee @ gsf decimal, -- Working Hours fee @ xj decimal, -- subtotal @ totalMoney decimal, -- total amount @ count int, -- Integer Data @ LJGLF decimal, -- Part Management Fee @ LJGSF decimal, -- Part time fee @ QTFY decimal, -- other fee @ WXJDTotalMoney decimal -- total amount beginbegin transaction -- enable transaction select @ count = 0; -- assign a value to the variable -- calculate other fees in the following example: SELECT @ count = count (*) FROM T_DLS_WXJD_Cost_QTFY where fid = @ wxjdIdIF @ count> 0 -- determine whether other fees have been written to other fees, next, assign in SELECT @ xj = case sum (xj) WHEN null THEN 0 ELSE Sum (xj) end from T_DLS_WXJD_Cost_QTFY where fid = @ wxjdId; endelsebegin select @ xj = 0; END -- SELECT @ count = 0; SELECT @ count = count (*) FROM T_DLS_WXJD_Cost_CLF WHERE Fid = @ wxjdIdIF @ count> 0 -- determine whether the material fee table has been written to the material fee, the following values are assigned to the management fee and work-time fee: begin select @ glf = case sum (GLF) WHEN null then 0 else sum (GLF) END, @ gsf = case sum (gsf) WHEN null THEN 0 ELSE Sum (gsf) end from T_DLS_WXJD_Cost_CLF WHERE Fid = @ wxjdIdENDELSEBEGIN SELECT @ glf = 0; SELECT @ gsf = 0; END -- assign the following value to the total amount: SELECT @ totalMoney = @ xj + @ glf + @ gsf; -- print @ totalMoney; -- The following will write the amount calculated FROM other fees and materials into the billing pool SELECT @ count = 0; SELECT @ count = COUNT (*) FROM T_FeeExist where dlsid = @ dlsId; IF @ count> 0 -- determines whether the fee pool table has been written into the agent fee. IF the table has been written into the agent fee, you need to add the statistical fees to the agent. begin select @ LJGLF = LJGLF, @ LJGSF = LJGSF, @ QTFY = QTFY, @ WXJDTotalMoney = balance FROM T_FeeExist where dlsid = @ dlsId; SELECT @ LJGLF = @ LJGLF + @ glf; SELECT @ LJGSF = @ LJGSF + @ gsf; SELECT @ QTFY = @ QTFY + @ xj; SELECT @ override = @ WXJDTotalMoney + @ totalMoney; UPDATE T_FeeExist set ljglf = @ LJGLF, LJGSF = @ LJGSF, QTFY = @ QTFY, Token = @ WXJDTotalMoney where dlsid = @ dlsId; ENDELSE -- the first time you write fees to an agent, add a new record BEGININSERT into t_feeexist (DLSID, LJGLF, LJGSF, QTFY, WXJDTotalMoney) values (@ dlsId, @ glf, @ gsf, @ xj, @ totalMoney ); ENDSET @ err1 = @ ERROR -- UPDATE whether the service identification table has been submitted to the billing pool Field UPDATE T_DLS_WXJD SET IsFYC = 'is 'where ID = @ wxjdIdSET @ err2 = @ ERROR -- determines whether or not error IF (@ Err1 = 0 and @ Err2 = 0) commit transaction -- submit the transaction elserollback transaction -- TRANSACTION rollback END

The statistical fee is briefly introduced here. The key point of the record here is how to summarize the data of each table and determine the value in the stored procedure!

 

: Select multi-Table multi-condition query is summarized into one table. The data result is incorrect.

Select Product NO,
(Select sum (id) from Statement of deposit in the previous period _ the detailed where condition is the same) as statement of deposit in the previous period,
(Select sum (id) from Warehouse picking ticket _ The where condition is the same) as Warehouse picking,
From part material information, last-period ending _ main table, last-period ending _ details
Where condition
Almost in this form