有关生成SQL时间报表问题,在线等``跪求解答

Question

数据模拟？每月的导出400个EXCEL文件 啥意思？

Answer 1

打卡1-打卡4的时间都采用随机生成,导出为EXCEL文件,每月的导出400个EXCEL文件,但是数据不能完全一样?什么意思?让我们帮你生成数据?且不能重复?

Answer 2

在查询要析器执行前, 在菜单项里,"查询">>"查询结果保存为文件". 然后执行查询,会提示你保存,保存为 x.xls就可以了.
如果不想这么保存,可以直接生成临时表,然后用opendatasorce或openrowset或bcp等等方式来生成excel文件.
DECLARE @sd DATETIME,@ed DATETIME,@d INT,@i INT
SELECT @sd='2007-1-1',@ed='2007-2-28',@d=DATEDIFF(dd,@sd,@ed),@i=0  --这里的@sd和@ed你自己调为你要的值.我调试不想输出那么多行.
DECLARE @t TABLE (date_ DATETIME,week_ TINYINT,log1 VARCHAR(5),log2 VARCHAR(5),log3 VARCHAR(5),log4 VARCHAR(5),log5 VARCHAR(5),log6 VARCHAR(5),log7 VARCHAR(5),log8 VARCHAR(5))
WHILE @i<@d
BEGIN
INSERT @t(date_) SELECT DATEADD(dd,@i,@sd)
SET @i=@i+1
END
SELECT CONVERT(VARCHAR(10),date_,120) 日期,DATEPART(dw,date_)-1 星期,ISNULL(t1,'') 打卡1,ISNULL(t2,'') 打卡2,ISNULL(t3,'') 打卡3,ISNULL(t4,'') 打卡4,ISNULL(log5,'') 打卡5,ISNULL(log6,'') 打卡6,ISNULL(log7,'') 打卡7,ISNULL(log8,'') 打卡8
FROM @t a
LEFT JOIN 
(SELECT sWD=1,eWD=5,t1='8:15-8:35',t2='12:00-12:25',t3='13:15-13:35',t4='5:30-5:50'
UNION ALL SELECT 6,0,'','','','') b
ON DATEPART(dw,date_)-1 BETWEEN sWD AND eWD

Answer 3

