sql--processing the summation of rows

Background:

Now there is a report, where the above data is the merchant's order data, according to the day, to find out from the order form a daily order of a merchant's orders, and then daycare the income statement to find out this day of the merchant's earnings, and then also to calculate the amount of income before one day, It then filters out the records for a certain time period from the table that is found.

Here is the SQL:

ALTER PROCEDURE [dbo]. [Sp_loadsellerdatabytime] @SellerCode NVARCHAR (36),--Merchant code @starttime char (20),--Start date @endtime Char,-- End Date @pagesize int=31,--How many pieces of data are on a page @pageIndex int=1--page Asdeclare @totalCount INT--Returns the number of data bars--Daily turnover table select * Into #temp_Co Unt from (SELECT left (convert Varchar,table0. createtime,21) as monthdate/* this article date */,sum (Table0. Totalcharge) as summonthmoney/* today total turnover */from [communitycatering]. [Catering]. [Sellerincome] Table0where [Email protected]/*sellercode*/group by Left (convert Varchar,table0. createtime,21) as tempcount--check Total amount select * Into #MainTable from (select Monthdate,summonthmoney,sumtotalmoney, ISNULL (totalcount,0) as TotalCount from (select monthdate,summonthmoney,sumtotalmoney= (select SUM (Summonthmoney) from #temp_Count t1where t1. Monthdate<=t2. monthdate) from #temp_Count T2) as Sileft JOIN (/* Check daily order quantity */SELECT Left (CONVERT (varchar,ord.completetime,21), ten) as MonthDate1, COUNT (1) as totalcount/* order quantity */from communitycatering.catering. [Order] Ord WHERE Ord. Completetime is not NULL and Ord. validstatus=5/* Validity: Select the completed order */and [email protected]/* merchant code*/GROUP by Left (convert (Varchar,ord.completetime, ) as temp on SI. Monthdate=temp. MonthDate1) as Maintable SELECT *,row_number () over (ORDER by #MainTable. Monthdate) as rownum/* paging requires field */from #MainTable W Here #MainTable. Monthdate between @StartTime and @EndTime/* Go to start time data */-Calculate total number of data bars SELECT @totalCount =isnull (COUNT (1 ), 0) from #MainTable WHERE #MainTable. Monthdate between @StartTime and @EndTime-output total number of data bars SELECT @totalCount as Totalcou nt--Delete temporary table drop table #MainTable drop table #temp_Count

As above code:

For example: The first column represents days, the second column represents the turnover of the day, and the third column represents all the turnover before this day, and you can see that the third column is an accumulation of the second column.

in SQL, a temporary table is defined first, and then a small nested query is used . in this nested query, we calculate the cumulative sum of the previous daily turnover.

sql--processing the summation of rows