请教，批量补充当月缺失日期的数据。

Question

有张表是这样的：
id                 shijian                             jieguo
1          2011-08-19 13:01:05.000                       有其中id是自动编号的，shijian这字段应该是每月连续的，jieguo都按“有”添加吧。
补充shijian中在当月中缺失的日期，如果有缺失的日期按照缺失的日期值添加，时间随机在13:00:00到14:00:00之间。
我想写个SQL语句每月月末执行一次。自己不会，请帮助。

Answer 1

declare @dateatart datetime
declare @dateend datetime
set @dateatart ='2012-08-18'
set @dateend='2012-08-31'
select dateadd(dd,number,@dateatart)
from master..spt_values where type='p' and number between 0 and datediff(dd,@dateatart,@dateend )

Answer 2

--如何用"最小缺失数"实现确实日期的自动补全-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE,
备注 VARCHAR(100)
)
GO
INSERT TBL
SELECT '2012-03-02','B' UNION ALL
SELECT '2012-03-05','C' UNION ALL
SELECT '2012-03-06','D' UNION ALL
SELECT '2012-03-07','E' UNION ALL
SELECT '2012-03-09','F' UNION ALL
SELECT '2012-03-11','G' UNION ALL
SELECT '2012-03-12','H' UNION ALL
SELECT '2012-03-13','I' UNION ALL
SELECT '2012-03-15','J' UNION ALL
SELECT '2012-03-19','K' UNION ALL
SELECT '2012-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 日期'
DECLARE @MINMISS DATE
SET @MINMISS=(
SELECT COALESCE(DATEADD(DD,1,MIN(A.日期)),
'2012-03-01') AS MISSING
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B 
WHERE B.日期=DATEADD(DD,1,A.日期))
AND EXISTS (
SELECT 1 FROM TBL WHERE 日期='2012-03-01'))
PRINT @MINMISS
WHILE @MINMISS<=@ENDTIME
BEGIN
INSERT TBL(日期) VALUES(@MINMISS)
SELECT @MINMISS=(
SELECT DATEADD(DD,1,MIN(A.日期))
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B 
WHERE B.日期=DATEADD(DD,1,A.日期))
)
END
EXEC(@SQL)EXEC P_SP '2012-03-20'/*
日期    备注
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-11    G
2012-03-12    H
2012-03-13    I2012-03-14    NULL2012-03-15    J2012-03-16    NULL2012-03-17    NULL2012-03-18    NULL2012-03-19    K2012-03-20    L
*/------------------------------------------------------------------------------------------------------方法二，利用递归实现：
-->生成测试数据:GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE
)
GO
INSERT TBL
SELECT '2012-03-01' UNION ALL
SELECT '2012-03-31'
--利用递归实现输出三月份的所有日期：
go
declare @date date
select @date=MAX(日期) from tbl
;with t
as(
select * from tbl
union all
select dateadd(dd,1,a.日期) from t a
where not exists(select * from tbl b
where b.日期=DATEADD(DD,1,a.日期)
)
and a.日期<@date
)
select *from t order by 日期/*
日期
2012-03-01
2012-03-02
2012-03-03
2012-03-04
2012-03-05
2012-03-06
2012-03-07
2012-03-08
2012-03-09
2012-03-10
2012-03-11
2012-03-12
2012-03-13
2012-03-14
2012-03-15
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
*/---------------------------------------------------
---------------------------------------------------
方法三：利用系统表构造实现
/*
    create table #tB(
    [A] int,
    [C2] varchar(10),
    [C3] datetime
    ) 
    insert #tB 
    select 1,'dfgsdfgsdf','2010-02-01' union all 
    select 2,'dfgsdfgsdf','2010-02-02' union all 
    select 3,'dfgsdfgsdf','2010-02-03' union all 
    select 4,'dfgsdfgsdf','2010-02-04' union all 
    select 4,'dfgsdfgsdf','2010-09-04' union all 
    select 5,'dfgsdfgsdf','2010-09-08' union all 
    select 5,'dfgsdfgsdf','2010-03-08' union all 
    select 6,'dfgsdfgsdf','2010-03-11' union all 
    select 4,'dfgsdfgsdf','2010-05-04' union all 
    select 5,'dfgsdfgsdf','2010-02-08' union all 
    select 6,'dfgsdfgsdf','2010-05-11' union all 
    select 7,'dfgsdfgsdf','2010-05-14' union all 
    select 8,'dfgsdfgsdf','2010-05-16' union all 
    select 7,'dfgsdfgsdf','2010-03-14' union all 
    select 8,'dfgsdfgsdf','2010-03-16' union all 
    select 6,'dfgsdfgsdf','2010-09-11' union all 
    select 7,'dfgsdfgsdf','2010-09-14' union all 
    select 8,'dfgsdfgsdf','2010-09-16' union all 
    select 9,'dfgsdfgsdf','2010-11-17'
想得到如下结果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','2010-02-01' union all 
select 2,'dfgsdfgsdf','2010-02-02' union all 
select 3,'dfgsdfgsdf','2010-02-03' union all 
select 4,'dfgsdfgsdf','2010-02-04' union all 
select 4,'dfgsdfgsdf','2010-09-04' union all 
select 5,'dfgsdfgsdf','2010-09-08' union all 
select 5,'dfgsdfgsdf','2010-03-08' union all 
select 6,'dfgsdfgsdf','2010-03-11' union all 
select 4,'dfgsdfgsdf','2010-05-04' union all 
select 5,'dfgsdfgsdf','2010-02-08' union all 
select 6,'dfgsdfgsdf','2010-05-11' union all 
select 7,'dfgsdfgsdf','2010-05-14' union all 
select 8,'dfgsdfgsdf','2010-05-16' union all 
select 7,'dfgsdfgsdf','2010-03-14' union all 
select 8,'dfgsdfgsdf','2010-03-16' union all 
select 6,'dfgsdfgsdf','2010-09-11' union all 
select 7,'dfgsdfgsdf','2010-09-14' union all 
select 8,'dfgsdfgsdf','2010-09-16' union all 
select 9,'dfgsdfgsdf','2010-11-17'
select 
isnull(c1,'2010-'+right('00'+ltrim(number),2)) as [month],--实现按月份递增
isnull(c2,0) as total,
ltrim(cast(isnull(c2,0)*100*1.0/(select sum([A]) from tbl) as decimal(18,2)))+'%' as [percent]
--求百分比
 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-03    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%*/--献丑了

