--生成测试数据 create table TF_KQ(YG_NO varchar(10),BAN_NO varchar(10),TRS_DD datetime) insert into TF_KQ select 'A002','002','2004-10-08 07:23:00.000' insert into TF_KQ select 'A002','002','2004-10-08 07:22:00.000' insert into TF_KQ select 'A002','002','2004-10-08 11:31:00.000' insert into TF_KQ select 'A002','002','2004-10-08 12:04:00.000' insert into TF_KQ select 'A002','002','2004-10-08 12:29:00.000' insert into TF_KQ select 'A002','002','2004-10-08 12:45:00.000' insert into TF_KQ select 'A002','002','2004-10-08 12:51:00.000' insert into TF_KQ select 'A002','002','2004-10-08 13:24:00.000' insert into TF_KQ select 'A002','002','2004-10-08 17:33:00.000' insert into TF_KQ select 'A002','002','2004-10-08 17:45:00.000' insert into TF_KQ select 'A002','002','2004-10-08 19:22:00.000' insert into TF_KQ select 'A002','002','2004-10-08 19:25:00.000' insert into TF_KQ select 'A002','002','2004-10-08 20:22:00.000' insert into TF_KQ select 'A002','002','2004-10-09 02:25:00.000' insert into TF_KQ select 'A002','002','2004-10-09 07:23:00.000' insert into TF_KQ select 'A002','002','2004-10-09 07:22:00.000' insert into TF_KQ select 'A002','002','2004-10-09 11:31:00.000' insert into TF_KQ select 'A002','002','2004-10-09 12:04:00.000' insert into TF_KQ select 'A002','002','2004-10-09 12:29:00.000' insert into TF_KQ select 'A002','002','2004-10-09 12:45:00.000' insert into TF_KQ select 'A002','002','2004-10-09 12:51:00.000' insert into TF_KQ select 'A002','002','2004-10-09 13:24:00.000' insert into TF_KQ select 'A002','002','2004-10-09 17:33:00.000' insert into TF_KQ select 'A002','002','2004-10-09 17:45:00.000' insert into TF_KQ select 'A002','002','2004-10-09 19:22:00.000' insert into TF_KQ select 'A002','002','2004-10-09 19:25:00.000' insert into TF_KQ select 'A002','002','2004-10-09 20:22:00.000' insert into TF_KQ select 'A002','002','2004-10-10 02:25:00.000' insert into TF_KQ select 'A002','002','2004-10-10 07:23:00.000' insert into TF_KQ select 'A002','002','2004-10-10 07:22:00.000' insert into TF_KQ select 'A002','002','2004-10-10 11:31:00.000' insert into TF_KQ select 'A002','002','2004-10-10 12:04:00.000' insert into TF_KQ select 'A002','002','2004-10-10 12:29:00.000' insert into TF_KQ select 'A002','002','2004-10-10 12:45:00.000' insert into TF_KQ select 'A002','002','2004-10-10 12:51:00.000' insert into TF_KQ select 'A002','002','2004-10-10 13:24:00.000' insert into TF_KQ select 'A002','002','2004-10-10 17:33:00.000' insert into TF_KQ select 'A002','002','2004-10-10 17:45:00.000' insert into TF_KQ select 'A002','002','2004-10-10 19:22:00.000' insert into TF_KQ select 'A002','002','2004-10-10 19:25:00.000' insert into TF_KQ select 'A002','002','2004-10-10 20:22:00.000' insert into TF_KQ select 'A002','002','2004-10-11 02:25:00.000' create table TF_SKTZ(YG_NO varchar(20),TRS_DD datetime,CHK_ID char(1)) insert into TF_SKTZ select 'A002','2005-07-08 14:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-08 18:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-08 19:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 08:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 12:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 14:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 18:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 19:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 00:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 08:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 12:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 14:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 18:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 19:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-11 08:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-27 12:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-27 14:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-08-09 12:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-08-09 14:00:00.000','Y' create table TF_TZ(YG_NO varchar(20),SZ_NO varchar(4),TRS_DD datetime,QTY numeric(28,8),CH_ID varchar(1)) insert into TF_TZ select 'A002','00D','2004-10-08 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2004-10-09 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2005-07-07 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2005-07-08 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2005-07-09 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2005-07-11 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2005-07-27 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2005-08-09 00:00:00.000',8.00000000,'Y' GO--创建存储过程 CREATE PROCEDURE SP_GetDetail @SDATE DATETIME, --日期范围起始日期 @EDATE DATETIME, --日期范围截止日期 @SYG_NO VARCHAR(20),--工号范围起始工号 @EYG_NO VARCHAR(20) --工号范围截止工号 AS BEGIN SELECT * INTO #T from TF_KQ INSERT INTO #T SELECT YG_NO,NULL,TRS_DD FROM TF_SKTZ
SELECT b.YG_NO, b.SZ_NO, b.TRS_DD, SB_Y = CONVERT(CHAR(5),b.SB_Y,108), SC_C = CONVERT(CHAR(5),b.SC_C,108), SC_Y = CONVERT(CHAR(5),b.SC_Y,108), SB_C = CONVERT(CHAR(5),b.SB_C,108), SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108), SJ_C = CONVERT(CHAR(5),b.SJ_C,108), JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0), KJ = CASE WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0 THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0)) ELSE 0 END, GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0), b.QTY FROM (SELECT a.YG_NO, SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710), a.TRS_DD, SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ), SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ), SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ), SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110), SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171), SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710), a.QTY FROM (SELECT YG_NO,SZ_NO,TRS_DD,QTY,CH_ID FROM TF_TZ UNION SELECT DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL FROM TF_KQ t WHERE NOT EXISTS(SELECT 1 FROM TF_TZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a WHERE (a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01')) AND (a.YG_NO BETWEEN @SYG_NO AND @EYG_NO) AND EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
END GO --执行存储过程 EXEC SP_GetDetail '2004-01-01','2006-01-01','A002','A008' GO
--生成测试数据 create table TF_KQ(YG_NO varchar(10),BAN_NO varchar(10),TRS_DD datetime) insert into TF_KQ select 'A002','002','2004-10-08 07:23:00.000' insert into TF_KQ select 'A002','002','2004-10-08 07:22:00.000' insert into TF_KQ select 'A002','002','2004-10-08 11:31:00.000' insert into TF_KQ select 'A002','002','2004-10-08 12:04:00.000' insert into TF_KQ select 'A002','002','2004-10-08 12:29:00.000' insert into TF_KQ select 'A002','002','2004-10-08 12:45:00.000' insert into TF_KQ select 'A002','002','2004-10-08 12:51:00.000' insert into TF_KQ select 'A002','002','2004-10-08 13:24:00.000' insert into TF_KQ select 'A002','002','2004-10-08 17:33:00.000' insert into TF_KQ select 'A002','002','2004-10-08 17:45:00.000' insert into TF_KQ select 'A002','002','2004-10-08 19:22:00.000' insert into TF_KQ select 'A002','002','2004-10-08 19:25:00.000' insert into TF_KQ select 'A002','002','2004-10-08 20:22:00.000' insert into TF_KQ select 'A002','002','2004-10-09 02:25:00.000' insert into TF_KQ select 'A002','002','2004-10-09 07:23:00.000' insert into TF_KQ select 'A002','002','2004-10-09 07:22:00.000' insert into TF_KQ select 'A002','002','2004-10-09 11:31:00.000' insert into TF_KQ select 'A002','002','2004-10-09 12:04:00.000' insert into TF_KQ select 'A002','002','2004-10-09 12:29:00.000' insert into TF_KQ select 'A002','002','2004-10-09 12:45:00.000' insert into TF_KQ select 'A002','002','2004-10-09 12:51:00.000' insert into TF_KQ select 'A002','002','2004-10-09 13:24:00.000' insert into TF_KQ select 'A002','002','2004-10-09 17:33:00.000' insert into TF_KQ select 'A002','002','2004-10-09 17:45:00.000' insert into TF_KQ select 'A002','002','2004-10-09 19:22:00.000' insert into TF_KQ select 'A002','002','2004-10-09 19:25:00.000' insert into TF_KQ select 'A002','002','2004-10-09 20:22:00.000' insert into TF_KQ select 'A002','002','2004-10-10 02:25:00.000' insert into TF_KQ select 'A002','002','2004-10-10 07:23:00.000' insert into TF_KQ select 'A002','002','2004-10-10 07:22:00.000' insert into TF_KQ select 'A002','002','2004-10-10 11:31:00.000' insert into TF_KQ select 'A002','002','2004-10-10 12:04:00.000' insert into TF_KQ select 'A002','002','2004-10-10 12:29:00.000' insert into TF_KQ select 'A002','002','2004-10-10 12:45:00.000' insert into TF_KQ select 'A002','002','2004-10-10 12:51:00.000' insert into TF_KQ select 'A002','002','2004-10-10 13:24:00.000' insert into TF_KQ select 'A002','002','2004-10-10 17:33:00.000' insert into TF_KQ select 'A002','002','2004-10-10 17:45:00.000' insert into TF_KQ select 'A002','002','2004-10-10 19:22:00.000' insert into TF_KQ select 'A002','002','2004-10-10 19:25:00.000' insert into TF_KQ select 'A002','002','2004-10-10 20:22:00.000' insert into TF_KQ select 'A002','002','2004-10-11 02:25:00.000' create table TF_SKTZ(YG_NO varchar(20),TRS_DD datetime,CHK_ID char(1)) insert into TF_SKTZ select 'A002','2005-07-08 14:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-08 18:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-08 19:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 08:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 12:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 14:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 18:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 19:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 00:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 08:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 12:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 14:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 18:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 19:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-11 08:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-27 12:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-27 14:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-08-09 12:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-08-09 14:00:00.000','Y' create table TF_TZ(YG_NO varchar(20),SZ_NO varchar(4),TRS_DD datetime,QTY numeric(28,8),CH_ID varchar(1)) insert into TF_TZ select 'A002','00D','2004-10-08 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2004-10-09 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2005-07-07 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2005-07-08 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2005-07-09 00:00:00.000',8.00000000,'Y' insert into TF_TZ select 'A002','00E','2005-07-11 00:00:00.000',8.00000000,'Y' GO
--创建存储过程 CREATE PROCEDURE SP_GetDetail @SDATE DATETIME, --日期范围起始日期 @EDATE DATETIME, --日期范围截止日期 @SYG_NO VARCHAR(20),--工号范围起始工号 @EYG_NO VARCHAR(20) --工号范围截止工号 AS BEGIN SELECT * INTO #T FROM TF_KQ
INSERT INTO #T SELECT YG_NO,NULL,TRS_DD FROM TF_SKTZ
SELECT * INTO #T1 FROM TF_TZ
INSERT INTO #T1 SELECT A.YG_NO,NULL,A.TRS_DD,NULL,'Y' FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A WHERE NOT EXISTS(SELECT 1 FROM TF_TZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD) AND NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD) SELECT b.YG_NO, b.SZ_NO, b.TRS_DD, SB_Y = CONVERT(CHAR(5),b.SB_Y,108), SC_C = CONVERT(CHAR(5),b.SC_C,108), SC_Y = CONVERT(CHAR(5),b.SC_Y,108), SB_C = CONVERT(CHAR(5),b.SB_C,108), SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108), SJ_C = CONVERT(CHAR(5),b.SJ_C,108), JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0), KJ = CASE WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0 THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0)) ELSE 0 END, GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0), b.QTY FROM (SELECT a.YG_NO, SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710), a.TRS_DD, SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ), SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ), SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ), SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110), SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171), SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710), a.QTY FROM (SELECT YG_NO,SZ_NO,TRS_DD,QTY,CH_ID FROM #T1--TF_TZ UNION SELECT DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL FROM TF_KQ t WHERE NOT EXISTS(SELECT 1 FROM TF_TZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a WHERE (a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01')) AND (a.YG_NO BETWEEN @SYG_NO AND @EYG_NO) AND EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
(580958 row(s) affected)Server: Msg 213, Level 16, State 4, Procedure SP_GetDetail, Line 10 Insert Error: Column name or number of supplied values does not match table definition.上面的通不过,执行出现上面的
把改成: CREATE PROCEDURE SP_GetDetail @SDATE DATETIME, --日期范围起始日期 @EDATE DATETIME, --日期范围截止日期 @SYG_NO VARCHAR(20),--工号范围起始工号 @EYG_NO VARCHAR(20) --工号范围截止工号 AS BEGIN SELECT YG_NO,BAN_NO,TRS_DD INTO #T FROM TF_KQ
INSERT INTO #T SELECT YG_NO,NULL,TRS_DD FROM TF_SKTZ
SELECT YG_NO,NULL,TRS_DD,CHK_ID INTO #T1 FROM TF_JBTZ
INSERT INTO #T1 SELECT A.YG_NO,NULL,A.TRS_DD,NULL,'Y' FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A WHERE NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD) AND NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD) SELECT b.YG_NO, Name = (select Name from MF_YG where YG_NO=b.YG_NO), b.SZ_NO, b.TRS_DD, SB_Y = CONVERT(CHAR(5),b.SB_Y,108), SC_C = CONVERT(CHAR(5),b.SC_C,108), SC_Y = CONVERT(CHAR(5),b.SC_Y,108), SB_C = CONVERT(CHAR(5),b.SB_C,108), SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108), SJ_C = CONVERT(CHAR(5),b.SJ_C,108), JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0), KJ = CASE WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0 THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0)) ELSE 0 END, GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0), b.QTY, b.CHK_ID FROM (SELECT a.YG_NO, SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710), a.TRS_DD, SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ), SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ), SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ), SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110), SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171), SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710), a.QTY, b.CHK_ID FROM (SELECT YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID FROM #T1--TF_JBTZ UNION SELECT DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL FROM TF_KQ t WHERE NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a WHERE (a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01')) AND (a.YG_NO BETWEEN @SYG_NO AND @EYG_NO) AND EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
END GO但是上面的执行后出现: Server: Msg 8155, Level 16, State 1, Procedure SP_GetDetail, Line 12 No column was specified for column 2 of '#T1'.
SELECT YG_NO,NULL,TRS_DD,CHK_ID INTO #T1 FROM TF_JBTZ
INSERT INTO #T1 SELECT A.YG_NO,NULL,A.TRS_DD,NULL,'Y' ...两个SQL向 TF_JBTZ插入的列数不同,把后一个查询改为 INSERT INTO #T1 SELECT A.YG_NO,NULL,A.TRS_DD,'Y' ...
SELECT YG_NO ,NULL,TRS_DD,CHK_ID INTO #T1 FROM TF_JBTZ僅執行上面的一句也執行不了, 好象這樣不能用"NULL"吧謝 謝!
为支持赋值的列指定一个列名:SELECT YG_NO ,NULL as BAN_NO,TRS_DD,CHK_ID INTO #T1 FROM TF_JBTZ
都把这个存储过程的字段规定死了,但是 还是出现(580958 row(s) affected) (385 row(s) affected) (14712 row(s) affected)Server: Msg 213, Level 16, State 5, Procedure SP_GetDetail, Line 14 Insert Error: Column name or number of supplied values does not match table definition. Server: Msg 107, Level 16, State 1, Procedure SP_GetDetail, Line 24 The column prefix 'b' does not match with a table name or alias name used in the query.不知道是怎么回事了
现在还有这个问题: Server: Msg 107, Level 16, State 2, Procedure SP_GetDetail, Line 24 The column prefix 'b' does not match with a table name or alias name used in the query.
楼主修改我的SQL语句了吧: SELECT b.YG_NO, Name = (select Name from MF_YG where YG_NO=b.YG_NO), b.SZ_NO, b.TRS_DD, SB_Y = CONVERT(CHAR(5),b.SB_Y,108), SC_C = CONVERT(CHAR(5),b.SC_C,108), SC_Y = CONVERT(CHAR(5),b.SC_Y,108), SB_C = CONVERT(CHAR(5),b.SB_C,108), SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108), SJ_C = CONVERT(CHAR(5),b.SJ_C,108), JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0), KJ = CASE WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0 THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0)) ELSE 0 END, GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0), b.QTY, b.CHK_ID FROM (SELECT a.YG_NO, SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710), a.TRS_DD, SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ), SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ), SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ), SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110), SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171), SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710), a.QTY, b.CHK_ID --!!!!这里用a.CHK_ID!!!! FROM (SELECT YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID FROM #T1--TF_JBTZ UNION SELECT DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL FROM TF_KQ t WHERE NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a WHERE (a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01')) AND (a.YG_NO BETWEEN @SYG_NO AND @EYG_NO) AND EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
SELECT YG_NO,BAN_NO,TRS_DD INTO #T FROM TF_KQ INSERT INTO #T SELECT YG_NO,NULL as BAN_NO,TRS_DD FROM TF_SKTZ
SELECT YG_NO,NULL as BAN_NO,TRS_DD,CHK_ID INTO #T1 FROM TF_JBTZ
INSERT INTO #T1 SELECT A.YG_NO,NULL as BAN_NO,A.TRS_DD,'Y' FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A WHERE NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD) AND NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD) SELECT b.YG_NO, Name = (select Name from MF_YG where YG_NO=b.YG_NO), b.SZ_NO, b.TRS_DD, SB_Y = CONVERT(CHAR(5),b.SB_Y,108), SC_C = CONVERT(CHAR(5),b.SC_C,108), SC_Y = CONVERT(CHAR(5),b.SC_Y,108), SB_C = CONVERT(CHAR(5),b.SB_C,108), SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108), SJ_C = CONVERT(CHAR(5),b.SJ_C,108), JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0), KJ = CASE WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0 THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0)) ELSE 0 END, GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0), b.QTY, b.CHK_ID FROM (SELECT a.YG_NO, SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710), a.TRS_DD, SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ), SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ), SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ), SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110), SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171), SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710), a.QTY, a.CHK_ID FROM (SELECT YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID FROM #T1--TF_JBTZ UNION SELECT DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL FROM TF_KQ t WHERE NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a WHERE
EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b 执行上面的语句还是出现:Server: Msg 207, Level 16, State 3, Line 17 Invalid column name 'SZ_NO'. Server: Msg 207, Level 16, State 1, Line 17 Invalid column name 'QTY'.这里是哪里不对应的关系?
FROM (SELECT YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID --!!#T1里压根就没有SZ_NO和QTY这两个字段 FROM #T1--TF_JBTZ -------------------------------------------------------------------------------- 你修改我给你的代码时不够仔细,应该按照我原来的SQL组织#T1表,把TF_JBTZ的几个字段都插入到#T1表中,这个语句就不会错了。
补卡表(tf_sktz)YG_NO 员工代号 varchar(20) TRS_DD 补卡时间 datetime CHK_ID 审核否()varchar(1)--->为Y是代表审核了,当为N代表没有审核;YG_NO TRS_DD CHK_ID A0022005-07-08 14:00:00.000 Y A0022005-07-08 18:00:00.000 Y A0022005-07-08 19:00:00.000 Y A0022005-07-09 08:00:00.000 Y A0022005-07-09 12:00:00.000 Y A0022005-07-09 14:00:00.000 Y A0022005-07-09 18:00:00.000 Y A0022005-07-09 19:00:00.000 Y A0022005-07-10 00:00:00.000 Y A0022005-07-10 08:00:00.000 Y A0022005-07-10 12:00:00.000 Y A0022005-07-10 14:00:00.000 Y A0022005-07-10 18:00:00.000 Y A0022005-07-10 19:00:00.000 Y A0022005-07-11 08:00:00.000 Y A0022005-07-27 12:00:00.000 Y A0022005-07-27 14:00:00.000 Y A0022005-08-09 12:00:00.000 Y A0022005-08-09 14:00:00.000 Y 一个数据库的每天考勤表(TF_KQ)的內容如下: YG_NO varchar(20)->为工号; BAN_NO varchar(4)->班别; TRS_DD datetime ->为打卡日期; 下为这表的例子以一个工号A002 打卡为2004-10-08天 的数据为准; YG_NO BAN_NO TRS_DD ------------------------------------------------------------------ A002 002 2004-10-08 07:23:00.000 A002 002 2004-10-08 07:22:00.000A002 002 2004-10-08 11:31:00.000 A002 002 2004-10-08 12:04:00.000 A002 002 2004-10-08 12:29:00.000A002 002 2004-10-08 12:45:00.000 A002 002 2004-10-08 12:51:00.000 A002 002 2004-10-08 13:24:00.000A002 002 2004-10-08 17:33:00.000 A002 002 2004-10-08 17:45:00.000A002 002 2004-10-08 19:22:00.000 A002 002 2004-10-08 19:25:00.000A002 002 2004-10-08 20:22:00.000 A002 002 2004-10-08 02:25:00.000 A003 ... ...................... A003 ... ...................... ... 又一个数据库每天对每一员工有一实登基数的表(TF_TZ)的內容如下: YG_NO varchar(20)->为工号;SZ_NO varchar(4)->班别;TRS_DD datetime ->为打卡日期; QTY numeric(28,8) 基数; CH_ID varchar(1)->审核否 YG_NO SZ_NO TRS_DD QTY CH_ID ------------------------------------------------------------------ ... ... .............. .... .. A0020022005-08-07 00:00:00.000 4.00000000N A0020022005-08-08 00:00:00.000 2.00000000Y A0020022005-08-09 00:00:00.000 3.00000000N A0020022005-08-10 00:00:00.000 7.00000000N ... ... .............. .... ..上面的TF_TZ的表也就是TF_JBTZ 表,我后来把表晚给换了一下FROM (SELECT YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID --!!#T1里压根就没有SZ_NO和QTY这两个字段 FROM #T1--TF_JBTZ 这是有的吧,改一下表明应没有问题吧
注意生成临时表#T1的SQL语句:SELECT YG_NO,NULL as BAN_NO,TRS_DD,CHK_ID INTO #T1 FROM TF_JBTZ上面这个SQL语句中列出的字段就是生成后的临时表#T1的字段,即:YG_NO,BAN_NO,TRS_DD,CHK_ID。
这个是明白,但是我在TF_JBTZ表里以 SELECT YG_NO,NULL as SZ_NO,TRS_DD,QTY,CHK_ID INTO #T1 FROM TF_JBTZ还是不行,一样的结果,就是不明白这里会有什么差异 FROM (SELECT YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID FROM #T1--TF_JBTZ 与这里;
INSERT INTO #T SELECT YG_NO,NULL as BAN_NO,TRS_DD FROM TF_SKTZ
SELECT YG_NO,NULL as SZ_NO,TRS_DD,QTY,CHK_ID INTO #T1 FROM TF_JBTZ
INSERT INTO #T1 SELECT A.YG_NO,NULL as BAN_NO,A.TRS_DD,NULL,'Y' FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A WHERE NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD) AND NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD) SELECT b.YG_NO, Name = (select Name from MF_YG where YG_NO=b.YG_NO), b.SZ_NO, b.TRS_DD, SB_Y = CONVERT(CHAR(5),b.SB_Y,108), SC_C = CONVERT(CHAR(5),b.SC_C,108), SC_Y = CONVERT(CHAR(5),b.SC_Y,108), SB_C = CONVERT(CHAR(5),b.SB_C,108), SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108), SJ_C = CONVERT(CHAR(5),b.SJ_C,108), JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0), KJ = CASE WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0 THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0)) ELSE 0 END, GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0), b.QTY, b.CHK_ID FROM (SELECT a.YG_NO, SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710), a.TRS_DD, SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ), SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ), SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ), SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110), SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171), SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710), a.QTY, a.CHK_ID FROM (SELECT YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID FROM #T1--TF_JBTZ UNION SELECT DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL FROM TF_KQ t WHERE NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a WHERE (a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01')) AND (a.YG_NO BETWEEN @SYG_NO AND @EYG_NO) AND EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
楼上的SQL语句我没修改,去掉了参数引用部分,以下的SQL是可以执行的: ----------------------------------------------------------------------- create table TF_KQ(YG_NO varchar(20),BAN_NO varchar(20),TRS_DD datetime) insert into TF_KQ select 'A002','002','2005-10-08 07:23:00.000' insert into TF_KQ select 'A002','002','2005-10-08 07:22:00.000' insert into TF_KQ select 'A002','002','2005-10-08 11:31:00.000' insert into TF_KQ select 'A002','002','2005-10-08 12:04:00.000' insert into TF_KQ select 'A002','002','2005-10-08 12:29:00.000' insert into TF_KQ select 'A002','002','2005-10-08 12:45:00.000' insert into TF_KQ select 'A002','002','2005-10-08 12:51:00.000' insert into TF_KQ select 'A002','002','2005-10-08 13:24:00.000' insert into TF_KQ select 'A002','002','2005-10-08 17:33:00.000' insert into TF_KQ select 'A002','002','2005-10-08 17:45:00.000' insert into TF_KQ select 'A002','002','2005-10-08 19:22:00.000' insert into TF_KQ select 'A002','002','2005-10-08 19:25:00.000' insert into TF_KQ select 'A002','002','2005-10-08 20:22:00.000' insert into TF_KQ select 'A002','002','2005-10-08 02:25:00.000'create table TF_JBTZ(YG_NO varchar(20),SZ_NO varchar(4),TRS_DD datetime,QTY numeric(28,8),CHK_ID varchar(1)) insert into TF_JBTZ select 'A002','002','2005-10-07 00:00:00.000',4.00000000,'N' insert into TF_JBTZ select 'A002','002','2005-10-08 00:00:00.000',2.00000000,'Y' insert into TF_JBTZ select 'A002','002','2005-10-09 00:00:00.000',3.00000000,'N' insert into TF_JBTZ select 'A002','002','2005-10-10 00:00:00.000',7.00000000,'N'create table TF_SKTZ(YG_NO varchar(20),TRS_DD datetime,CHK_ID varchar(1)) insert into TF_SKTZ select 'A002','2005-07-08 14:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-08 18:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-08 19:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 08:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 12:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 14:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 18:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-09 19:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 00:00:00.000','Y' insert into TF_SKTZ select 'A002','2005-07-10 08:00:00.000','Y'SELECT YG_NO,BAN_NO,TRS_DD INTO #T FROM TF_KQINSERT INTO #T SELECT YG_NO,NULL as BAN_NO,TRS_DD FROM TF_SKTZSELECT YG_NO,NULL as SZ_NO,TRS_DD,QTY,CHK_ID INTO #T1 FROM TF_JBTZINSERT INTO #T1 SELECT A.YG_NO,NULL as BAN_NO,A.TRS_DD,NULL,'Y' FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A WHERE NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD) AND NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD) SELECT b.YG_NO, b.SZ_NO, b.TRS_DD, SB_Y = CONVERT(CHAR(5),b.SB_Y,108), SC_C = CONVERT(CHAR(5),b.SC_C,108), SC_Y = CONVERT(CHAR(5),b.SC_Y,108), SB_C = CONVERT(CHAR(5),b.SB_C,108), SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108), SJ_C = CONVERT(CHAR(5),b.SJ_C,108), JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0), KJ = CASE WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0 THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0)) ELSE 0 END, GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0), b.QTY, b.CHK_ID FROM (SELECT a.YG_NO, SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710), a.TRS_DD, SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ), SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ), SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ), SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110), SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171), SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710), a.QTY, a.CHK_ID FROM (SELECT YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID FROM #T1--TF_JBTZ UNION SELECT DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL FROM TF_KQ t WHERE NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a WHERE EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
ALTER PROCEDURE SP_GetDetail @SDATE DATETIME, --日期范围起始日期 @EDATE DATETIME, --日期范围截止日期 @SYG_NO VARCHAR(20),--工号范围起始工号 @EYG_NO VARCHAR(20) --工号范围截止工号 AS BEGINSELECT YG_NO,BAN_NO,TRS_DD INTO #T FROM TF_KQINSERT INTO #T SELECT YG_NO,NULL as BAN_NO,TRS_DD FROM TF_SKTZSELECT YG_NO,NULL as SZ_NO,TRS_DD,QTY,CHK_ID INTO #T1 FROM TF_JBTZINSERT INTO #T1 SELECT A.YG_NO,NULL as BAN_NO,A.TRS_DD,NULL,'Y' FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A WHERE NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD) AND NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD) SELECT b.YG_NO, b.SZ_NO, b.TRS_DD, SB_Y = CONVERT(CHAR(5),b.SB_Y,108), SC_C = CONVERT(CHAR(5),b.SC_C,108), SC_Y = CONVERT(CHAR(5),b.SC_Y,108), SB_C = CONVERT(CHAR(5),b.SB_C,108), SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108), SJ_C = CONVERT(CHAR(5),b.SJ_C,108), JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0), KJ = CASE WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0 THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0)) ELSE 0 END, GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0), b.QTY, b.CHK_ID FROM (SELECT a.YG_NO, SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710), a.TRS_DD, SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ), SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ), SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ), SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110), SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171), SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710), a.QTY, a.CHK_ID FROM (SELECT YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID FROM #T1--TF_JBTZ UNION SELECT DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL FROM TF_KQ t WHERE NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a WHERE (a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01')) AND (a.YG_NO BETWEEN @SYG_NO AND @EYG_NO) AND EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b--EXEC SP_GetDetail '2005-08-01','2005-09-31','17002114','17002114' END GO 把变量加进去,执行EXEC SP_GetDetail '2005-08-01','2005-09-31','17002114','17002114'出现: Server: Msg 8114, Level 16, State 4, Procedure SP_GetDetail, Line 0 Error converting data type varchar to datetime.
create table TF_KQ(YG_NO varchar(10),BAN_NO varchar(10),TRS_DD datetime)
insert into TF_KQ select 'A002','002','2004-10-08 07:23:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 07:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 11:31:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:04:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:29:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:51:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 13:24:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 17:33:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 17:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 19:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 19:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 20:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 02:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 07:23:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 07:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 11:31:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:04:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:29:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:51:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 13:24:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 17:33:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 17:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 19:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 19:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 20:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 02:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 07:23:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 07:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 11:31:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:04:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:29:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:51:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 13:24:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 17:33:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 17:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 19:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 19:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 20:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-11 02:25:00.000'
create table TF_SKTZ(YG_NO varchar(20),TRS_DD datetime,CHK_ID char(1))
insert into TF_SKTZ select 'A002','2005-07-08 14:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-08 18:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-08 19:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 08:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 12:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 14:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 18:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 19:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 00:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 08:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 12:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 14:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 18:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 19:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-11 08:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-27 12:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-27 14:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-08-09 12:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-08-09 14:00:00.000','Y'
create table TF_TZ(YG_NO varchar(20),SZ_NO varchar(4),TRS_DD datetime,QTY numeric(28,8),CH_ID varchar(1))
insert into TF_TZ select 'A002','00D','2004-10-08 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2004-10-09 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2005-07-07 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2005-07-08 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2005-07-09 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2005-07-11 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2005-07-27 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2005-08-09 00:00:00.000',8.00000000,'Y'
GO--创建存储过程
CREATE PROCEDURE SP_GetDetail
@SDATE DATETIME, --日期范围起始日期
@EDATE DATETIME, --日期范围截止日期
@SYG_NO VARCHAR(20),--工号范围起始工号
@EYG_NO VARCHAR(20) --工号范围截止工号
AS
BEGIN
SELECT * INTO #T from TF_KQ
INSERT INTO #T SELECT YG_NO,NULL,TRS_DD FROM TF_SKTZ
SELECT
b.YG_NO,
b.SZ_NO,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
SC_Y = CONVERT(CHAR(5),b.SC_Y,108),
SB_C = CONVERT(CHAR(5),b.SB_C,108),
SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108),
SJ_C = CONVERT(CHAR(5),b.SJ_C,108),
JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0),
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),
b.QTY
FROM
(SELECT
a.YG_NO,
SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710),
a.TRS_DD,
SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ),
SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ),
SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ),
SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110),
SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171),
SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710),
a.QTY
FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CH_ID
FROM
TF_TZ
UNION
SELECT
DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL
FROM
TF_KQ t
WHERE
NOT EXISTS(SELECT 1 FROM TF_TZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a
WHERE
(a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01'))
AND
(a.YG_NO BETWEEN @SYG_NO AND @EYG_NO)
AND
EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
END
GO
--执行存储过程
EXEC SP_GetDetail '2004-01-01','2006-01-01','A002','A008'
GO
/*
YG_NO SZ_NO TRS_DD SB_Y SC_C SC_Y SB_C SJ_Y SJ_C JBS KJ GS QTY
----- ----- ---------- ----- ----- ----- ----- ----- ----- --- -- -- ----------
A002 002 2004-10-10 07:23 12:29 13:24 17:45 19:25 02:25 7 0 9 NULL
A002 002 2004-10-08 07:23 12:29 13:24 17:45 19:25 02:25 7 0 9 8.00000000
A002 002 2004-10-09 07:23 12:29 13:24 17:45 19:25 02:25 7 0 9 8.00000000
A002 NULL 2005-07-08 NULL NULL 14:00 18:00 19:00 NULL 0 5 4 8.00000000
A002 NULL 2005-07-09 NULL 12:00 14:00 18:00 19:00 00:00 5 5 4 8.00000000
A002 NULL 2005-07-11 NULL NULL NULL NULL NULL NULL 0 9 0 8.00000000
A002 NULL 2005-07-27 NULL 12:00 14:00 NULL NULL NULL 0 9 0 8.00000000
A002 NULL 2005-08-09 NULL 12:00 14:00 NULL NULL NULL 0 9 0 8.00000000
*/
谢谢!
create table TF_KQ(YG_NO varchar(10),BAN_NO varchar(10),TRS_DD datetime)
insert into TF_KQ select 'A002','002','2004-10-08 07:23:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 07:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 11:31:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:04:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:29:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:51:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 13:24:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 17:33:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 17:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 19:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 19:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 20:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 02:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 07:23:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 07:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 11:31:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:04:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:29:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:51:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 13:24:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 17:33:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 17:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 19:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 19:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 20:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 02:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 07:23:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 07:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 11:31:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:04:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:29:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:51:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 13:24:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 17:33:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 17:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 19:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 19:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 20:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-11 02:25:00.000'
create table TF_SKTZ(YG_NO varchar(20),TRS_DD datetime,CHK_ID char(1))
insert into TF_SKTZ select 'A002','2005-07-08 14:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-08 18:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-08 19:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 08:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 12:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 14:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 18:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 19:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 00:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 08:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 12:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 14:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 18:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 19:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-11 08:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-27 12:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-27 14:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-08-09 12:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-08-09 14:00:00.000','Y'
create table TF_TZ(YG_NO varchar(20),SZ_NO varchar(4),TRS_DD datetime,QTY numeric(28,8),CH_ID varchar(1))
insert into TF_TZ select 'A002','00D','2004-10-08 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2004-10-09 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2005-07-07 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2005-07-08 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2005-07-09 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2005-07-11 00:00:00.000',8.00000000,'Y'
GO
CREATE PROCEDURE SP_GetDetail
@SDATE DATETIME, --日期范围起始日期
@EDATE DATETIME, --日期范围截止日期
@SYG_NO VARCHAR(20),--工号范围起始工号
@EYG_NO VARCHAR(20) --工号范围截止工号
AS
BEGIN
SELECT * INTO #T FROM TF_KQ
INSERT INTO #T SELECT YG_NO,NULL,TRS_DD FROM TF_SKTZ
SELECT * INTO #T1 FROM TF_TZ
INSERT INTO #T1
SELECT
A.YG_NO,NULL,A.TRS_DD,NULL,'Y'
FROM
(SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A
WHERE
NOT EXISTS(SELECT 1 FROM TF_TZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD)
AND
NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD) SELECT
b.YG_NO,
b.SZ_NO,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
SC_Y = CONVERT(CHAR(5),b.SC_Y,108),
SB_C = CONVERT(CHAR(5),b.SB_C,108),
SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108),
SJ_C = CONVERT(CHAR(5),b.SJ_C,108),
JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0),
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),
b.QTY
FROM
(SELECT
a.YG_NO,
SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710),
a.TRS_DD,
SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ),
SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ),
SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ),
SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110),
SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171),
SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710),
a.QTY
FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CH_ID
FROM
#T1--TF_TZ
UNION
SELECT
DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL
FROM
TF_KQ t
WHERE
NOT EXISTS(SELECT 1 FROM TF_TZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a
WHERE
(a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01'))
AND
(a.YG_NO BETWEEN @SYG_NO AND @EYG_NO)
AND
EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
END
GO
--执行存储过程
EXEC SP_GetDetail '2004-01-01','2006-01-01','A002','A008'
GO/*
YG_NO SZ_NO TRS_DD SB_Y SC_C SC_Y SB_C SJ_Y SJ_C JBS KJ GS QTY
----- ----- ---------- ----- ----- ----- ----- ----- ----- --- -- -- ----------
A002 002 2004-10-08 07:23 12:29 13:24 17:45 19:25 02:25 7 0 9 8.00000000
A002 002 2004-10-09 07:23 12:29 13:24 17:45 19:25 02:25 7 0 9 8.00000000
A002 002 2004-10-10 07:23 12:29 13:24 17:45 19:25 02:25 7 0 9 NULL
A002 NULL 2005-07-08 NULL NULL 14:00 18:00 19:00 NULL 0 5 4 8.00000000
A002 NULL 2005-07-09 NULL 12:00 14:00 18:00 19:00 00:00 5 5 4 8.00000000
A002 NULL 2005-07-10 NULL 12:00 14:00 18:00 19:00 NULL 0 5 4 NULL
A002 NULL 2005-07-11 NULL NULL NULL NULL NULL NULL 0 9 0 8.00000000
A002 NULL 2005-07-27 NULL 12:00 14:00 NULL NULL NULL 0 9 0 NULL
A002 NULL 2005-08-09 NULL 12:00 14:00 NULL NULL NULL 0 9 0 NULL
*/
(580958 row(s) affected)Server: Msg 213, Level 16, State 4, Procedure SP_GetDetail, Line 10
Insert Error: Column name or number of supplied values does not match table definition.上面的通不过,执行出现上面的
CREATE PROCEDURE SP_GetDetail
@SDATE DATETIME, --日期范围起始日期
@EDATE DATETIME, --日期范围截止日期
@SYG_NO VARCHAR(20),--工号范围起始工号
@EYG_NO VARCHAR(20) --工号范围截止工号
AS
BEGIN
SELECT YG_NO,BAN_NO,TRS_DD INTO #T FROM TF_KQ
INSERT INTO #T SELECT YG_NO,NULL,TRS_DD FROM TF_SKTZ
SELECT YG_NO,NULL,TRS_DD,CHK_ID INTO #T1 FROM TF_JBTZ
INSERT INTO #T1
SELECT
A.YG_NO,NULL,A.TRS_DD,NULL,'Y'
FROM
(SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A
WHERE
NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD)
AND
NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD) SELECT
b.YG_NO,
Name = (select Name from MF_YG where YG_NO=b.YG_NO),
b.SZ_NO,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
SC_Y = CONVERT(CHAR(5),b.SC_Y,108),
SB_C = CONVERT(CHAR(5),b.SB_C,108),
SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108),
SJ_C = CONVERT(CHAR(5),b.SJ_C,108),
JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0),
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),
b.QTY,
b.CHK_ID
FROM
(SELECT
a.YG_NO,
SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710),
a.TRS_DD,
SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ),
SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ),
SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ),
SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110),
SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171),
SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710),
a.QTY,
b.CHK_ID
FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID
FROM
#T1--TF_JBTZ
UNION
SELECT
DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL
FROM
TF_KQ t
WHERE
NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a
WHERE
(a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01'))
AND
(a.YG_NO BETWEEN @SYG_NO AND @EYG_NO)
AND
EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
END
GO但是上面的执行后出现:
Server: Msg 8155, Level 16, State 1, Procedure SP_GetDetail, Line 12
No column was specified for column 2 of '#T1'.
INSERT INTO #T1 SELECT A.YG_NO,NULL,A.TRS_DD,NULL,'Y' ...两个SQL向 TF_JBTZ插入的列数不同,把后一个查询改为
INSERT INTO #T1 SELECT A.YG_NO,NULL,A.TRS_DD,'Y' ...
还是出现(580958 row(s) affected)
(385 row(s) affected)
(14712 row(s) affected)Server: Msg 213, Level 16, State 5, Procedure SP_GetDetail, Line 14
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 107, Level 16, State 1, Procedure SP_GetDetail, Line 24
The column prefix 'b' does not match with a table name or alias name used in the query.不知道是怎么回事了
Server: Msg 107, Level 16, State 2, Procedure SP_GetDetail, Line 24
The column prefix 'b' does not match with a table name or alias name used in the query.
b.YG_NO,
Name = (select Name from MF_YG where YG_NO=b.YG_NO),
b.SZ_NO,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
SC_Y = CONVERT(CHAR(5),b.SC_Y,108),
SB_C = CONVERT(CHAR(5),b.SB_C,108),
SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108),
SJ_C = CONVERT(CHAR(5),b.SJ_C,108),
JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0),
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),
b.QTY,
b.CHK_ID
FROM
(SELECT
a.YG_NO,
SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710),
a.TRS_DD,
SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ),
SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ),
SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ),
SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110),
SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171),
SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710),
a.QTY,
b.CHK_ID --!!!!这里用a.CHK_ID!!!!
FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID
FROM
#T1--TF_JBTZ
UNION
SELECT
DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL
FROM
TF_KQ t
WHERE
NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a
WHERE
(a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01'))
AND
(a.YG_NO BETWEEN @SYG_NO AND @EYG_NO)
AND
EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
END
GO
INSERT INTO #T SELECT YG_NO,NULL as BAN_NO,TRS_DD FROM TF_SKTZ
SELECT YG_NO,NULL as BAN_NO,TRS_DD,CHK_ID INTO #T1 FROM TF_JBTZ
INSERT INTO #T1
SELECT
A.YG_NO,NULL as BAN_NO,A.TRS_DD,'Y'
FROM
(SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A
WHERE
NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD)
AND
NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD)
SELECT
b.YG_NO,
Name = (select Name from MF_YG where YG_NO=b.YG_NO),
b.SZ_NO,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
SC_Y = CONVERT(CHAR(5),b.SC_Y,108),
SB_C = CONVERT(CHAR(5),b.SB_C,108),
SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108),
SJ_C = CONVERT(CHAR(5),b.SJ_C,108),
JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0),
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),
b.QTY,
b.CHK_ID
FROM
(SELECT
a.YG_NO,
SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710),
a.TRS_DD,
SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ),
SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ),
SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ),
SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110),
SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171),
SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710),
a.QTY,
a.CHK_ID
FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID
FROM
#T1--TF_JBTZ
UNION
SELECT
DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL
FROM
TF_KQ t
WHERE
NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a
WHERE
EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
执行上面的语句还是出现:Server: Msg 207, Level 16, State 3, Line 17
Invalid column name 'SZ_NO'.
Server: Msg 207, Level 16, State 1, Line 17
Invalid column name 'QTY'.这里是哪里不对应的关系?
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID --!!#T1里压根就没有SZ_NO和QTY这两个字段
FROM
#T1--TF_JBTZ
--------------------------------------------------------------------------------
你修改我给你的代码时不够仔细,应该按照我原来的SQL组织#T1表,把TF_JBTZ的几个字段都插入到#T1表中,这个语句就不会错了。
TRS_DD 补卡时间 datetime
CHK_ID 审核否()varchar(1)--->为Y是代表审核了,当为N代表没有审核;YG_NO TRS_DD CHK_ID
A0022005-07-08 14:00:00.000 Y
A0022005-07-08 18:00:00.000 Y
A0022005-07-08 19:00:00.000 Y
A0022005-07-09 08:00:00.000 Y
A0022005-07-09 12:00:00.000 Y
A0022005-07-09 14:00:00.000 Y
A0022005-07-09 18:00:00.000 Y
A0022005-07-09 19:00:00.000 Y
A0022005-07-10 00:00:00.000 Y
A0022005-07-10 08:00:00.000 Y
A0022005-07-10 12:00:00.000 Y
A0022005-07-10 14:00:00.000 Y
A0022005-07-10 18:00:00.000 Y
A0022005-07-10 19:00:00.000 Y
A0022005-07-11 08:00:00.000 Y
A0022005-07-27 12:00:00.000 Y
A0022005-07-27 14:00:00.000 Y
A0022005-08-09 12:00:00.000 Y
A0022005-08-09 14:00:00.000 Y
一个数据库的每天考勤表(TF_KQ)的內容如下:
YG_NO varchar(20)->为工号; BAN_NO varchar(4)->班别; TRS_DD datetime ->为打卡日期;
下为这表的例子以一个工号A002 打卡为2004-10-08天 的数据为准;
YG_NO BAN_NO TRS_DD
------------------------------------------------------------------
A002 002 2004-10-08 07:23:00.000
A002 002 2004-10-08 07:22:00.000A002 002 2004-10-08 11:31:00.000
A002 002 2004-10-08 12:04:00.000
A002 002 2004-10-08 12:29:00.000A002 002 2004-10-08 12:45:00.000
A002 002 2004-10-08 12:51:00.000
A002 002 2004-10-08 13:24:00.000A002 002 2004-10-08 17:33:00.000
A002 002 2004-10-08 17:45:00.000A002 002 2004-10-08 19:22:00.000
A002 002 2004-10-08 19:25:00.000A002 002 2004-10-08 20:22:00.000
A002 002 2004-10-08 02:25:00.000
A003 ... ......................
A003 ... ......................
...
又一个数据库每天对每一员工有一实登基数的表(TF_TZ)的內容如下:
YG_NO varchar(20)->为工号;SZ_NO varchar(4)->班别;TRS_DD datetime ->为打卡日期;
QTY numeric(28,8) 基数; CH_ID varchar(1)->审核否
YG_NO SZ_NO TRS_DD QTY CH_ID
------------------------------------------------------------------
... ... .............. .... ..
A0020022005-08-07 00:00:00.000 4.00000000N
A0020022005-08-08 00:00:00.000 2.00000000Y
A0020022005-08-09 00:00:00.000 3.00000000N
A0020022005-08-10 00:00:00.000 7.00000000N
... ... .............. .... ..上面的TF_TZ的表也就是TF_JBTZ 表,我后来把表晚给换了一下FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID --!!#T1里压根就没有SZ_NO和QTY这两个字段
FROM
#T1--TF_JBTZ
这是有的吧,改一下表明应没有问题吧
SELECT YG_NO,NULL as SZ_NO,TRS_DD,QTY,CHK_ID INTO #T1 FROM TF_JBTZ还是不行,一样的结果,就是不明白这里会有什么差异 FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID
FROM
#T1--TF_JBTZ 与这里;
YG_NO varchar(20)->为工号;SZ_NO varchar(4)->班别;TRS_DD datetime ->为打卡日期;
QTY numeric(28,8) 基数; CHK_ID varchar(1)->审核否
YG_NO SZ_NO TRS_DD QTY CHK_ID
------------------------------------------------------------------
... ... .............. .... ..
A0020022005-08-07 00:00:00.000 4.00000000N
A0020022005-08-08 00:00:00.000 2.00000000Y
A0020022005-08-09 00:00:00.000 3.00000000N
A0020022005-08-10 00:00:00.000 7.00000000N
... ... .............. .... ..
INSERT INTO #T SELECT YG_NO,NULL as BAN_NO,TRS_DD FROM TF_SKTZ
SELECT YG_NO,NULL as SZ_NO,TRS_DD,QTY,CHK_ID INTO #T1 FROM TF_JBTZ
INSERT INTO #T1
SELECT
A.YG_NO,NULL as BAN_NO,A.TRS_DD,NULL,'Y'
FROM
(SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A
WHERE
NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD)
AND
NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD)
SELECT
b.YG_NO,
Name = (select Name from MF_YG where YG_NO=b.YG_NO),
b.SZ_NO,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
SC_Y = CONVERT(CHAR(5),b.SC_Y,108),
SB_C = CONVERT(CHAR(5),b.SB_C,108),
SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108),
SJ_C = CONVERT(CHAR(5),b.SJ_C,108),
JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0),
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),
b.QTY,
b.CHK_ID
FROM
(SELECT
a.YG_NO,
SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710),
a.TRS_DD,
SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ),
SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ),
SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ),
SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110),
SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171),
SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710),
a.QTY,
a.CHK_ID
FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID
FROM
#T1--TF_JBTZ
UNION
SELECT
DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL
FROM
TF_KQ t
WHERE
NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a
WHERE
(a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01'))
AND
(a.YG_NO BETWEEN @SYG_NO AND @EYG_NO)
AND
EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
一个数据库的每天考勤表(TF_KQ)的內容如下:
YG_NO varchar(20)->为工号; BAN_NO varchar(4)->班别; TRS_DD datetime ->为打卡日期;
下为这表的例子以一个工号A002 打卡为2004-10-08天 的数据为准;
YG_NO BAN_NO TRS_DD
------------------------------------------------------------------
A002 002 2005-10-08 07:23:00.000
A002 002 2005-10-08 07:22:00.000A002 002 2005-10-08 11:31:00.000
A002 002 2005-10-08 12:04:00.000
A002 002 2005-10-08 12:29:00.000A002 002 2005-10-08 12:45:00.000
A002 002 2005-10-08 12:51:00.000
A002 002 2005-10-08 13:24:00.000A002 002 2005-10-08 17:33:00.000
A002 002 2005-10-08 17:45:00.000A002 002 2005-10-08 19:22:00.000
A002 002 2005-10-08 19:25:00.000A002 002 2005-10-08 20:22:00.000
A002 002 2005-10-08 02:25:00.000
A003 ... ......................
A003 ... ......................
...
又一个数据库每天对每一员工有一实登基数的表(TF_JBTZ)的內容如下:
YG_NO varchar(20)->为工号;SZ_NO varchar(4)->班别;TRS_DD datetime ->为打卡日期;
QTY numeric(28,8) 基数; CHK_ID varchar(1)->审核否
YG_NO SZ_NO TRS_DD QTY CHK_ID
------------------------------------------------------------------
... ... .............. .... ..
A0020022005-10-07 00:00:00.000 4.00000000N
A0020022005-10-08 00:00:00.000 2.00000000Y
A0020022005-10-09 00:00:00.000 3.00000000N
A0020022005-10-10 00:00:00.000 7.00000000N
... ... .............. .... ..上面的两个表通过 (YG_NO,TRS_DD)来关联;
对考勤表(TF_KQ)条件是:
1,上午上班刷卡有效时间为:早上7:00刷卡,刷卡前后30分钟有效,如果同时这时间内有刷两次以上以最后一次为准;
2、中午下班刷卡时间为12:00,刷卡前后30分钟有效,如果同时这时间内有刷两次以上以最后一次为准;
3、下午上班刷卡时间为13:30,刷卡前后30分钟有效,如果同时这时间内有刷两次以上以最后一次为准;
4、下午下班刷卡时间为18:00,刷卡前后30分钟有效,如果同时这时间内有刷两次以上以最后一次为准;
5、晚上加班入刷卡时间为19:01,刷卡前后30分钟有效,如果同时这时间内有刷两次以上以最后一次为准;
6、晚上加班出刷卡时间为第二天的04:00钟,刷卡从19:31至第二天04:30分钟有效,如果同时这时间内有刷两次以上以最后一次为准;
对考勤表(TF_TZ)条件是:
1、JBS(加班时数)=SJ_C(加班出)-SJ_Y(加班入);
2、工时=9;
3、KJ(旷职)=9-{(SC_C(用餐出)-SB_Y(上班入))+(SB_C(下班出)-SC_Y(用餐入))};->如果KJ(旷职)<0时就为空;
4、QTY(基数)=表(TF_TZ)的对应工号与班别对应当天的QTY;目的:以TRS_DD为打卡日期变量范围和YG_NO为工号变量范围写一个存储过程明细表如下: YG_NO BAN_NO TRS_DD SB_Y SC_C SC_Y SB_C SJ_Y SJ_C JBS KJ GS QTY
A002 002 05-10-08 07:22 12:29 13:24 17:45 19:25 02:26 07:01 0 9 2
..................................................................................以上表的字段意义:
SB_Y(上班入)
SC_C(用餐出)
SC_Y(用餐入)
SB_C(下班出)
SJ_Y(加班入)
SJ_C(加班出)
JBS(加班时数)
KJ(旷职)
GS(工时)
QTY(基数)
上面的问题我只算到了(TF_KQ)这个表的考勤表:
而实际上上还有忘记打卡而要补卡的一个表的动作;
如补卡了也要算是打卡的了,就要算到考勤里面去;
补卡表(tf_sktz)YG_NO 员工代号 varchar(20)
TRS_DD 补卡时间 datetime
CHK_ID 审核否()varchar(1)--->为Y是代表审核了,当为N代表没有审核;YG_NO TRS_DD CHK_ID
A0022005-07-08 14:00:00.000Y
A0022005-07-08 18:00:00.000Y
A0022005-07-08 19:00:00.000Y
A0022005-07-09 08:00:00.000Y
A0022005-07-09 12:00:00.000Y
A0022005-07-09 14:00:00.000Y
A0022005-07-09 18:00:00.000Y
A0022005-07-09 19:00:00.000Y
A0022005-07-10 00:00:00.000Y
A0022005-07-10 08:00:00.000Y
。。不知道这样整理行不行
-----------------------------------------------------------------------
create table TF_KQ(YG_NO varchar(20),BAN_NO varchar(20),TRS_DD datetime)
insert into TF_KQ select 'A002','002','2005-10-08 07:23:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 07:22:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 11:31:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 12:04:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 12:29:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 12:45:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 12:51:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 13:24:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 17:33:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 17:45:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 19:22:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 19:25:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 20:22:00.000'
insert into TF_KQ select 'A002','002','2005-10-08 02:25:00.000'create table TF_JBTZ(YG_NO varchar(20),SZ_NO varchar(4),TRS_DD datetime,QTY numeric(28,8),CHK_ID varchar(1))
insert into TF_JBTZ select 'A002','002','2005-10-07 00:00:00.000',4.00000000,'N'
insert into TF_JBTZ select 'A002','002','2005-10-08 00:00:00.000',2.00000000,'Y'
insert into TF_JBTZ select 'A002','002','2005-10-09 00:00:00.000',3.00000000,'N'
insert into TF_JBTZ select 'A002','002','2005-10-10 00:00:00.000',7.00000000,'N'create table TF_SKTZ(YG_NO varchar(20),TRS_DD datetime,CHK_ID varchar(1))
insert into TF_SKTZ select 'A002','2005-07-08 14:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-08 18:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-08 19:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 08:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 12:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 14:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 18:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-09 19:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 00:00:00.000','Y'
insert into TF_SKTZ select 'A002','2005-07-10 08:00:00.000','Y'SELECT YG_NO,BAN_NO,TRS_DD INTO #T FROM TF_KQINSERT INTO #T SELECT YG_NO,NULL as BAN_NO,TRS_DD FROM TF_SKTZSELECT YG_NO,NULL as SZ_NO,TRS_DD,QTY,CHK_ID INTO #T1 FROM TF_JBTZINSERT INTO #T1
SELECT
A.YG_NO,NULL as BAN_NO,A.TRS_DD,NULL,'Y'
FROM
(SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A
WHERE
NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD)
AND
NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD)
SELECT
b.YG_NO,
b.SZ_NO,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
SC_Y = CONVERT(CHAR(5),b.SC_Y,108),
SB_C = CONVERT(CHAR(5),b.SB_C,108),
SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108),
SJ_C = CONVERT(CHAR(5),b.SJ_C,108),
JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0),
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),
b.QTY,
b.CHK_ID
FROM
(SELECT
a.YG_NO,
SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710),
a.TRS_DD,
SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ),
SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ),
SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ),
SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110),
SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171),
SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710),
a.QTY,
a.CHK_ID
FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID
FROM
#T1--TF_JBTZ
UNION
SELECT
DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL
FROM
TF_KQ t
WHERE
NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a
WHERE
EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b
@SDATE DATETIME, --日期范围起始日期
@EDATE DATETIME, --日期范围截止日期
@SYG_NO VARCHAR(20),--工号范围起始工号
@EYG_NO VARCHAR(20) --工号范围截止工号
AS
BEGINSELECT YG_NO,BAN_NO,TRS_DD INTO #T FROM TF_KQINSERT INTO #T SELECT YG_NO,NULL as BAN_NO,TRS_DD FROM TF_SKTZSELECT YG_NO,NULL as SZ_NO,TRS_DD,QTY,CHK_ID INTO #T1 FROM TF_JBTZINSERT INTO #T1
SELECT
A.YG_NO,NULL as BAN_NO,A.TRS_DD,NULL,'Y'
FROM
(SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_SKTZ WHERE CHK_ID='Y') A
WHERE
NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=A.YG_NO AND TRS_DD=A.TRS_DD)
AND
NOT EXISTS(SELECT 1 FROM (SELECT DISTINCT YG_NO,TRS_DD=CAST(CONVERT(CHAR(10),DATEADD(MI,-390,TRS_DD),120) AS DATETIME) FROM TF_KQ) B WHERE B.YG_NO=A.YG_NO AND B.TRS_DD = A.TRS_DD)
SELECT
b.YG_NO,
b.SZ_NO,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
SC_Y = CONVERT(CHAR(5),b.SC_Y,108),
SB_C = CONVERT(CHAR(5),b.SB_C,108),
SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108),
SJ_C = CONVERT(CHAR(5),b.SJ_C,108),
JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0),
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),
b.QTY,
b.CHK_ID
FROM
(SELECT
a.YG_NO,
SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710),
a.TRS_DD,
SB_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ),
SC_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ),
SC_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ),
SB_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110),
SJ_Y = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171),
SJ_C = (SELECT max(TRS_DD) FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710),
a.QTY,
a.CHK_ID
FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CHK_ID
FROM
#T1--TF_JBTZ
UNION
SELECT
DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL
FROM
TF_KQ t
WHERE
NOT EXISTS(SELECT 1 FROM TF_JBTZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a WHERE
(a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01'))
AND
(a.YG_NO BETWEEN @SYG_NO AND @EYG_NO)
AND
EXISTS(SELECT 1 FROM #T WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b--EXEC SP_GetDetail '2005-08-01','2005-09-31','17002114','17002114'
END
GO
把变量加进去,执行EXEC SP_GetDetail '2005-08-01','2005-09-31','17002114','17002114'出现:
Server: Msg 8114, Level 16, State 4, Procedure SP_GetDetail, Line 0
Error converting data type varchar to datetime.