How does MSSQL implement automatic completion of dates (completion of missing dates in the data table)

In our real life, we will encounter many problems with missing dates, but we need

Shows the missing date. How can we solve this problem? Here I set myself

You can share the following three methods:

Method 1: Create a stored procedure

-- How to Use "minimum missing quantity" to automatically complete a real date

--> Generate Test Data:
GO
IF OBJECT_ID ('tbl') IS NOT NULL
DROP TABLE TBL
GO
Create table tbl (
DATE,
Remarks VARCHAR (100)
)
GO
INSERT TBL
SELECT '2014-03-02 ',' B 'UNION ALL
SELECT '2014-03-05 ', 'C' UNION ALL
SELECT '2014-03-06 ', 'd 'Union ALL
SELECT '2014-03-07 ', 'E' UNION ALL
SELECT '2014-03-09 ', 'F' UNION ALL
SELECT '2014-03-11 ', 'G' UNION ALL
SELECT '2014-03-12 ', 'H' UNION ALL
SELECT '2014-03-13 ',' I 'Union ALL
SELECT '2014-03-15 ', 'J' UNION ALL
SELECT '1970-03-19 ', 'K' UNION ALL
Select '2014-03-20 ', 'l'

Go
If object_id ('P _ sp ') is not null
Drop proc p_sp
Go
Create proc p_sp @ endtime date
As
Declare @ SQL varchar (100)
Set @ SQL = 'select * From TBL order by date'
Declare @ minmiss date
Set @ minmiss = (
Select coalesce (dateadd (DD, 1, min (A. Date )),
'2017-03-01 ') as missing
From TBL
Where not exists (
Select * From TBL B
Where B. Date = dateadd (DD, 1, A. Date ))
And exists (
Select 1 from TBL where date = '2017-03-01 '))
Print @ minmiss
While @ minmiss <= @ endtime
Begin
Insert TBL (date) values (@ minmiss)
Select @ minmiss = (
Select dateadd (DD, 1, min (A. Date ))
From TBL
Where not exists (
SELECT * FROM TBL B
Where B. Date = DATEADD (DD, 1, A. Date ))
)
END
EXEC (@ SQL)

EXEC P_SP '2017-03-20'

/*
Date remarks
2012-03-01 NULL
2012-03-02 B
2012-03-03 NULL
2012-03-04 NULL
2012-03-05 C
2012-03-06 D
2012-03-07 E
2012-03-08 NULL
2012-03-09 F
2012-03-10 NULL
2012-03-11G
2012-03-12 H
2012-03-13 I

2012-03-14 NULL

2012-03-15 J

2012-03-16 NULL

2012-03-17 NULL

2012-03-18 NULL

2012-03-19 K

2012-03-20 L

*/

--------------------------------------------------

--------------------------------------------------

-- Method 2: implement with recursion:

--> Generate Test Data:

GO
IF OBJECT_ID ('tbl') IS NOT NULL
DROP TABLE TBL
GO
Create table tbl (
DATE
)
GO
INSERT TBL
SELECT '2014-03-01 'UNION ALL
SELECT '2014-03-31'

-- Use recursion to output all dates of January:
Go
Declare @ date
Select @ date = MAX (date) from tbl
; With t
As (
Select * From TBL
Union all
Select dateadd (DD, 1, A. Date) from T
Where not exists (select * From TBL B
Where B. Date = dateadd (DD, 1, A. Date)
)
And a. Date <@ date
)
Select * from t order by date

/*
Date
2012-03-01
2012-03-02
2012-03-03
2012-03-04
2012-03-05
2012-03-
2012-03-07
2012-03-08
2012-03-09
2012-03-10
2012-03-11
2012-03-12
2012-03-13
2012-03-
2012-03-
2012-03-16
2012-03-17
2012-03-18
2012-03-19
2012-03-20
2012-03-21
2012-03-22
2012-03-23
2012-03-24
2012-03-25
2012-03-26
2012-03-27
2012-03-28
2012-03-29
2012-03-30
2012-03-31
*/