fcuandy 你理解错了
打卡时间就是随机生成一个时间`在那个范围内就可以了
而不是一个时间段啊

Answer 4

OKDECLARE @sd DATETIME,@ed DATETIME,@d INT,@i INT
SELECT @sd='2007-1-1',@ed='2007-2-28',@d=DATEDIFF(dd,@sd,@ed),@i=0  --这里的@sd和@ed你自己调为你要的值.我调试不想输出那么多行.
DECLARE @t TABLE (date_ DATETIME,week_ TINYINT,log1 VARCHAR(5),log2 VARCHAR(5),log3 VARCHAR(5),log4 VARCHAR(5),log5 VARCHAR(5),log6 VARCHAR(5),log7 VARCHAR(5),log8 VARCHAR(5))
declare @s varchar(12)
select @s='8:10-8:24'
WHILE @i<@d
    BEGIN
        INSERT @t(date_) SELECT DATEADD(dd,@i,@sd)
        SET @i=@i+1
    END
SELECT CONVERT(VARCHAR(10),date_,120) 日期,DATEPART(dw,date_)-1 星期,
ISNULL(left(t1,charindex(':',t1))+rtrim(cast(stuff(left(t1,charindex('-',t1)-1),1,charindex(':',t1),'') as int)+cast(rand(checksum(newid())) * (cast(right(t1,charindex(':',reverse(t1))-1) as int)-cast(stuff(left(t1,charindex('-',t1)-1),1,charindex(':',t1),'') as int)) as int)),'') 打卡1,
ISNULL(left(t2,charindex(':',t2))+rtrim(cast(stuff(left(t2,charindex('-',t2)-1),1,charindex(':',t2),'') as int)+cast(rand(checksum(newid())) * (cast(right(t2,charindex(':',reverse(t2))-1) as int)-cast(stuff(left(t2,charindex('-',t2)-1),1,charindex(':',t2),'') as int)) as int)),'') 打卡2,
ISNULL(left(t3,charindex(':',t3))+rtrim(cast(stuff(left(t3,charindex('-',t3)-1),1,charindex(':',t3),'') as int)+cast(rand(checksum(newid())) * (cast(right(t3,charindex(':',reverse(t3))-1) as int)-cast(stuff(left(t3,charindex('-',t3)-1),1,charindex(':',t3),'') as int)) as int)),'') 打卡3,
ISNULL(left(t4,charindex(':',t4))+rtrim(cast(stuff(left(t4,charindex('-',t4)-1),1,charindex(':',t4),'') as int)+cast(rand(checksum(newid())) * (cast(right(t4,charindex(':',reverse(t4))-1) as int)-cast(stuff(left(t4,charindex('-',t4)-1),1,charindex(':',t4),'') as int)) as int)),'') 打卡4,
ISNULL(log5,'') 打卡5,ISNULL(log6,'') 打卡6,ISNULL(log7,'') 打卡7,ISNULL(log8,'') 打卡8
    FROM @t a
LEFT JOIN 
    (SELECT sWD=1,eWD=5,t1='8:15-8:35',t2='12:00-12:25',t3='13:15-13:35',t4='5:30-5:50'
    UNION ALL SELECT 6,0,null,null,null,null) b  --这里将''改为null为了防止以后改时间设置造成连接非null从而字串处理函数出错.
    ON DATEPART(dw,date_)-1 BETWEEN sWD AND eWD
/*
2007-01-01 1 8:24 12:3 13:33 5:40
2007-01-02 2 8:20 12:14 13:25 5:48
2007-01-03 3 8:17 12:15 13:21 5:44
2007-01-04 4 8:20 12:6 13:33 5:45
2007-01-05 5 8:32 12:17 13:15 5:30
2007-01-06 6
2007-01-07 0
2007-01-08 1 8:15 12:12 13:24 5:49
2007-01-09 2 8:25 12:6 13:24 5:31
2007-01-10 3 8:22 12:4 13:17 5:30
2007-01-11 4 8:20 12:18 13:31 5:45
2007-01-12 5 8:28 12:22 13:23 5:33
2007-01-13 6
2007-01-14 0
2007-01-15 1 8:28 12:18 13:20 5:41
2007-01-16 2 8:26 12:20 13:27 5:33
2007-01-17 3 8:17 12:20 13:17 5:48
2007-01-18 4 8:15 12:2 13:24 5:42
2007-01-19 5 8:31 12:14 13:20 5:39
2007-01-20 6
2007-01-21 0
2007-01-22 1 8:23 12:10 13:24 5:45
2007-01-23 2 8:30 12:23 13:27 5:39
2007-01-24 3 8:30 12:18 13:16 5:40
2007-01-25 4 8:18 12:13 13:23 5:30
2007-01-26 5 8:17 12:19 13:23 5:40
2007-01-27 6
2007-01-28 0
2007-01-29 1 8:22 12:21 13:28 5:32
2007-01-30 2 8:26 12:1 13:17 5:46
2007-01-31 3 8:24 12:10 13:25 5:34
2007-02-01 4 8:26 12:9 13:20 5:38
2007-02-02 5 8:23 12:6 13:19 5:36
2007-02-03 6
2007-02-04 0
2007-02-05 1 8:28 12:3 13:21 5:31
2007-02-06 2 8:18 12:3 13:24 5:40
2007-02-07 3 8:25 12:2 13:26 5:43
2007-02-08 4 8:22 12:14 13:20 5:34
2007-02-09 5 8:30 12:3 13:30 5:49
2007-02-10 6
2007-02-11 0
2007-02-12 1 8:27 12:23 13:26 5:38
2007-02-13 2 8:21 12:17 13:18 5:32
2007-02-14 3 8:15 12:7 13:29 5:46
2007-02-15 4 8:21 12:6 13:23 5:31
2007-02-16 5 8:30 12:17 13:32 5:36
2007-02-17 6
2007-02-18 0
2007-02-19 1 8:18 12:4 13:17 5:32
2007-02-20 2 8:27 12:11 13:31 5:41
2007-02-21 3 8:30 12:4 13:17 5:41
2007-02-22 4 8:31 12:6 13:32 5:33
2007-02-23 5 8:32 12:9 13:28 5:31
2007-02-24 6
2007-02-25 0
2007-02-26 1 8:24 12:14 13:20 5:46
2007-02-27 2 8:25 12:18 13:27 5:45
*/

Answer 5

导出的话,你可以直接生成临时表.将表变量@t改为临时表,怎么做都可以.
另外, 需要说明的是,你的每个时间范围的上下限小时都是相同的,所以我用的分钟的int值直接加的.
比如 8:10-8:35  
如果你的时间范围的上下限小时值不相同
比如 8:50-9:25 这样的,那么 这种取法不对.容错性好点的写法应该是
DECLARE @sd DATETIME,@ed DATETIME,@d INT,@i INT
SELECT @sd='2007-1-1',@ed='2007-2-28',@d=DATEDIFF(dd,@sd,@ed),@i=0  --这里的@sd和@ed你自己调为你要的值.我调试不想输出那么多行.
DECLARE @t TABLE (date_ DATETIME,week_ TINYINT,log1 VARCHAR(5),log2 VARCHAR(5),log3 VARCHAR(5),log4 VARCHAR(5),log5 VARCHAR(5),log6 VARCHAR(5),log7 VARCHAR(5),log8 VARCHAR(5))
declare @s varchar(12)
select @s='8:10-8:24'
WHILE @i<@d
    BEGIN
        INSERT @t(date_) SELECT DATEADD(dd,@i,@sd)
        SET @i=@i+1
    END
SELECT CONVERT(VARCHAR(10),date_,120) 日期,DATEPART(dw,date_)-1 星期,
ISNULL(convert(varchar(5),dateadd(mi,cast(rand(checksum(newid())) * datediff(mi,left(t1,charindex('-',t1)-1),stuff(t1,1,charindex('-',t1),'')) as int),left(t1,charindex('-',t1)-1)),114),'') 打卡1,
ISNULL(convert(varchar(5),dateadd(mi,cast(rand(checksum(newid())) * datediff(mi,left(t2,charindex('-',t2)-1),stuff(t2,1,charindex('-',t2),'')) as int),left(t2,charindex('-',t2)-1)),114),'') 打卡2,
ISNULL(convert(varchar(5),dateadd(mi,cast(rand(checksum(newid())) * datediff(mi,left(t3,charindex('-',t3)-1),stuff(t3,1,charindex('-',t3),'')) as int),left(t3,charindex('-',t3)-1)),114),'') 打卡3,
ISNULL(convert(varchar(5),dateadd(mi,cast(rand(checksum(newid())) * datediff(mi,left(t4,charindex('-',t4)-1),stuff(t4,1,charindex('-',t4),'')) as int),left(t4,charindex('-',t4)-1)),114),'') 打卡4,
ISNULL(log5,'') 打卡5,ISNULL(log6,'') 打卡6,ISNULL(log7,'') 打卡7,ISNULL(log8,'') 打卡8
    FROM @t a
LEFT JOIN 
    (SELECT sWD=1,eWD=5,t1='8:15-8:35',t2='12:00-12:25',t3='13:15-13:35',t4='17:30-17:50'
    UNION ALL SELECT 6,0,null,null,null,null) b
    ON DATEPART(dw,date_)-1 BETWEEN sWD AND eWD

Answer 6

随手敲的,不排除手误.
CREATE PROC p
(@sd DATETIME,
@ed DATETIME)
AS
BEGIN
  --将declare @sd和ed还有给ed和sd赋值的去掉.
  ...END
--然后
declare @i INT,@k INT,@sd DATETIME,@ed DATETIME,@s VARCHAR(1000)
SELECT @i=0,@k=400,@sd='2007-1-1',@ed='2007-10-1'
WHILE @i<@k
    BEGIN
        SELECT @s='bcp "EXEC 库名..p ''' /*这里的p就是存储过程名*/ + CONVERT(VARCHAR(10),@sd,120) + ''',''' + CONVERT(VARCHAR(10),@ed,120)  + '''" queryout "e:\tmp\myExecl' + RTRIM(@i) + '.xls" /c /Usa -P密码',@i=@i+1
        EXEC master..xp_cmdshell @s
    END