Answer 3

先生成上个月的所有日期表，和已有数据对比，找出缺失的日期，再插入USE test
GO
DROP TABLE t;
GO
DROP TABLE t2;
GO
CREATE TABLE t
(
id int identity
,date datetime
)
GO
CREATE TABLE t2
(
id int identity
,date datetime
,value nvarchar(20)
)
GOINSERT INTO t2
VALUES
('2012-07-01','有'),('2012-07-13','有')DECLARE @start_date datetime;
DECLARE @insert_date datetime;SET @start_date =CAST ( (CONVERT(varchar(7),DATEADD(m,-1,GETDATE()),120)+'-01') AS datetime);
SET @start_date = DATEADD(hour,13,@start_date);
SET @insert_date = @start_date
SET @insert_date = DATEADD ( minute , CAST (rand()*60 AS tinyint) , @insert_date)
SET @insert_date = DATEADD ( SECOND , CAST (rand()*60 AS tinyint) , @insert_date)
SET @insert_date = DATEADD ( MILLISECOND , CAST (rand()*1000 AS smallint) , @insert_date)WHILE DATEPART(mm,@start_date) = DATEPART(mm,@insert_date)
BEGIN
INSERT INTO t VALUES ( @insert_date );
SET @insert_date =CAST( CONVERT(varchar(10), DATEADD(DAY,1,@insert_date),120) AS datetime);
SET @insert_date = DATEADD(HOUR,13,@insert_date);
SET @insert_date = DATEADD ( minute , CAST (rand()*60 AS tinyint) , @insert_date)
SET @insert_date = DATEADD ( SECOND , CAST (rand()*60 AS tinyint) , @insert_date)
SET @insert_date = DATEADD ( MILLISECOND , CAST (rand()*1000 AS smallint) , @insert_date)
END
GOINSERT INTO t2(date,value)
SELECT t.date,'有' AS value
FROM t
WHERE NOT EXISTS (
SELECT 'True'
FROM t2
WHERE CONVERT(varchar(10),t.date,120)= CONVERT( varchar(10),t2.date,120)
)SELECT * FROM t2id          date                    value
----------- ----------------------- --------------------
1           2012-07-01 00:00:00.000 有
2           2012-07-13 00:00:00.000 有
3           2012-07-02 13:17:41.937 有
4           2012-07-03 13:42:38.310 有
5           2012-07-04 13:12:45.767 有
6           2012-07-05 13:16:58.940 有
7           2012-07-06 13:23:46.410 有
8           2012-07-07 13:33:41.267 有
9           2012-07-08 13:54:43.067 有
10          2012-07-09 13:35:33.950 有
11          2012-07-10 13:06:14.790 有
12          2012-07-11 13:17:15.963 有
13          2012-07-12 13:34:39.837 有
14          2012-07-14 13:57:36.580 有
15          2012-07-15 13:31:41.787 有
16          2012-07-16 13:58:33.943 有
17          2012-07-17 13:04:41.533 有
18          2012-07-18 13:06:05.163 有
19          2012-07-19 13:18:17.977 有
20          2012-07-20 13:07:27.150 有
21          2012-07-21 13:08:29.993 有
22          2012-07-22 13:39:06.967 有
23          2012-07-23 13:09:09.030 有
24          2012-07-24 13:58:34.247 有
25          2012-07-25 13:47:11.357 有
26          2012-07-26 13:33:52.317 有
27          2012-07-27 13:37:20.537 有
28          2012-07-28 13:57:07.770 有
29          2012-07-29 13:15:03.567 有
30          2012-07-30 13:13:45.790 有
31          2012-07-31 13:20:36.723 有(31 行受影响)

