Complicated? CTE's with Window (sometimes called Analytical) functions do the exact opposite. They un-complicate queries. It would be well worth your time to learn about ROW_NUMBER, RANK, NTILE, and a few others. "Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions" by Itzik Ben-Gan next to me on my desk.
I guess some of this functions are self taught as i have never come across them. Sometimes i wonder why in our university they only teach the basic SQL i.e INSERT,SELECT,UPDATE,DELETE and a few inner queries. I have studied the query you gave me and some how it makes sense, though it took me a while to get it. At the same time I'm modifying it to include what i want. The other thing, i have seen people using the HAVING function, i tried it but i got a few errors and will see if i can get it right. That book is worth having it and i will have to get it.
Unless you get into recursive CTE's just think of a CTE as a sub-query in the FROM clause (known as a derived table) except it's not in the FROM clause. It's still just a "derived table" or "inline view" just like a sub-query in a FROM clause.
After reading this comment i said to myself, wait then i looked at the CTE query above and it actually opened up my thoughts about inner queries and so forth.
One thing, on the CTE given above can i add a HAVING function at the END to say i only want to see those records where nr is greater than 2.
example
from the dp table
'1','SAV',80
'2','SAV',80
from the ln table
'1','IL',70
'1','MIC',89
'2','MIC',89
'3','MIC',89
result should be only those that have more than one record, here the '1','IL',70 will be excluded from the result.
SELECT
[nr],
[type],
[DT],
FROM lnCTE
WHERE acct_type1=1 AND acct_type2=1;
HAVING count(nr) >= 2