SQL date completion and SQL completion

SQL date completion and SQL completion

</Pre> <pre name = "code" class = "SQL"> IF OBJECT_ID ('tbl') IS NOT nullddrop TABLE TBLGOCREATE TABLE TBL (DATE) goinsert tblselect '2017-09-30 'union allselect '2017-10-06' goDECLARE @ date dateselect @ DATE = MAX (DATE) from tbl; with tas (SELECT * from tblunion allselect dateadd (DD, 1,. date) from t awhere not exists (SELECT * from tbl bwhere B. date = DATEADD (DD, 1,. date) and. DATE <@ DATE) SELECT * FROM T ORDER BY DATE

How does SQL server use a select statement to find out the sum of data in a table for a period of time every day (and complete the missing date )?

Select COUNT (*), convert (date, max (date ))
From table group by convert (date, date)
Union all
Select * from
(Select 0 co, convert (varchar (10), dateadd (dd, number, convert (varchar (8), getdate (), 120) + '01'), 120) as dt
From master .. spt_values
Where type = 'P'
And dateadd (dd, number, convert (varchar (8), getdate (), 120) + '01') <= dateadd (dd,-1, convert (varchar (8), dateadd (mm, 1, getdate (), 120) + '01') c
Where dt not in (select convert (date, max (date) from Table group by convert (date, date ))
Change the table and date to the corresponding table and date column names.

After an SQL query is completed, the completion date needs help.

Based on your data, let's give you an example. The data type is not exactly the same as yours. You need to adjust it based on your actual situation:
-- Create table [table] (dtime datetime, data varchar (10), num smallint) -- add Test data -- insert into [table] select '2017-9-8 ', 'data1', 0 -- insert into [table] select '2014-9-10', 'data1', 3 -- insert into [table] select '2014-9-11 ', 'data1 ', 4 -- Test statement select * from [table] -- declare @ d table (time datetime) declare @ date datetimeset @ date = '2014-09-07 'while @ date <= '2014-09-12' begin insert @ d select @ date set @ date = dateadd (dd, 1, cast (@ date as datetime) endselect convert (varchar (10), time, 120) as time, isnull (data, 'data1') as data, isnull (num, 0) as numfrom @ d left join [table] ton convert (varchar (10), dtime, 120) = convert (varchar (10), time, 120) final result:

For more information, see my blog:
Blog.sina.com.cn/s/blog_9f39f0c70102ux87.html
In the fifth point, the title is "5. Monthly Statistics query"