case statement in where clause in sql server

Question

HI

Case statement is not working in where clause .

	ON (E.AttendanceCode = HEA.AttandanceCode  AND InOut = 10)
	WHERE 
	Case when @Employcode='*' then E.EmployeeCode IN(
						SELECT DISTINCT EmployeeCode FROM dbo.HR_AttendanceDetails AS HEA
						WHERE CONVERT(VARCHAR(10),AttendanceTime,120) = CONVERT(VARCHAR(10), @ProcessDate,120)
						AND CONVERT(VARCHAR(10),E.AttendanceTime,120) = CONVERT(VARCHAR(10), @ProcessDate,120)
					)
		when @Employcode<>'*' Then E.EmployeeCode =@Employcode
    end

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 1

That's not correct syntax, CASE statement doesn't work that way. here is a example 

WHERE
@locationID = 
  CASE @locationType
      WHEN 'location' THEN account_location
      WHEN 'area' THEN xxx_location_area 
      WHEN 'division' THEN xxx_location_division 
  END

or

SELECT * 
FROM Table
WHERE dateCol = 
   CASE WHEN @date is not null then @date ELSE dateCol END

---

Satheesh
My Blog

Answer 2

The CASE statement needs to be a part of the expression and not written directly.

For eg:

ON (E.AttendanceCode = HEA.AttandanceCode  AND InOut = 10)
WHERE @Employcode= CASE STATEMENT

Answer 3

As per the other replies above, your where clause needs to return a Boolean (true/false) value where something equals something. I think the below may work, however have done this blind :-) .

WHERE @EmployeeCode=

      (Case when @Employcode='*' then (SELECT DISTINCT EmployeeCode FROM dbo.HR_AttendanceDetails AS HEA

                                                      WHERE CONVERT(VARCHAR(10),AttendanceTime,120) = CONVERT(VARCHAR(10), @ProcessDate,120)

                                                      AND CONVERT(VARCHAR(10),E.AttendanceTime,120) = CONVERT(VARCHAR(10), @ProcessDate,120)

                                                      )

      when @Employcode<>'*' Then E.EmployeeCode

    end)

Regards,

 

Answer 4

WHERE E.EmployeeCode = @EmployeeCode OR
      (@EmployeeCode = '*' AND
       E.AttenanceTime >= convert(date, @ProcessDate) AND
       E.AttenanceTime <  dateadd(DAY, 1, convert(date, @ProcessDate)) AND
         EXISTS (SELECT *
                 FROM   dbo.HR_AttendanceDetails AS HEA
                 WHERE  HEA.AttenanceTime >= convert(date, @ProcessDate)
                   AND  HEA.AttenanceTime <  dateadd(DAY, 1, convert(date, @ProcessDate))
                  AND  HEA.EmployeeCode = E.EmployeeCode))
OPTION (RECOMPILE)

Note that I have changed the handling of the date columns, so that any indexes on the AttendaceTime columns can be used.

I've also added OPTION(RECOMPILE), since the the best plan for this query depends on the actual parameters.

Obviously, the code is untested.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Hello

I have resolved it by below ,if you have any solution then tell me

 alter PROC [dbo].[uspHR_GenerateEmployeeAttendanceSummary]

/*-------------------------------------------------------------------
Author  :   Creation/Modification Date  :  Remarks:  
-------------------------------------------------------------------  
Ahsan Kabir  10/02/2014           Using this SP Attendance details data are 
								  Summariezed and insert into a HR_DailyAttendanceSummary


EXEC [uspHR_GenerateEmployeeAttendanceSummary1]  '027D6D2E-2062-41B5-8B65-238225DD1810','29-Dec-2013'

--------------------------------------------------------------------*/
@Employcode varchar(36)= '*'  ,
@ProcessDate    VARCHAR(11)='*'

AS
SET DATEFORMAT YMD
Declare @strSQL nvarchar(max)
Set @strSQL=' '