Answer 4

有张表是这样的：
id shijian jieguo
1 2011-08-01 13:01:05.000 有
2 2011-08-01 15:22:07.000 无
3 2011-08-02 13:18:23.000 有
4 2011-08-02 15:06:53.000 无
5 2011-08-04 13:16:21.000 有
6 2011-08-07 15:09:42.000 无
7 2011-08-19 13:01:05.000 有
8 2011-08-19 15:09:42.000 无其中id是自动编号的，shijian这字段应该是每月连续的，不连续的要按缺失的日期补上数据，时间一天2次，一次随机在13:00:00到14:00:00之间，一次随机在13:00:00到14:00:00之间；jieguo在13:00:00到14:00:00之间按“有”添加，在15:00:00到16:00:00之间按“无”添加。
补充下，
1、我的SQL是SQL2000的。
2、对比数据是执行脚本当时的月份，从1号到执行脚本当天。比如说今天我执行了，那么应该就是判断2012-08-01到2012-08-20这些天中丢失的数据，并补上丢失的数据，已经有的要跳过。

Answer 5

有张表是这样的：
id shijian jieguo
1 2011-08-01 13:01:05.000 有
2 2011-08-01 15:22:07.000 无
3 2011-08-02 13:18:23.000 有
4 2011-08-02 15:06:53.000 无
5 2011-08-04 13:16:21.000 有
6 2011-08-07 15:09:42.000 无
7 2011-08-19 13:01:05.000 有
8 2011-08-19 15:09:42.000 无其中id是自动编号的，shijian这字段应该是每月连续的，不连续的要按缺失的日期补上数据，时间一天2次，两条数据，一次随机在13:00:00到14:00:00之间，一次随机在13:00:00到14:00:00之间；jieguo在13:00:00到14:00:00之间按“有”添加，在15:00:00到16:00:00之间按“无”添加。
补充下，
1、我的SQL是SQL2000的。
2、对比数据是执行脚本当时的月份，从1号到执行脚本当天。比如说今天我执行了，那么应该就是判断2012-08-01到2012-08-20这些天中丢失的数据，并补上丢失的数据，已经有的要跳过。

Answer 6

