locked
case statement in where clause in sql server RRS feed

  • 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/

    Monday, February 10, 2014 10:51 AM

Answers

  • 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/

    • Marked as answer by Ahsan KabirMVP Monday, February 10, 2014 2:18 PM
    Monday, February 10, 2014 2:17 PM

All replies

  • 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



    Monday, February 10, 2014 10:57 AM
  • 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

    Monday, February 10, 2014 11:00 AM
  • 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,

     
    • Edited by gsclayton Monday, February 10, 2014 11:14 AM
    Monday, February 10, 2014 11:13 AM
  • 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, esquel@sommarskog.se
    Monday, February 10, 2014 12:13 PM
  • 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/

    • Marked as answer by Ahsan KabirMVP Monday, February 10, 2014 2:18 PM
    Monday, February 10, 2014 2:17 PM
  • 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/

    Monday, February 10, 2014 2:18 PM
  • 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, esquel@sommarskog.se
    Monday, February 10, 2014 2:22 PM