locked
Case when is not working please RRS feed

  • Question

  • I am trying to  assign the value 0 for the result isnull  else  the value should be 1

    If Minclockin is null then the value  should be 0 else 1. How can make it sql Please help . I applied the following code but not working

    DECLARE @Date  smalldatetime
    declare  @dateclocked as date
    declare @maxtimeclockin as varchar(max)
    SET @Date = '10-14-2019'
    set @dateclocked = CAST(@Date as date)
     select  Case [Minclockin] from  [dbo].[goViewEmpMinMaxClocking]    
    WHERE  EmpCode = 13 AND  ClockedDay =  @dateclocked
    When is Isnull  then 0
    Else 1 end;
    


    polachan

    Monday, October 14, 2019 10:39 AM

All replies

  • I guess you have incorrect syntax there.

    See if this works for you and some examples on case statements:

    DECLARE @Date SMALLDATETIME
    DECLARE @dateclocked AS DATE
    DECLARE @maxtimeclockin AS VARCHAR(max)
    
    SET @Date = '10-14-2019'
    SET @dateclocked = CAST(@Date AS DATE)
    
    SELECT CASE 
    		WHEN [Minclockin] IS NULL THEN 0 ELSE 1	END AS MinclockinValue
    FROM [dbo].[goViewEmpMinMaxClocking]
    WHERE EmpCode = 13
    	AND ClockedDay = @dateclocked


    Regards,
    Vaibhav

    Monday, October 14, 2019 10:46 AM
  • When I apply the code with another sql it is not working. Multipart identifier is not working. If I remove the line case when , it would work please any idea to fix

     

    SELECT [LogID] ,[AttendanceDate] ,Employee.DepotNo ,Employee.DepartmentID

    ,ISNULL((SELECT  MinClockin FROM [dbo].[goViewEmpMinMaxClocking]    WHERE  EmpCode = Employee.EmployeeID AND  ClockedDay =  @dateclocked),'00:00') ClockedIn
    ,ISNULL((SELECT  MaxClockOut FROM [dbo].[goViewEmpMinMaxClocking]    WHERE  EmpCode = Employee.EmployeeID AND  ClockedDay =  @dateclocked),'00:00') ClockedOut

    CASE WHEN [Minclockin] IS NULL THEN 0 ELSE 1 END AS MinclockinValue FROM [dbo].[goViewEmpMinMaxClocking] WHERE EmpCode = Employee.EmployeeID AND ClockedDay = @dateclocked FROM dbo.goEmployee Employee INNER JOIN [dbo].[goAttendancelog] Attendancelog ON Employee.EmployeeID = Attendancelog.EmployeeID



    polachan



    • Edited by polachan Monday, October 14, 2019 11:04 AM
    Monday, October 14, 2019 11:02 AM
  • Hi polachen,

    Check below code- Using CASE Expression

    SELECT  [LogID],
    		[AttendanceDate],
    		Employee.DepotNo,
    		Employee.DepartmentID,
    		ISNULL(CLK.MinClockin,'00:00') ClockedIn,
    		ISNULL(CLK.MaxClockOut,'00:00') ClockedOut,
    		CASE CLK.[Minclockin] IS NULL THEN 0 ELSE 1 END AS MinclockinValue
    FROM dbo.[goEmployee] Employee 
    INNER JOIN [dbo].[goAttendancelog] Attendancelog  ON Employee.EmployeeID = Attendancelog.EmployeeID 
    LEFT JOIN [dbo].[goViewEmpMinMaxClocking] clk ON CLK.EmpCode = Employee.EmployeeID 
    AND  CLK.ClockedDay = @dateclocked

    Check below code- Using IIF Function

    SELECT  [LogID],
    		[AttendanceDate],
    		Employee.DepotNo,
    		Employee.DepartmentID,
    		ISNULL(CLK.MinClockin,'00:00') ClockedIn,
    		ISNULL(CLK.MaxClockOut,'00:00') ClockedOut,
    		IIF(CLK.[Minclockin] IS NULL,0,1) AS MinclockinValue
    FROM dbo.[goEmployee] Employee 
    INNER JOIN [dbo].[goAttendancelog] Attendancelog  ON Employee.EmployeeID = Attendancelog.EmployeeID 
    LEFT JOIN [dbo].[goViewEmpMinMaxClocking] clk ON CLK.EmpCode = Employee.EmployeeID 
    AND  CLK.ClockedDay = @dateclocked

    Note: Suggested one of the many ways that is easy to understand and implement.
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi


    • Proposed as answer by Jayanth Kurup Tuesday, October 15, 2019 8:29 AM
    Monday, October 14, 2019 4:25 PM
  • Hi polachan,

    Just a Info-

    You can avoid multiple calling of same table with the help of required join/temporary table/table variable/subquery/CTE based on the need.

    or else the scan count and logical reads would increase which will eventually lead to performance problems too. you can check scan/logical counts using 'SET STATISTICS IO ON'.

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Monday, October 14, 2019 4:30 PM
  • Hi polachan,

    Or would you like this one ? 

    Could you  please share us your table structure (CREATE TABLE …) and some sample data (INSERT INTO…) along with your expected result? So that we’ll get a right direction and make some test.

    SELECT a.[LogID]
    ,a.[AttendanceDate]
    ,Employee.DepotNo
    ,Employee.DepartmentID
    ,ISNULL( a.MinClockin ,'00:00') ClockedIn
    ,ISNULL(a.MaxClockOut ,'00:00') ClockedOut
    ,CASE WHEN a.[Minclockin] IS NULL THEN 0 ELSE 1	END AS MinclockinValue
    FROM [dbo].[goViewEmpMinMaxClocking] a	
    INNER JOIN  dbo.goEmployee Employee on a.EmpCode = Employee.EmployeeID
    INNER JOIN [dbo].[goAttendancelog] Attendancelog  ON Employee.EmployeeID = Attendancelog.EmployeeID 
    WHERE  a.ClockedDay = @dateclocked

    Best Regards,

    Rachel



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 15, 2019 8:17 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 17, 2019 9:39 AM