2000我没试过，有问题请帖出错误消息--准备初始数据
USE test
GO
DROP TABLE t;
GO
DROP TABLE t2;
GO
--存放按日期连续的随机数据
CREATE TABLE t
(
id int identity
,date datetime
,value nvarchar(20)
)
GO
--存放测试数据
CREATE TABLE t2
(
id int identity
,date datetime
,value nvarchar(20)
)
GO
--插入测试数据
INSERT INTO t2
VALUES
('2012-08-01 13:00:00.000','有')
,('2012-08-01 15:00:00.000','无')
,('2012-08-02 13:00:00.000','有')
,('2012-08-02 15:00:00.000','无')
,('2012-08-04 13:00:00.000','有')
,('2012-08-07 15:00:00.000','无')
,('2012-08-19 13:00:00.000','有')
,('2012-08-19 15:00:00.000','无')
GO
--插入截至到昨天的连续随机日期到表t
DECLARE @start_date datetime;
DECLARE @insert_date datetime;
DECLARE @today datetime = getdate();SET @start_date = CAST ( (CONVERT(varchar(7),GETDATE(),120)+'-01') AS datetime);
SET @start_date = DATEADD(hour,13,@start_date);
SET @insert_date = @start_date SET @insert_date = DATEADD ( minute , CAST (rand()*60 AS tinyint) , @insert_date)
SET @insert_date = DATEADD ( SECOND , CAST (rand()*60 AS tinyint) , @insert_date)
SET @insert_date = DATEADD ( MILLISECOND , CAST (rand()*1000 AS smallint) , @insert_date)--当下一条要插入的数据小于当前日期执行
WHILE   DATEPART(day,@insert_date) < DATEPART(day,@today)
BEGIN
INSERT INTO t VALUES ( @insert_date,'有' );
--准备15:00-16:00的数据
SET @insert_date = CAST( CONVERT(varchar(10), @insert_date) AS datetime);
SET @insert_date = DATEADD(HOUR,15,@insert_date);
SET @insert_date = DATEADD ( minute , CAST (rand()*60 AS tinyint) , @insert_date)
SET @insert_date = DATEADD ( SECOND , CAST (rand()*60 AS tinyint) , @insert_date)
SET @insert_date = DATEADD ( MILLISECOND , CAST (rand()*1000 AS smallint) , @insert_date)
INSERT INTO t VALUES ( @insert_date,'无' );
--准备第二天的数据
SET @insert_date =CAST( CONVERT(varchar(10), DATEADD(DAY,1,@insert_date),120) AS datetime);
SET @insert_date = DATEADD(HOUR,13,@insert_date);
SET @insert_date = DATEADD ( minute , CAST (rand()*60 AS tinyint) , @insert_date)
SET @insert_date = DATEADD ( SECOND , CAST (rand()*60 AS tinyint) , @insert_date)
SET @insert_date = DATEADD ( MILLISECOND , CAST (rand()*1000 AS smallint) , @insert_date)
END
GO
--查看t的内容
SELECT * FROM t--与t2比较，插入缺失的日期
INSERT INTO t2(date,value)
SELECT t.date,t.value AS value
FROM t
WHERE NOT EXISTS (
SELECT 'True'
FROM t2
WHERE CONVERT(varchar(13),t.date,120)= CONVERT( varchar(13),t2.date,120)
)
--查看结果
SELECT * FROM t2
ORDER BY date

Answer 7

消息 170，级别 15，状态 1，第 5 行
第 5 行: ',' 附近有语法错误。
消息 139，级别 15，状态 1，第 4 行
不能向局部变量赋予默认值。
消息 137，级别 15，状态 2，第 15 行
必须声明变量 '@today'。

Answer 8

我的代码可能不能直接使用，你建个测试数据库，把代码全部复制过去，运行试试呢？
或者加我QQ，254113859.调整了生成随机时间的方法。--插入截至到昨天的连续随机日期到表t
DECLARE @start_date datetime;
DECLARE @insert_date datetime;
DECLARE @today datetime = getdate();SET @start_date = CAST ( (CONVERT(varchar(7),GETDATE(),120)+'-01') AS datetime);
SET @start_date = DATEADD(hour,13,@start_date);
SET @insert_date = @start_date SET @insert_date = @insert_date + rand() * 1.0/24--当下一条要插入的数据小于当前日期执行
WHILE   DATEPART(day,@insert_date) < DATEPART(day,@today)
BEGIN
INSERT INTO t VALUES ( @insert_date,'有' );
--准备15:00-16:00的数据
SET @insert_date =CAST(  CONVERT(varchar(10),@insert_date,120) as datetime);
SET @insert_date = DATEADD(HOUR,15,@insert_date);
SET @insert_date = @insert_date + rand() * 1.0/24 INSERT INTO t VALUES ( @insert_date,'无' );
--准备第二天的数据
SET @insert_date = DATEadd(day,1,CAST(  CONVERT(varchar(10),@insert_date,120) as datetime));
SET @insert_date = DATEADD(HOUR,13,@insert_date);
SET @insert_date = @insert_date + rand() * 1.0/24END
GO

Answer 9

我把这句DECLARE @today datetime = getdate();分两句写，不会提示“必须声明变量 '@today'”了。
先声明，在赋值。
但是整段SQL脚本里面没有已经存在值的判断，执行2遍就生成重复的了。
补上丢失的数据，已经有的要跳过。