以前我发布我查询我修改了这种方式现在结果得到在2秒但得到零值 [英] Previously i posted i query i modified that querye this way now result getting with in 2sec but getting zero value
问题描述
以前我发布了i查询我修改了这个查询这种方式现在结果得到了2秒但是得到零值这有什么不对请帮帮我 Previously i posted i query i modified that query this way now result getting with in 2sec but getting zero value what is wrong in this please help me out 这篇关于以前我发布我查询我修改了这种方式现在结果得到在2秒但得到零值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
< pre lang =SQL> USE [INVATTENDENCEL]
GO
/ * *****对象:StoredProcedure [dbo]。[SP_GET_EXTRA_HOURS_WORKED_NEW]脚本日期:02/12/2016 09:19 :52 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- EXEC [SP_GET_EXTRA_HOURS_WORKED_NN] 29,&#39; Mechanical CHP Maintenance&#39 ;,&安培;#39; 201 6-02-11&#39;
ALTER proc [dbo]。[SP_GET_EXTRA_HOURS_WORKED_NNN]
@ SITE_ID INT ,
@ DEPT_NAME VARCHAR ( 100 ),
@ TO_DATE DATETIME
AS
声明 @ d1 int , @ d2 int , @ d3 int , @ d4 int , @ d5 int , @ m1 int , @ m2 int , @ m3 int , @ m4 int , @ m5 int , @ y1 int , @ y2 int , @ y3 int , @ y4 int , @ y5 int
BEGIN
set @ d1 = (选择 SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-4, @ TO_DATE ), 5 ), 0 , 3 ))
set @ m1 =(选择 SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-4, @ TO_DATE ), 5 ), 4 , 2 ))
set @ y1 =( select SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-4, @ TO_DATE ), 5 ), 7 , 2 ))
set @ d2 =(选择 SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-3, @ TO_DATE ), 5 ), 0 , 3 ))
set @ m2 =( select SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-3) , @ TO_DATE ), 5 ), 4 , 2 ))
set @ y2 =(选择 SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-3, @ TO_DATE ), 5 ), 7 , 2 ))
set @ d3 =(选择 SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-2, @ TO_DATE ), 5 ), 0 , 3 ))
set @ m3 =(选择 SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-2, @ TO_DATE ), 5 ), 4 , 2 ))
set @ y3 =(选择 SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-2, @ TO_DATE ), 5 ), 7 , 2 ))
set @ d4 =(选择 SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-1, @ TO_DATE ) , 5 ), 0 , 3 ) )
set @ m4 =(选择 SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-1, @ TO_DATE ), 5 ), 4 , 2 ))
set @ y4 =(选择 SUBSTRING( CONVERT ( VARCHAR ( 8 ),DATEadd(DD,-1, @ TO_DATE ), 5 ), 7 , 2 ))
set @ d5 =( select SUBSTRING( CONVERT ( VARCHAR ( 8 ), @ TO_DATE , 5 ), 0 , 3 ))
set @ m5 =(选择 SUBSTRING( CONVERT ( VARCHAR ( 8 ), @ TO_DATE , 5 ), 4 , 2 ))
set @ y5 =( select SUBSTRING( CONVERT ( VARCHAR ( 8 ), @ TO_DATE , 5 ), 7 , 2 ))
创建 表# temp1(
EMP_CODE VARCHAR ( 1000 ),
EMP_NAME CHAR ( 50 ),G INT ,F INT ,S INT ,T INT ,G1 INT ,F1 INT ,S1 INT ,T1 INT )
< span class =code-keyword> create table TMP1(
EMP_CODE VARCHAR ( 1000 ),
EMP_NAME CHAR ( 50 ),G INT ,F INT ,S INT ,T INT ,G1 INT ,F1 INT ,S1 INT ,T1 INT )
创建 表 #temp2(
EMP_CODE VARCHAR ( 1000 ),
EMP_NAME CHAR ( 50 ),G < span class =code-keyword> INT ,F INT ,S INT ,T INT ,G1 INT ,F1 INT , S1 INT ,T1 INT )
INSERT INTO #temp1(EMP_CODE,EMP_NAME,G,F,S,T)
< span class =code-keyword>选择 distinct emp.EMP_CODE,emp.EMP_NAME,
case 当 et.DAY=@d1 和 et.MONTH=@m1 和 et.YEAR=@y1 和 et.EMP_ID = emp.EMP_CODE 和 et.site_id=@SI TE_ID 然后 et.GENERAL_SHIFT else 0 < span class =code-keyword> end as G,
case et.DAY =@d1 和 et.MONTH=@m1 和 et.YEAR=@y1 和 et.EMP_ID = emp.EMP_CODE 和 et.site_id=@SITE_ID 然后 et.FIRST_SHIFT else 0 end as F,
case 当 et.DAY=@d1 和 et.MONTH=@m1 和 et.YEAR=@y1 和 et.EMP_ID = emp.EMP_CODE
案例 何时 et.DAY=@d1 和 et.MONTH=@m1 和 et.YEAR=@y1 和 et.EMP_ID = emp.EMP_CODE 和 et.site_id=@SITE_ID 然后 et.THIRD_SHIFT else 0 end as T
来自 dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
left join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE = et.EMP_ID
其中 EMP.SITE_ID=@SITE_ID AND EMP。[STATUS] =&#39; Present&#39; AND emp.DEPT_NAME = @DEPT_NAME
INSERT INTO #temp2(EMP_CODE,EMP_NAME,G1,F1,S1,T1)
选择 distinct emp.EMP_CODE,emp.EMP_NAME,
case when et.DAY=@d2 和 et.MONTH=@m2 和 et.YEAR = @ y2 和 et.EMP_ID = emp.EMP_CODE 和 et.site_id=@SITE_ID then et.GENERAL_SHIFT else 0 结束 as G1,
case when et.DAY=@d2 和 et.MONTH=@m2 和 et.YEAR=@y2 和 et.EMP_ID = emp.EMP_CODE 和 et.site_id=@SITE_ID 然后 et.FIRST_SHIFT else 0 end as F1,
case et.DAY =@d2 和等。 MONTH = @ m2 和 et.YEAR=@y2 和 et.EMP_ID = emp.EMP_CODE 和 et.site_id=@SITE_ID 然后 et.SECOND_SHIFT else 0 end as S1,
case et.DAY=@d2 和 et.MONTH=@m2 和 et.YEAR=@y2 和 et.EMP_ID = emp.EMP_CODE 和 et.site_id=@SITE_ID 然后 et .THIRD_SHIFT else 0 end as T1
来自 dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
left join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE = et.EMP_ID
其中 EMP.SITE_ID=@SITE_ID AND EMP。[状态] =&#39;现在&# 39; AND emp.DEPT_NAME = @DEPT_NAME
update T1 set T1.G1 = isnull(T2.G1, 0 ),T1.F1 = isnull(T2.F1, 0 ),T1.S1 = isnull(T2.S1, 0 ),T1.T1 = isnull(T2.T1 , 0 )
FROM #temp1 T1 JOIN #temp2 T2 ON T1.EMP_CODE = T2.EMP_CODE
insert 进入 TMP1 选择 * 来自# temp1
update TMP1 set G1 = isnull(G1, 0 ),F1 = isnull(F1, 0 ),S1 = isnull(S1, 0 跨度>),T1 = ISNULL(T1 , 0 )
SELECT * FROM TMP1
drop table TMP1
drop table #temp1
drop 表#temp2
END
0值
我尝试过:
以前我发布了i查询我修改了这种方式查询现在结果在2秒内得到但是得到零值这有什么问题请帮帮我 I我不是SQL专家,但我觉得你的数据库架构是错误的。
你需要使用 Date
字段而不是 et.DAY
, et.MONTH
和 et.YEAR
。
你还应该看看什么是SQL pivot
。
USE [INVATTENDENCEL]
GO
/****** Object: StoredProcedure [dbo].[SP_GET_EXTRA_HOURS_WORKED_NEW] Script Date: 02/12/2016 09:19:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC [SP_GET_EXTRA_HOURS_WORKED_NN]29,'Mechanical CHP Maintenance','2016-02-11'
ALTER proc [dbo].[SP_GET_EXTRA_HOURS_WORKED_NNN]
@SITE_ID INT,
@DEPT_NAME VARCHAR(100),
@TO_DATE DATETIME
AS
declare @d1 int,@d2 int,@d3 int,@d4 int,@d5 int,@m1 int,@m2 int,@m3 int,@m4 int,@m5 int,@y1 int,@y2 int,@y3 int,@y4 int,@y5 int
BEGIN
set @d1=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-4,@TO_DATE), 5),0,3))
set @m1=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-4,@TO_DATE), 5),4,2))
set @y1=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-4,@TO_DATE), 5),7,2))
set @d2=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-3,@TO_DATE), 5),0,3))
set @m2=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-3,@TO_DATE), 5),4,2))
set @y2=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-3,@TO_DATE), 5),7,2))
set @d3=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-2,@TO_DATE), 5),0,3))
set @m3=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-2,@TO_DATE), 5),4,2))
set @y3=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-2,@TO_DATE), 5),7,2))
set @d4=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-1,@TO_DATE), 5),0,3))
set @m4=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-1,@TO_DATE), 5),4,2))
set @y4=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-1,@TO_DATE), 5),7,2))
set @d5=(select SUBSTRING(CONVERT(VARCHAR(8), @TO_DATE, 5),0,3))
set @m5=(select SUBSTRING(CONVERT(VARCHAR(8), @TO_DATE, 5),4,2))
set @y5=(select SUBSTRING(CONVERT(VARCHAR(8), @TO_DATE, 5),7,2))
create table #temp1(
EMP_CODE VARCHAR(1000),
EMP_NAME CHAR(50),G INT,F INT,S INT,T INT,G1 INT,F1 INT,S1 INT,T1 INT)
create table TMP1(
EMP_CODE VARCHAR(1000),
EMP_NAME CHAR(50),G INT,F INT,S INT,T INT,G1 INT,F1 INT,S1 INT,T1 INT)
create table #temp2(
EMP_CODE VARCHAR(1000),
EMP_NAME CHAR(50),G INT,F INT,S INT,T INT,G1 INT,F1 INT,S1 INT,T1 INT)
INSERT INTO #temp1 (EMP_CODE,EMP_NAME,G,F,S,T)
select distinct emp.EMP_CODE,emp.EMP_NAME,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.GENERAL_SHIFT else 0 end as G,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.FIRST_SHIFT else 0 end as F,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.SECOND_SHIFT else 0 end as S,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.THIRD_SHIFT else 0 end as T
from dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
left join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE=et.EMP_ID
where EMP.SITE_ID=@SITE_ID AND EMP.[STATUS]='Present' AND emp.DEPT_NAME=@DEPT_NAME
INSERT INTO #temp2 (EMP_CODE,EMP_NAME,G1,F1,S1,T1)
select distinct emp.EMP_CODE,emp.EMP_NAME,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.GENERAL_SHIFT else 0 end as G1,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.FIRST_SHIFT else 0 end as F1,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.SECOND_SHIFT else 0 end as S1,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.THIRD_SHIFT else 0 end as T1
from dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
left join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE=et.EMP_ID
where EMP.SITE_ID=@SITE_ID AND EMP.[STATUS]='Present' AND emp.DEPT_NAME=@DEPT_NAME
update T1 set T1.G1=isnull(T2.G1,0),T1.F1=isnull(T2.F1,0),T1.S1=isnull(T2.S1,0),T1.T1=isnull(T2.T1,0)
FROM #temp1 T1 JOIN #temp2 T2 ON T1.EMP_CODE=T2.EMP_CODE
insert into TMP1 select * from #temp1
update TMP1 set G1=isnull(G1,0),F1=isnull(F1,0),S1=isnull(S1,0),T1=isnull(T1,0)
SELECT * FROM TMP1
drop table TMP1
drop table #temp1
drop table #temp2
END
0 value
What I have tried:
Previously i posted i query i modified that query this way now result getting with in 2sec but getting zero value what is wrong in this please help me outI am not SQL specialist, but I have the feeling that your DB schema is wrong.
You need to use a Date
field rather than et.DAY
, et.MONTH
and et.YEAR
.
You should also have a look at what is an SQL pivot
.