A trigger is the perfect approach for this.  Some developers just irrationally avoid triggers no matter what.

Be sure to specify option FIRE_TRIGGERS in your BULK INSERT...

The duplicate...

The SET applies to all SELECTs.

You're welcome.  I'm very glad it helped.  And thanks for the follow-up and nice feedback!

A trigger is the perfect approach for this.  Some developers just irrationally avoid triggers no matter what.

Be sure to specify option FIRE_TRIGGERS in your BULK INSERT command.

Give me a reason to use the new data type when I can't use date functions with it natively, or can't get DATEDIFF in particular to work with it...

I too don't think OUTER APPLY is the main problem.  But I would strongly urge you to get rid of the function against the table column you are searching on,...

Not much to go on, but, in general, just put the appropriate conditions in the WHERE clause:

DECLARE @key_value ...
DECLARE @col_value ...

SET @key_value = ...
SET @col_value = ...

UPDATE...

Keep in mind, too, that the point of naming is to give accurate business names to objects, not to make things easier for developers.  That is, just because a naming...

With stored procedures, I like a 2-3 letter acronym at the start to define the process (adm for admin, fin for finance, etc) followed by _ and then what...

Give your databases names that reflect their business purpose.  Don't use prefixes or suffixes in the name to indicate "database".  MS got this half right: MS has dbs "master", "model",...

Make sure the D_Date table is uniquely clustered on Date (it should already be, but verify, just in case).  I'd stick with a scalar function for now, but get rid...

Actually, for an even number of rows, the median is the avg of the middle two (IIRC).

For example, for values:

1, 2, 3, 101, 200, 500 :: median is 52 (...

Agreed, we need to see the code.

And, do you have a table with non-workdays in it?  We'll need to know the DDL for that table to properly determine business days.

That's not an accurate method to get a median value.

For example, if you have these values:

SELECT (MAX(value) - MIN(value)) / 2 + MIN(value)

FROM ( VALUES(1),(2),(3),(1000),(3000) ) AS data(value)

the median (middle) value...