Wednesday, September 14, 2011

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.)
Original Table Sandeep
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',CONVERT(datetime,'2011-01-05'),10
insert @t select 90       ,'Third Agreement',CONVERT(datetime,'2011-04-16'),20
insert @t select 75       ,'Third Agreement',CONVERT(datetime,'2011-05-05'),10
insert @t select 90       ,'Third Agreement',CONVERT(datetime,'2011-02-20'),90
select * from @t





The scenario is to display Customer’s total bill based on the services availed to him according to Service Agreement and Date..

And Expect out put is as follow



Output Table Sandeep

For that I used the following query

;with cteTable(CustID,combined,inx,price)
as
(
select CustID,  AgreementName + ' ['+ CONVERT(varchar,ServDate,106)+']'                        
, inx=ROW_NUMBER() over (PARTITION by CustID order by AgreementName, ServDate),Price
from @t t
)
,cteConcate(CustID,finalstatus,inx,Price1)
as
(
select CustID, convert(varchar(max),combined), 1,(price) from cteTable where inx=1
union all
select cteConcate.CustID, convert(varchar(max),cteConcate.finalstatus+', '+cteTable.combined), cteConcate.inx+1, cteTable.price
from cteConcate
inner join cteTable on cteTable.CustID = cteConcate.CustID and cteTable.inx=cteConcate.inx+1

)
select CustID, MAX(finalstatus) Description ,sum(Price1) Price from cteConcate group by CustID






if have any query please let me know.



Happy Coding….