shani19831 (8/6/2013)
Thanks for the query, its returning what i want, but i have never used CTE and have no knowledge of it, guess its time to learn.is there no easy way of doing it instead of the way you did it cause its so complicated.
How easy is it, say i want to join other tabes to ln and dp tables in order to display other information?
The reason is, i need extra information but its hosted in other different tables that i normally join to ln or dp.
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. Then look at examples on how to use them in CTE's. Once you understand their power, you will see opportunities to use them again and again. I keep a copy of "Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions" by Itzik Ben-Gan next to me on my desk. It is NOT on the shelf gathering dust.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/