It might be worth trying code below, as it seems to limit the number of "j" lookups required. A nested-loop join didn't prevent the extra lookups -- for whatever...

whenriksen (10/17/2016)

---

ScottPletcher (10/17/2016)

---

Rather unfair to cut off the part where I asked what specifically are your requirements, because you've never stated them clearly, and then to blast me for not...

whenriksen (10/17/2016)

---

ScottPletcher (10/14/2016)

---

If you just want a list of Sunday dates that are two weeks apart from a given starting date:

SELECT COUNT(*),

...

I posted code that did that type of calc already. What specifically are you trying to list?

If you...

whenriksen (10/14/2016)

---

You made me curious and its been a quiet day.

SELECT COUNT(*),

CAST(DATEADD(WEEK, DATEDIFF(WEEK, '20061231', DATEADD(DAY, 7

- CASE DATEPART(WEEKDAY, o.modify_date) WHEN...

Here's a simple, brute-force -- but still no looping -- method, if you prefer that.

SELECT

string AS original_string,

CASE WHEN are_enough_delims_present = 0 THEN...

Typically a single query works best in that case, since the ytd query will by definition include the other data. Not sure what your definition of "week" is, but...

whenriksen (10/14/2016)

---

ScottPletcher (10/14/2016)

---

Wow, that's a fascinating statement. Computers are known for doing billions of certain mathematical calcs per second (graphics chips hit trillions). Under no circumstances I know...

spaghettidba (10/14/2016)

Generally speaking, triggers are a very bad place to implement business logic, because they make the code difficult to debug/follow. They are some sort of "hidden" code that is...

The Dixie Flatline (10/13/2016)

Also, although I haven't done time trials, I have no trouble believing that sometimes a simple index seek can take less time than a complex calculation against...

whenriksen (10/13/2016)

---

ScottPletcher (10/13/2016)

---

I don't see how that would be the case. Couldn't you just use simple math to calc the pay periods, then join to the pre-calc'd dates? ...

You'll want to match directly on id, then you can use CHECKSUM across the other columns. Remember to always think set-based when possible, to get max benefits from SQL's processing.

SET...

whenriksen (10/13/2016)

---

Another benefit to using the calendar table rather than the Select DateAdd, performance is much better when used for filtering/grouping.

One request I received at my work was to see...

Do you want each individual date difference or do you just want an average days' difference between orders? The latter is much less work to calc :-).

Good article, but there's a much easier way to calc the nth given day of a given month. Also, btw, Thanksgiving is the fourth Thursday of the month not...