Power of common table expression (CTE)
Hello friends, Recently I came across a case where I was supposed to generate report for Customer based on services availed to him and for that I used A common table expression (CTE) and it really impressed. initially I referred this great document of MSDN http://msdn.microsoft.com/en-us/library/ms190766.aspx here is my Table of data.(Actual Tables are not like this, I have managed a simplified Table for the Demo.) Here is query to generate Table declare @t table (id int identity,CustID int, AgreementName varchar(100), ServDate Datetime,Price float) insert @t select 70 ,'First Agreement',CONVERT(datetime,'2011-01-05'),10 insert @t select 70 ,'Second Agreement',CONVERT(datetime,'2011-01-06'),10 insert @t select 75 ,'First Agreement',CONVERT(datetime,'2011-03-14'),10 insert @t select 70 ,'Second Agreement',CONVERT(datetime,'2011-01-05'),30 insert @t select 75 ,'Third Agreement',CO...