My query is running more than 15 sec help me here to get result in less than 3 sec,thanks in advance

Question

2.00/5 (1 vote)

See more:

SQL-Server-2008R2

C#

Hi My query is running more than 15 sec help me here to get result in less than 3 sec, below my stored procedure is their Thanks in Advance

   ALTER proc [GET_EXTRA_HOURS_WORKED]
   @SITE_ID INT,
   @DEPT_NAME VARCHAR(100),
   @TO_DATE DATETIME
   AS
   BEGIN
   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

   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))


    select distinct emp.EMP_CODE,emp.EMP_NAME,
    isnull((select et1.GENERAL_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d1 and et1.MONTH=@m1 and et1.YEAR=@y1 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as G,
   isnull((select et1.FIRST_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d1 and et1.MONTH=@m1 and et1.YEAR=@y1 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as F,
   isnull((select et1.SECOND_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d1 and et1.MONTH=@m1 and et1.YEAR=@y1 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as S,
   isnull((select et1.THIRD_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d1 and et1.MONTH=@m1 and et1.YEAR=@y1 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as T,

   isnull( (select et1.GENERAL_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d2 and et1.MONTH=@m2 and et1.YEAR=@y2 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as G1,
   isnull((select et1.FIRST_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d2 and et1.MONTH=@m2 and et1.YEAR=@y2 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as F1,
   isnull((select et1.SECOND_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d2 and et1.MONTH=@m2 and et1.YEAR=@y2 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as S1,
   isnull((select et1.THIRD_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d2 and et1.MONTH=@m2 and et1.YEAR=@y2 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as T1,

    isnull((select et1.GENERAL_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d3 and et1.MONTH=@m3 and et1.YEAR=@y3 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as G2,
   isnull((select et1.FIRST_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d3 and et1.MONTH=@m3 and et1.YEAR=@y3 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as F2,
   isnull((select et1.SECOND_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d3 and et1.MONTH=@m3 and et1.YEAR=@y3 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as S2,
   isnull((select et1.THIRD_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d3 and et1.MONTH=@m3 and et1.YEAR=@y3 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as T2,

    isnull((select et1.GENERAL_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d4 and et1.MONTH=@m4 and et1.YEAR=@y4 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as G3,
   isnull((select et1.FIRST_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d4 and et1.MONTH=@m4 and et1.YEAR=@y4 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as F3,
   isnull((select et1.SECOND_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d4 and et1.MONTH=@m4 and et1.YEAR=@y4 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as S3,
   isnull((select et1.THIRD_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d4 and et1.MONTH=@m4 and et1.YEAR=@y4 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as T3,

    isnull((select et1.GENERAL_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d5 and et1.MONTH=@m5 and et1.YEAR=@y5 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as G4,
   isnull((select et1.FIRST_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d5 and et1.MONTH=@m5 and et1.YEAR=@y5 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as F4,
   isnull((select et1.SECOND_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d5 and et1.MONTH=@m5 and et1.YEAR=@y5 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as S4,
   isnull((select et1.THIRD_SHIFT from EMP_FPONM_EMP_TIME_SHEET et1 where et1.DAY=@d5 and et1.MONTH=@m5 and et1.YEAR=@y5 and et1.EMP_ID=emp.EMP_CODE and et1.site_id=@SITE_ID),0) as T4
     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


   END

What I have tried:

My query is running more than 15 sec help me here to get result in less than 3 sec,Thanks in Advance

Posted 11-Feb-16 8:28am

durga63

Add a Solution

Comments

PIEBALDconsult 11-Feb-16 15:15pm

Seek thee DATEPART.

And I have never seen anything as monstrous as that other part.

CHill60 11-Feb-16 20:54pm

Ah ... I wish I had seen your comment before I expanded the code. I am in shock. It is indeed beyond monstrous

durga63 11-Feb-16 22:42pm

So please suggest me what should i do

durga63 11-Feb-16 23:35pm

I CHANGED QUERY LIKE THIS ITS RUNNEING WITH IN 1 SEC BUT GETTING 0 VALUE
WHAT IS WRONG WITH THIS

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)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2016-02-11T09:22:00

Solution 2

Over time as those tables fill your performance will just get more and more terrible. Don't use all the subqueries. Instead join to the tables.

Posted 11-Feb-16 9:22am

ZurdoDev

Comments

durga63 11-Feb-16 16:13pm

Can you please re write the query and help me out

ZurdoDev 11-Feb-16 16:17pm

No, but I can help you with a join if you get stuck.

durga63 11-Feb-16 23:10pm

ok.Thank u

actually iam creating two temp tables select data from two temp tables finally data updated to one table but iam getting Zero value so please help me here

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 left JOIN #temp2 T2 ON T1.EMP_CODE=T2.EMP_CODE

ZurdoDev 12-Feb-16 6:53am

If you are getting zero either the field is null or zero. If null, it's likely that there are no matching emp_codes between the two tables.

Corporal Agarn · Answer 2 · 2016-02-11T08:41:00

Solution 1

1) you do not need the select for your sets.
2) What about using Day, Month, Year

SQL

set @d1=DAY(@TO_DATE);
set @m1=MONTH(@TO_DATE);
set @y1=Year(@TO_DATE);

3) Look at using something like

SQL

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 AS G

Posted 11-Feb-16 8:41am

Corporal Agarn

Comments

durga63 11-Feb-16 14:55pm

Actually day,month,year is set for select last 5 days from today so iam using like that

Sascha Lefèvre 11-Feb-16 15:41pm

Then what about this:
set @d1 = DAY(DATEadd(DD,-4,@TO_DATE))
etc.

durga63 11-Feb-16 16:03pm

ok how to get month and year

Corporal Agarn 12-Feb-16 6:28am

You need to learn how the code presented by Sascha Lefevre works
Month would be set @m1 = MONTH(DATEADD(DD,-4,@TO_DATE)) do you think you can get year?

durga63 11-Feb-16 16:10pm

Can you please re write the entire query and help me out

durga63 11-Feb-16 23:38pm

i RE WRITE THE QUERY IAM GETTING OUTPUT WITH IN SEC BUT GETTING O VALUE EVERY TIME
PLEAS
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=is

Corporal Agarn 12-Feb-16 6:30am

Try rewriting using the suggestions above