Ahhh, ... ok... I try really hard NOT to use that kind of construction...

I can't think of any way to generate the extra record that duplicates information from what would be the "previous" record, and have it happen in a query. ...

I don't know the direct answer, but why place the ON clause for that LEFT JOIN in a position AFTER the CROSS APPLY unless you intended that CROSS APPLY to...

Further investigation by looking at the missing items has determined that it appears to be a data issue, as a rather larger "sub" routing is intentionally not part of the...

VSP (11/11/2014)

---

The following query calculates the SD and MEAN by customer (it doesn't include order_date).

select PO.customer_id

,PO.order_date

,row_number() over(partition by order_date order by order_date desc) as DATE_LINE

...

jbionic2010 (11/5/2014)

---

This clause aint correct. We are looking for amount Not Between stdev_amt - 3 * avg_amt And stdev_amt + 3...

Without more details of the overall query, it's rather difficult to know what will work vs. what won't. Obviously, you're not going to be able to have a...

Like2SQL (8/19/2014)

---

Thanks for the responses guys. But the reason I asked for some numbers is because, as mentioned by some on this thread, the recruiters often try to get me...

Yes, a million executions is a large number, but imagine trying to perform this manipulation in a data warehouse, where maybe you have billions of rows. That's...

I'm pretty sure I can't force anyone to do anything. I don't need to. Clearly, you'd rather complain than actually solve your problem. You...

And I have to wonder if your sample size is large enough. While the CPU time values for the string manipulation are less than half those of the...

Just a quick FYI to make everyone aware that the posted query doesn't work with the posted table inserts, and that you'll need to add a right paren to one...

Eirikur makes a good point. An hour designation that doesn't justify a 2nd digit will need a leading zero.

Here are the corrected code samples:

DECLARE @TV AS decimal(8,0) =...

The question is, what's the meaning of the last 4 characters. Yes, you could throw them away, but if they hold meaning, that's not usually a good idea....

Yep, that's a problem... Not much that's easy to be done about it, either. There's really only one way to get it done, and it's expensive...