SQL records results on a daily basis and supports paging, no record padding 0 values

The format of the database is as follows, which is a list of records that require a daily summary of the schedule for the reverse output.

The following SQL statement can be used to summarize by day and can be paged out

DECLARE @Days TABLE (datetime) DECLARE @StartDate datetime= ' 2014-04-05 14:22:20 '--Start date of summary DECLARE @EndDate datetime= ' 2015-04-08 15:21:33 '--summary End date declare @start datetimedeclare @E2_UserName varchar = ' CN000001 '-username DECLARE @ Startid int=1--page uses the Get record start Iddeclare @EndID INT =15--paging uses the end ID of the get record, test for 15 data Set @start = CONVERT (VARCHAR, @StartDate, + ' 00:00:00 ' while @start <= CONVERT (VARCHAR, @EndDate, 23°c) + ' 00:00:00 ' Begininsert into @Days VALUES (@start) SET @st Art = DATEADD (day, 1, @start) END; With OrderList as (the order by date DESC) as ROW,--here specifies that the SUM (Md_tuijianjiang) as ' md_tuij is arranged in reverse order of the date Ianjiang ', sum (Md_jiandianjiang) as ' Md_jiandianjiang ', sum (md_rifenhong) as ' Md_rifenhong ', sum (Md_jihuo) as ' Md_ji Huo ', sum (Md_tgzxjiang) as ' Md_tgzxjiang ', sum (Md_lingdaojiang) as ' Md_lingdaojiang ', sum (Md_shichangjiang) as ' md_s Hichangjiang ', sum (md_help) as ' Md_help ', sum (md_luckly) as ' md_luckly ', sum (Md_fanbujiang) as ' Md_fanbujiang ', SU M (Md_month)As ' Md_month ', sum (md_checkin) as ' Md_checkin ', sum (total) as ' total ', date from (SELECT SUM (ISNULL ([Md_tuijianjian g],0) as ' Md_tuijianjiang ', sum (ISNULL ([md_jiandianjiang],0)) as ' Md_jiandianjiang ', sum (ISNULL ([md_rifenhong],0)) A S ' Md_rifenhong ', sum (ISNULL ([md_jihuo],0)) as ' Md_jihuo ', sum (ISNULL ([md_tgzxjiang],0)) as ' Md_tgzxjiang ', sum (isnu LL ([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), Days (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°c) + ' 00:00:00 ' =b. Date anD [E2_username][email protected]_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

SQL records results on a daily basis and supports paging, no record padding 0 values