locked
CASE expression RRS feed

  • Question

  • User-1826049516 posted

    Hey,

    I'm trying to filter out a query based on the period of absence.  These can be one of 4 values:

    D: All day
    M: Morning only
    A: Afternoon only
    C: Custom (so start and finish times)

    Multiples can exist, so M and A for the same day, D and C for the same day.

    This is my query:

    select		*
    from		[Absentee Records]
    where		ABR_Clock_No = 58 and ABR_Shift_Date = convert(date, getdate()) and ABR_Period =
    			(
    				case
    					when getdate() between (ABR_Start_Date + ABR_Start_Time) and (ABR_Finish_Date + ABR_Finish_Time) then 'C'
    					when convert(time, getdate()) <= '12:00:00.000' then 'M'
    					when convert(time, getdate()) > '12:00:00.000' then 'A'
    					else 'D'
    				end
    			);

    This doesn't work as if it's a D absence, it never gets hit because the 2 preceding ones will either be met (before or after 12).  The goal is to show the M absence if the viewing time is before 12, the A absence if the viewing time is after 12, the C absence if the viewing time falls between the start and finish times, even if another absence exists too.  And the D absence if none of the others exist.

    But the time is always before or after 12, so I can't think what to do.

    This is a sample data set for today, without the ABR_Period where clause:

    ABR_Id	ABR_Clock_No	ABR_Shift_Date	ABR_Type	ABR_Code	ABR_Days	ABR_Period	ABR_Flag	ABR_Time	ABR_Rate	ABR_Start_Date	ABR_Start_Time	ABR_Finish_Date	ABR_Finish_Time	ABR_Notes	ABR_User_Id	ABR_Bonus	ABR_Code2	ABR_Time_Unpaid	ABR_Is_Authorised1	ABR_Is_Authorised2	ABR_CC_Code
    26089	58	2016-09-23 00:00:00	Ap	3	1	D	S	420	0.0000	NULL	NULL	NULL	NULL	NULL	admin               	0.0000	0	0	Y	Y	     
    26090	58	2016-09-23 00:00:00	A 	15	1	C	D	0	0.0000	2016-09-23 00:00:00	1900-01-01 10:40:00	2016-09-23 00:00:00	1900-01-01 11:40:00	NULL	admin               	0.0000	0	60	Y	Y	     

    Friday, September 23, 2016 3:17 PM

Answers

  • User-1826049516 posted

    I managed to get around this use case when exists with not exists to check what's there.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 27, 2016 1:08 PM

All replies

  • User-1826049516 posted
    case
    	when 1=1 then 'C'
    	when 1=1 then 'D'
    	when convert(time, getdate()) <= '12:00:00.000' then 'M'
    	when convert(time, getdate()) > '12:00:00.000' then 'A'
    end
    

    If I use this it works OK, so the date BETWEEN seems to allow the case expression to fall through to the next one, because it picks out both records.

    I thought CASE stops on the first match?  Is this a bug, or am I doing something wrong.

    Friday, September 23, 2016 6:16 PM
  • User77042963 posted

    Check this (you will not be able to do that if one is data and the other  is time:

    ABR_Start_Date + ABR_Start_Time

    --Try this but it is not a good practice

     Cast(ABR_Start_Date as datetime)+ Cast(ABR_Start_Time as datetime)
    Friday, September 23, 2016 7:20 PM
  • User-1826049516 posted

    I managed to get around this use case when exists with not exists to check what's there.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 27, 2016 1:08 PM