SQL summarizes the record results by day and supports pagination. No records are filled with 0 values. SQL is collected by day.

SQL summarizes the record results by day and supports pagination. No records are filled with 0 values. SQL is collected by day.

The format in the database is as follows. It is a record list, which must be output in descending order of daily summary.

The following SQL statement can be used for daily summary and output by page.

DECLARE @ Days TABLE (date DATETIME) DECLARE @ StartDate datetime = '2017-04-05 14:22:20 '-- Summary start date DECLARE @ EndDate datetime = '2017-04-08 15:21:33' -- Summary end date DECLARE @ start DATETIMEDECLARE @ E2_UserName varchar (100) = 'cn00000' -- username DECLARE @ StartID INT = 1 -- start IDDECLARE @ EndID INT = 15 -- end ID of the retrieved record used on the page, SET @ start = CONVERT (VARCHAR, @ StartDate, 23) + '00:00:00 'WHILE @ start <= CONVERT (VARCHAR, @ EndDate, 23) + '00:00:00 'begininsert into @ Days VALUES (@ start) SET @ start = DATEADD (DAY, 1, @ start) END; WITH orderList AS (SELECT ROW_NUMBER () OVER (order by date DESC) AS Row, -- This specifies that SUM (MD_TuiJianJiang) AS 'md _ TuiJianJiang ', SUM (MD_JianDianJiang) is arranged in descending ORDER of the date) AS 'md _ JianDianJiang ', SUM (MD_RiFenHong) AS 'md _ RiFenHong', SUM (MD_JiHuo) AS 'md _ jihuo', SUM (MD_TGZXJiang) AS 'md _ TGZXJiang ', SUM (MD_LingDaoJiang) AS 'md _ LingDaoJiang ', SUM (MD_ShiChangJiang) AS 'md _ ShiChangJiang', SUM (MD_Help) AS 'md _ help', SUM (MD_Luckly) AS 'md _ Luckly ', SUM (MD_FanBuJiang) AS 'md _ FanBuJiang', SUM (MD_Month) AS 'md _ month', SUM (MD_CheckIN) AS 'md _ CheckIN ', SUM (TOTAL) AS 'Total', date FROM (select sum (ISNULL ([MD_TuiJianJiang], 0) AS 'md _ TuiJianJiang ', SUM (ISNULL ([MD_JianDianJiang], 0) AS 'md _ JianDianJiang ', SUM (ISNULL ([MD_RiFenHong], 0) AS 'md _ RiFenHong', SUM (ISNULL ([MD_JiHuo], 0 )) AS 'md _ JiHuo ', SUM (ISNULL ([MD_TGZXJiang], 0) AS 'md _ TGZXJiang', SUM (ISNULL ([MD_LingDaoJiang], 0 )) AS 'md _ LingDaoJiang ', SUM (ISNULL ([MD_ShiChangJiang], 0) AS 'md _ ShiChangJiang', SUM (ISNULL ([MD_Help], 0 )) AS 'md _ help', SUM (ISNULL ([MD_Luckly], 0) AS 'md _ Luckly ', SUM (ISNULL ([MD_FanBuJiang], 0 )) AS 'md _ FanBuJiang ', SUM (ISNULL ([MD_Month], 0) AS 'md _ month', SUM (ISNULL ([MD_CheckIN], 0 )) AS 'md _ CheckIN ', SUM (ISNULL (MD_TuiJianJiang + MD_RiFenHong + MD_JianDianJiang + MD_JiHuo, 0) AS 'Total', -- ISNULL (DAY (MD_Datetime), DAY (B. date) AS 'md _ datetime', ISNULL ([E2_UserName], @ E2_UserName) AS 'e2 _ username', B. date FROM [E2_MoneyDetails] right join @ Days B on convert (VARCHAR, MD_Datetime, 23) + '00:00:00 '= B. date AND [E2_UserName] = @ E2_UserNameWHERE B. date between dateadd (DAY,-1, @ StartDate) AND @ EndDateGROUP by B. date, E2_UserName) egroup by date) SELECT * FROM orderlistWHERE Row BETWEEN @ StartID AND @ EndID

The final result style is