locked
Query optimization RRS feed

  • Question

  • User-1826049516 posted

    Hey,

    This is probably just me aiming for "perfection", but is there a better way to achieve this query.  The requirement is to first check for absences, if only single return that absence, if there are multiple return 'Miltiple'.  If no absences exist, then check for activities and grab the most recent one based on a record in Clocking Records.  If no absences exist and no activities exist, return 'Unknown'.

    select		e.EMP_ClockNumber,
    			case
    				when exists
    				(
    					select		1
    					from		[Absentee Records]
    					where		ABR_Clock_No = e.EMP_ClockNumber and ABR_Shift_Date =
    									case
    										when datename(weekday, getdate()) = 'Saturday' then dateadd(day, -1, convert(date, getdate()))
    										when datename(weekday, getdate()) = 'Sunday' then dateadd(day, -2, convert(date, getdate()))
    										else convert(date, getdate())
    									end
    				)
    				then
    				(
    					case
    						when
    						(
    							select		count(*)
    							from		[Absentee Records]
    							where		ABR_Clock_No = e.EMP_ClockNumber and ABR_Shift_Date =
    											case
    												when datename(weekday, getdate()) = 'Saturday' then dateadd(day, -1, convert(date, getdate()))
    												when datename(weekday, getdate()) = 'Sunday' then dateadd(day, -2, convert(date, getdate()))
    												else convert(date, getdate())
    											end
    						) = 1
    						then
    						(
    							select		rtrim
    										(
    											coalesce(abp.ABP_Name, abt.ABT_Name, skp.SKP_Name, skt.SKT_Name, hlt.HLT_Name)
    										) +
    										case
    											when abr.ABR_Period is not null then
    												case
    													when abr.ABR_Period = 'D' then ' (all day)'
    													when abr.ABR_Period = 'M' then ' (morning only)'
    													when abr.ABR_Period = 'A' then ' (afternoon only)'
    													when abr.ABR_Period = 'C' then ' (' + convert(varchar(5), abr.ABR_Start_Time, 108) + ' - ' + convert(varchar(5), abr.ABR_Finish_Time, 108) + ')'
    												else ''
    												end
    											else ''
    										end
    							from		[Absentee Records] abr left join
    											[Absence Paid] abp on abp.ABP_Code = abr.ABR_Code and abr.ABR_Type = 'Ap' left join
    											[Absence Types] abt on abt.ABT_Code = abr.ABR_Code and abr.ABR_Type = 'A' left join
    											[Sick Paid] skp on skp.SKP_Code = abr.ABR_Code and abr.ABR_Type = 'Sp' left join
    											[Sick Types] skt on skt.SKT_Code = abr.ABR_Code and abr.ABR_Type = 'S' left join
    											[Holiday Types] hlt on hlt.HLT_Code = abr.ABR_Code and abr.ABR_Type = 'H'
    							where		ABR_Clock_No = e.EMP_ClockNumber and ABR_Shift_Date =
    											case
    												when datename(weekday, getdate()) = 'Saturday' then dateadd(day, -1, convert(date, getdate()))
    												when datename(weekday, getdate()) = 'Sunday' then dateadd(day, -2, convert(date, getdate()))
    												else convert(date, getdate())
    											end
    						)
    						else 'Multiple'
    					end
    				)
    				else
    					case
    						when exists
    						(
    							select		top 1 act.ACT_Name
    							from		[Activity Records] acr join
    											Activities act on act.ACT_Code = acr.ACR_ACT_Code
    							where		acr.ACR_Clock_No = e.EMP_ClockNumber and acr.ACR_Shift_Date = 
    										(
    											select		max(CLK_Shift_Date)
    											from		[Clocking Records]
    											where		CLK_Clock_No = acr.ACR_Clock_No
    										)
    							order by	(acr.ACR_Start_Date + acr.ACR_Start_Time) desc
    						)
    						then
    						(
    							select		top 1 act.ACT_Name
    							from		[Activity Records] acr join
    											Activities act on act.ACT_Code = acr.ACR_ACT_Code
    							where		acr.ACR_Clock_No = e.EMP_ClockNumber and acr.ACR_Shift_Date = 
    										(
    											select		max(CLK_Shift_Date)
    											from		[Clocking Records]
    											where		CLK_Clock_No = acr.ACR_Clock_No
    										)
    							order by	(acr.ACR_Start_Date + acr.ACR_Start_Time) desc
    						)
    						else 'Unknown'
    					end
    			end ACT_Name
    from		Employees e
    where		e.EMP_Left_Date is null and e.EMP_Number is not null;

    I have in mind about wrapping the subqueries for absences and activities in coalesce rather than case when... then... else... because absences could easily be null, as can activities for a brand new users who'se setup but not clocked in yet.

    Thanks

    Monday, September 12, 2016 7:56 AM