IF  (Isnull(@Employcode,'*') <> '*')       
  BEGIN        
   IF charindex('WHERE',@strSQL)>0       
    SET @strSQL=@strSQL+' AND '       
   ELSE       
    SET @strSQL=@strSQL+' WHERE ' 
             
   SET @strSQL=@strSQL+' A.EmployeeCode=CAST('''+@Employcode+''' AS VARCHAR(36))'   
  END   

DELETE FROM HR_DailyAttendanceSummary WHERE CONVERT(VARCHAR(11),AttendanceDate,120) = CONVERT(VARCHAR(11),@ProcessDate,120)


Set @strSQL ='SELECT NEWID() AttendanceSummaryCode,A.EmployeeCode,A.AttendanceDate,A.InTime,A.OutTime
,CASE WHEN S.DayType = 0 THEN ''W''
	  WHEN CONVERT(TIME,A.InTime) <= CONVERT(TIME,S.LateTime) THEN ''P''
	  WHEN (CONVERT(TIME,A.InTime) > CONVERT(TIME,S.LateTime) AND CONVERT(TIME,A.InTime) < CONVERT(TIME,S.AbsentTime))  THEN ''D'' 
	  WHEN CONVERT(TIME,A.InTime) >= CONVERT(TIME,S.AbsentTime)  THEN ''A'' 
	  WHEN S.WorkingHourCode IS NULL THEN ''N/D'' END [Status]	  
,A.WorkingUnitCode,S.WorkingHourCode,''0E2C7D4B-B442-4A75-8D53-581EA847C0CB'' UserCode
,GETDATE() ActionDate,''INSERT'' ActionType,null ComplianceOTHour,null PolicyCode
FROM 
(
SELECT CASE WHEN InTime.EmployeeCode IS NOT NULL THEN InTime.EmployeeCode
			WHEN OutTime.EmployeeCode IS NOT NULL THEN OutTime.EmployeeCode END EmployeeCode
	  ,CASE WHEN InTime.AttendanceDate IS NOT NULL THEN InTime.AttendanceDate
	 	    WHEN OutTime.AttendanceDate IS NOT NULL THEN OutTime.AttendanceDate END AttendanceDate
    ,InTime.InTime,OutTime.OutTime,InTime.WorkingUnitCode

FROM (
	SELECT E.EmployeeCode,CAST(CONVERT(varchar(11),AttendanceTime,120) AS DATETIME) AttendanceDate
			,min(AttendanceTime) InTime ,E.WorkingUnitCode 
	FROM HR_AttendanceDetails E
	INNER JOIN HR_Employee AS HE2 ON (E.EmployeeCode = HE2.EmployeeCode AND HE2.ActionType<> ''DELETE'')
	INNER JOIN HR_EmployeeAttendance AS HEA ON (E.AttendanceCode = HEA.AttandanceCode  AND InOut = 10)
	WHERE [dbo].[fxn_cDate](E.AttendanceTime) = [dbo].[fxn_cDate]( cast( '''+ @ProcessDate +''' as varchar(11)))
	                                                              
	GROUP BY E.EmployeeCode,E.WorkingUnitCode,CAST(CONVERT(varchar(11),AttendanceTime,120) AS DATETIME)
	)InTime LEFT JOIN 
	(
	SELECT  E.EmployeeCode,CAST(CONVERT(varchar(11),AttendanceTime,120) AS DATETIME) AttendanceDate
			,MAX(AttendanceTime) OutTime ,E.WorkingUnitCode ,min(AttendanceTime)Ina
	FROM HR_AttendanceDetails E
	INNER JOIN HR_Employee AS HE2 ON (E.EmployeeCode = HE2.EmployeeCode AND HE2.ActionType<> ''DELETE'')
	INNER JOIN HR_EmployeeAttendance AS HEA ON (E.AttendanceCode = HEA.AttandanceCode AND InOut = 00)
	WHERE [dbo].[fxn_cDate](E.AttendanceTime) = [dbo].[fxn_cDate]( cast( ''' + @ProcessDate +''' as varchar(11)))
	GROUP BY E.EmployeeCode,E.WorkingUnitCode,CAST(CONVERT(varchar(11),AttendanceTime,120) AS DATETIME)
	)OutTime ON InTime.EmployeeCode = OutTime.EmployeeCode

)A
LEFT Join [HR_MapShiftWithEmployee] M on A.EmployeeCode=M.EmployeeCode AND [dbo].[fxn_cDate](A.AttendanceDate) between [dbo].[fxn_cDate](M.EffectiveFromDate) 
and [dbo].[fxn_cDate](M.EffectiveToDate)
LEFT Join HR_AttendanceWorkingHour S On M.ShiftCode=S.ShiftCode AND LEFT(DATENAME(DW,A.AttendanceDate),3)=[DayName] AND S.ActionType <> ''DELETE'''
+@strSQL

--Select @strSQL
INSERT INTO HR_DailyAttendanceSummary
EXEC sp_executeSQL @strSQL 

Go

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 6

Actually I don't like to use

EXEC sp_executeSQL @strSQL 

on the above sp.

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 7

Actually I don't like to use

EXEC sp_executeSQL @strSQL

Nor do I. Did you look at my post?

And if you use sp_executesql, you should pass the parameter values as, yes, parameters, not inline them.

And you should not include SET DATEFORMAT in your stored procedures. Nor should you write any code which is depend on the date format.

---

Erland Sommarskog, SQL Server MVP, [e-mail]