A common table expression (CTE) can be thought of as a temporary result
set that is defined within the execution scope of a single SELECT,
INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a
derived table in that it is not stored as an object and lasts only for
the duration of the query. Unlike a derived table, a CTE can be
self-referencing and can be referenced multiple times in the same query.
e.g.-
;with SelectCta(Bundle_No, User_Id, AllotDate, UserName, NoofBills,Net_Amt, Claim_Amt,Status,Alloted_Date,BundleNoText,AllotedUserId, TPA, billdated, TPAID) as
(
SELECT DISTINCT
IE_BundleNo.Bundle_No, IE_BundleNo.User_Id, CONVERT(varchar, IE_BundleNo.Alloted_Date, 103) AS AllotDate, UserLogin.UserName, IE_BundleNo.NoofBills,
IE_BundleNo.Net_Amt, IE_BundleNo.Claim_Amt, IE_BundleNo.Status, IE_BundleNo.Alloted_Date, IE_BundleNo.BundleNoText, IE_BillAllotStatus.AllotedUserId,
(select DISTINCT InsurerComName from IE_Master_InsuranceCompany where IE_Master_InsuranceCompany.InsurerComID=IE_BillAllotStatus.TPAID) AS TPA,
'From ' + CONVERT(varchar, IE_BillAllotStatus.BatchDateFrom, 103) + ' To ' + CONVERT(varchar, IE_BillAllotStatus.BatchDateTo, 103) AS billdated,
IE_BillAllotStatus.TPAID
FROM IE_BundleNo INNER JOIN
UserLogin ON IE_BundleNo.User_Id = UserLogin.UserID INNER JOIN
IE_BillAllotStatus ON IE_BundleNo.BundleNoText = IE_BillAllotStatus.BundleNoText
)
select Bundle_No, User_Id, AllotDate, UserName, NoofBills,Net_Amt, Claim_Amt,Status,Alloted_Date,BundleNoText,AllotedUserId, TPA, billdated, TPAID,
dbo.Get_Insurencecompany(TPAID,Bundle_No ) as InsCompanyName
from SelectCta