Feels counterintuitive, to see ram hovering at 100% 24/7 on production server and think that it increases performace.

I'd like to have atleast 10% spare for other OS...

Ty!

Both solutions worked great. Also nice to see different approaches to same problem.

Nice! It seems indeed to be slighty faster, the executaion plan shows query cost of my old query being 58% and CTE query 42%...

And statistics info old query vs...

Sorry, edited.

Oh sorry there was some scope creep... users have a registered field, that needs to be included in every where clause, so the current CTE solution wont work.

SET STATISTICS IO...

@Recurs1on & @sean Lange

That helped ty very much 🙂

Hmm not really.

I got this query..

Select a.a from a LEFT JOIN b ON a.a=b.b WHERE b.b IS NULL

Is it possible to get same results with

Select a.a from a LEFT JOIN...

Ah ty!

Lets see if i can apply it... have alot of inner join in the real query...

Conclusion: It worked with my query.. ty!

After some...

If there's no way for dynamical months, then 30 days/month.

declare @date1 date = '2010-05-17'

declare @date2 date = '2013-01-16'

declare @years int

declare @months int

declare @days int

select @years = DATEDIFF(YEAR,@date1,@date2) - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end

select @months...

select [Years] = datediff(year,0,ET-ST)-1,

[Months] = datepart(month,ET-ST)-1,

...

Running on production server, but yes, alot less reads.

The changes you did made a huge impact on reads in your query.

========== Jim-720070 version ==========

Warning: Null value is eliminated by an aggregate or other SET operation.

(3336...

Here's the result with real data:

========== Jim-720070 version ==========

Warning: Null value is eliminated by an aggregate or other SET operation.

(3336 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical...

Oh one more thing!

I compared both queries to view the performance, and realized that Cadavres query return 1/4 more results then Jims...

Checking the results it showed that Cadavres does...