May 6, 2009 at 5:03 pm
Hey gang. I'm pretty sure I'm taking the long way around this issue...
The first CASE statement is in there in an attempt to avoid a "Difference of two datetime columns caused overflow at runtime" error on the DATEDIFF(ss...) statement. 60 year response times are nonsense, so we just need a placeholder (999999) of any kind there.
What am I overlooking? For context, this is part of a User Defined Table Function.
Is there a native LESSER(val1, val2) function I could use?
THANKS!
SELECT
...
CASE WHEN (
CASE WHEN r.priority_number <= 3 THEN rmi.Time_FirstCallTakingKeystroke
WHEN r.priority_number > 3 THEN COALESCE(rpi.Time_PickUpPromised, rmi.Time_FirstCallTakingKeystroke)
END) < '1975-01-01'
THEN 999999
ELSE DATEDIFF(ss,
CASE WHEN r.priority_number <= 3 THEN rmi.Time_FirstCallTakingKeystroke
WHEN r.priority_number > 3 THEN COALESCE(rpi.Time_PickUpPromised, rmi.Time_FirstCallTakingKeystroke) END,
COALESCE( rva.Fixed_Time_Staged, rva.Fixed_Time_ArrivedAtScene, rva.Fixed_Time_Call_Cleared))
END AS ResponseTime_secs,
May 6, 2009 at 8:52 pm
You can make it look a little bit better with CTE's:
;WITH cteSourceJoin as (
Select *
, CASE WHEN r.priority_number 3 THEN COALESCE(rpi.Time_PickUpPromised, rmi.Time_FirstCallTakingKeystroke)
END as DerivedTime
From r, rpi, rmi -- some JOIN here, no doubt..
)
SELECT
...
CASE WHEN DerivedTime < '1975-01-01'
THEN 999999
ELSE DATEDIFF(ss, DerivedTime,
COALESCE( rva.Fixed_Time_Staged, rva.Fixed_Time_ArrivedAtScene, rva.Fixed_Time_Call_Cleared))
END AS ResponseTime_secs,
...
From cteSourceJoin
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 9:34 pm
There isn't a native one, but why not write one? To solve your DATEDIFF issue, I actually think you want the greater value. It can be made an inline table valued function. Pseudocode follows.
create function greaterDate
@parm1 datetime
@parm2 datetime
as
select case when @parm1 >= @parm2 then @parm1 else @parm2 end as lesserValue
--
DATEDIFF(ss, greaterDate('1975-01-01', DerivedTime), COALESCE( rva.Fixed_Time_Staged, rva.Fixed_Time_ArrivedAtScene)
--
A CLR function is another option, but either way I think you would be sacrificing performance for simplicity of code. Your CASE expressions may look ugly, but they work just fine. Listen to Barry, and use CTEs to break things down step by step to make it read better. With complicated case expressions or nested functions, I will sometimes use three or 4 CTEs that I can test one at a time so that a single humongous string doesn't become visually overwhelming.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 7, 2009 at 12:00 pm
Fellas, that Common Table Expression worked like a charm. I'm now avoiding gobs of tempdb space, avoiding referencing tables multiple times, avoiding the date arithmetic error, and returning resultsets quicker. It will be good to be versed in the CTE construct. Was that introduced in 2005?
THANK YOU!
May 7, 2009 at 12:09 pm
Greg J (5/7/2009)
Was that introduced in 2005?
Yes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply