1. Use a statement to get the maximum number of days in a certain date? Select Day (DD (DD, -DAY ('2004-02-13'), DateAdd (mm, 1, '2004-02-13))))) AS' DAY NUMBER '2. Less records become multiple record problems Have a table TBL date income spendant 2004-02-11 00:00:00 60 45 2004-03-01 00:00:00 60 45 2004-03-02 00:00:00 40 50 2004-03-05 00:00 00 50 40
/ * Test data: Create Table TBL ([Date] SmallDateTime, [Revenue] Int, [Expenditure] INT) INSERT INTO TBL SELECT '2004-02-11', 60, 45 Union Select '2004-03-01', 60 45 Union Select '2004-03-02', 40, 50 Union Select '2004-03-05', 50, 40 * / To get the result:
Date income spending balance 2004-02-01 00:00:00 Null null null 2004-02-02 00:00:00 Null null 2004-02-03 00:00:00 Null null null 2004-02-04 00: 00:00 Null Null Null 2004-02-05 00:00:00 Null Null Null 2004-02-06 00:00:00 Null null 2004-02-07 00:00:00 Null null null 2004-02-08 00:00:00 Null null 2004-02-09 00:00:00 Null null null 2004-02-10 00:00:00 Null null null 2004-02-11 00:00:00 60 45 15 2004-02 -12 00:00:00 NULL NULL 15 2004-02-13 00:00:00 Null Null 15 2004-02-14 00:00:00 Null Null 15 2004-02-15 00:00:00 Null NULL 15 2004 -02-16 00:00:00 NULL NULL 15 2004-02-17 00:00:00 Null Null 15 2004-02-18 00:00:00 Null Null 15 2004-02-19 00:00:00 Null Null 15 2004-02-20 00:00:00 Null Null 15 2004-02-21 00:00:00 Null Null 152004-02-22 00:00:00 Null Null 15 2004-02-23 00:00:00 NULL Null 15 2004-02-24 00:00:00 Null Null 15 2004-02-25 00:00:00 Null Null 15 2004-02-26 00:00:00 Null Null 15 2004-02-27 00:00: 00 Null Null 15 2004-02-28 00:00:00 Null Null 15 2004-02- 29 00:00:00 NULL NULL 15 2004-03-01 00:00:00 60 45 30 2004-03-02 00:00:00 40 50 20 2004-03-03 00:00:00 Null Null 20 2004- 03-04 00:00:00 NULL NULL 20 2004-03-05 00:00:00 50 40 30 2004-03-06 00:00:00 Null Null 30 2004-03-07 00:00:00 NULL NULL 30 2004-03-08 00:00:00 Null Null 30 2004-03-09 00:00:00 Null Null 30 2004-03-10 00:00:00 Null Null 30 2004-03-11 00:00:00 NULL NULL 30 2004-03-12 00:00:00 Null Null 30 2004-03-13 00:00:00 Null Null 30 2004-03-14 00:00:00 Null Null 30 2004-03-15 00:00: 00 Null Null 30 2004-03-16 00:00:00 Null Null 30 2004-03-17 00:00:00 Null Null 30 2004-03-18 00:00:00 Null NULL 30 2004-03-1900: 00:00 Null Null 30 2004-03-20 00:00:00 Null Null 30 2004-03-21 00:00:00 Null NULL 30 2004-03-22 00:00:
00 Null Null 30 2004-03-23 00:00:00 Null Null 30 2004-03-24 00:00:00 Null Null 30 2004-03-25 00:00:00 Null Null 30 2004-03-2600: 00:00 Null Null 30 2004-03-27 00:00:00 Null Null 30 2004-03-28 00:00:00 Null Null 30 2004-03-29 00:00:00 Null NULL 30 2004-03-30 00:00:00 Null Null 30 2004-03-31 00:00:00 Null Null 30 Answer: Select Y [Date], TBL. [Revenue], TBL. [Expendit], (SELECT SUM (ISNULL. [Income], 0) -In ENULL (TBL. [Expenditure], 0)) from tbl where [Date] <= Y. [Date]) AS [额] from TBL Right Join (SELECT DATEADD (DD, Ni, DateAdd DD, 1-day (m.minday), m.minday)) AS [Date] from (SELECT 0 AS i
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 Union All Select 26 Union All Select 27 Union All Select 28 Union All SELECT 29 Union All Select 30 Union All Select 31) N, (SELECT MIN) AS MINDAY from TBL Group by datediff (Month, 0, Date) M Where Datediff (mm, dateadd (DD, Ni, Dateadd (DD, 1-day (m.Minday), M.Minday), M.Minday) = 0) AS Y ON TBL. [Date] = Y. Date