---------------------------------------------------
---------------------------------------------------
Method 3: construct a system table
/*
Create table # tB (
[A] int,
[C2] varchar (10 ),
[C3] datetime
)
Insert # tB
Select 1, 'dfgsdfgsdf ', '2017-02-01' union all
Select 2, 'dfgsdfgsdf ', '2017-02-02' union all
Select 3, 'dfgsdfgsdf ', '2017-02-03' Union all
Select 4, 'dfgsdfgsdf ', '2017-02-04' Union all
Select 4, 'dfgsdfgsdf ', '2017-09-04' Union all
Select 5, 'dfgsdfgsdf ', '2017-09-08' Union all
Select 5, 'dfgsdfgsdf ', '2017-03-08' Union all
Select 6, 'dfgsdfgsdf ', '2017-03-11' Union all
Select 4, 'dfgsdfgsdf ', '2017-05-04' Union all
Select 5, 'dfgsdfgsdf ', '2017-02-08' Union all
Select 6, 'dfgsdfgsdf ', '2017-05-11' Union all
Select 7, 'dfgsdfgsdf ', '2017-05-14' Union all
Select 8, 'dfgsdfgsdf ', '2017-05-16' Union all
Select 7, 'dfgsdfgsdf ', '2017-03-14' Union all
Select 8, 'dfgsdfgsdf ', '2017-03-16' Union all
Select 6, 'dfgsdfgsdf ', '2017-09-11' Union all
Select 7, 'dfgsdfgsdf ', '2017-09-14' Union all
Select 8, 'dfgsdfgsdf ', '2017-09-16' Union all
Select 9, 'dfgsdfgsdf ', '2017-11-17'

Expected results:

SQL code

Month total percent
2010-01 0 ....
2010-02 14 ....
2010-03 26 ....
2010-04 0 ....
2010-05 25 ....
2010-06 0 ....
2010-07 0 ....
2010-08 0 ....
2010-09 25 ....
2010-10 0 ....
2010-11 9 ....
2010-12 0 ....

*/
Go
If OBJECT_ID ('tbl') is not null
Drop table tbl
Go
Create table tbl (
[A] int,
[C2] varchar (10 ),
[C3] datetime
)
Insert tbl
Select 1, 'dfgsdfgsdf ', '2017-02-01' union all
Select 2, 'dfgsdfgsdf ', '2017-02-02' union all
Select 3, 'dfgsdfgsdf ', '2017-02-03' union all
Select 4, 'dfgsdfgsdf ', '2017-02-04' union all
Select 4, 'dfgsdfgsdf ', '2017-09-04' union all
Select 5, 'dfgsdfgsdf ', '2017-09-08' union all
Select 5, 'dfgsdfgsdf ', '2017-03-08' union all
Select 6, 'dfgsdfgsdf ', '2017-03-11' union all
Select 4, 'dfgsdfgsdf ', '2017-05-04' union all
Select 5, 'dfgsdfgsdf ', '2017-02-08' union all
Select 6, 'dfgsdfgsdf ', '2017-05-11' union all
Select 7, 'dfgsdfgsdf ', '2017-05-14' union all
Select 8, 'dfgsdfgsdf ', '2017-05-16' union all
Select 7, 'dfgsdfgsdf ', '2017-03-14' union all
Select 8, 'dfgsdfgsdf ', '2017-03-16' union all
Select 6, 'dfgsdfgsdf ', '2017-09-11' union all
Select 7, 'dfgsdfgsdf ', '2017-09-14' union all
Select 8, 'dfgsdfgsdf ', '2017-09-16' union all
Select 9, 'dfgsdfgsdf ', '2017-11-17'

Select
Isnull (c1, '2017-'+ right ('00' + ltrim (number), 2)

[Month], -- increment by month
Isnull (c2, 0) as total,
Ltrim (cast (isnull (c2, 0) x 100*1.0/(select sum ([A]) from

Tbl) as decimal () + '%' as [percent]
-- Percentage
From master .. spt_values B
Left join
(Select convert (varchar (7), C3, 120) as c1, sum ([A]) as c2

From tbl
Group by convert (varchar (7), C3, 120)
) C on B. number = month (c. c1 + '-01') where B. type = 'p' and

B. number between 1 and 12

/*
Month total percent
2010-01 0 0.00%
2010-02 15 14.29%
2010-01 26 24.76%
2010-04 0 0.00%
2010-05 25 23.81%
2010-06 0 0.00%
2010-07 0 0.00%
2010-08 0 0.00%
2010-09 30 28.57%
2010-10 0 0.00%
2010-11 9 8.57%
2010-12 0 0.00%

*/

It is for reference only, and the knowledge is not comprehensive. Please forgive me. Thank you for reading this article.