Answers

  • User-1826049516 posted

    I'm creating a View.  Can parameters be used in a view in this way?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 14, 2016 2:18 PM

All replies

  • User-1826049516 posted

    Oh, the problem I can foresee with coalesce is the count - that would return 0 which is not null so the first parameter in coalesce would always be chosen.

    So ideally I would need to make count return null instead of 0!!!!!

    Monday, September 12, 2016 8:32 AM
  • User-1404113929 posted

    hi, instead of write every time 

    use temp table and based on it modify your query .

    select * into #temp
    from [Absentee Records]
    where ABR_Clock_No = e.EMP_ClockNumber
    and ABR_Shift_Date =
    case
    when datename(weekday, getdate()) = 'Saturday' then dateadd(day, -1, convert(date, getdate()))
    when datename(weekday, getdate()) = 'Sunday' then dateadd(day, -2, convert(date, getdate()))
    else convert(date, getdate())

    select @count=count(*) from #temp

    Thanks ,

    Murali

    Monday, September 12, 2016 11:34 AM
  • User364663285 posted

    Put isnull(column1,1), when checking against columns that can be null.

    Tuesday, September 13, 2016 7:09 AM
  • User-1826049516 posted

    Thanks for the replies.  I've just thought of this:

    when count = 0 do this
    when count = 1 do that
    when count > 1 do this and that

    But I'd have to repeat the same count query for each when clause.  That seems crazy to me:

    select		case
    				when
    				(
    					select		count(*)
    					from		[Absentee Records]
    					where		ABR_Clock_No = 58 and ABR_Shift_Date =
    									case
    										when datename(weekday, getdate()) = 'Saturday' then dateadd(day, -1, convert(date, getdate()))
    										when datename(weekday, getdate()) = 'Sunday' then dateadd(day, -2, convert(date, getdate()))
    										else convert(date, getdate())
    									end
    				) = 0 then 'Do this'
    				when
    				(
    					select		count(*)
    					from		[Absentee Records]
    					where		ABR_Clock_No = 58 and ABR_Shift_Date =
    									case
    										when datename(weekday, getdate()) = 'Saturday' then dateadd(day, -1, convert(date, getdate()))
    										when datename(weekday, getdate()) = 'Sunday' then dateadd(day, -2, convert(date, getdate()))
    										else convert(date, getdate())
    									end
    				) = 1 then 'Do that'
    				when
    				(
    					select		count(*)
    					from		[Absentee Records]
    					where		ABR_Clock_No = 58 and ABR_Shift_Date =
    									case
    										when datename(weekday, getdate()) = 'Saturday' then dateadd(day, -1, convert(date, getdate()))
    										when datename(weekday, getdate()) = 'Sunday' then dateadd(day, -2, convert(date, getdate()))
    										else convert(date, getdate())
    									end
    				) > 1 then 'Do this and that'
    				else 'Unknown'
    			end ACT_Name

    You really ought to be able to just do this:

    select		case
    				when
    				(
    					select		count(*)
    					from		[Absentee Records]
    					where		ABR_Clock_No = 58 and ABR_Shift_Date =
    									case
    										when datename(weekday, getdate()) = 'Saturday' then dateadd(day, -1, convert(date, getdate()))
    										when datename(weekday, getdate()) = 'Sunday' then dateadd(day, -2, convert(date, getdate()))
    										else convert(date, getdate())
    									end
    				) = 0 then 'Do this'
    				else if = 1 then 'Do that'
    				else if > 1 then 'Do this and that'
    				else 'Unknown'
    			end ACT_Name

    But I guess a temp table would get around that.  Or even a view?

    Wednesday, September 14, 2016 2:12 PM
  • User-1826049516 posted

    I'm creating a View.  Can parameters be used in a view in this way?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 14, 2016 2:18 PM