I have previously written many articles on CTE. One question I get often is how to use multiple CTE in one query or multiple CTE in SELECT statement. Let us see quickly two examples for the same. I had done my best to take simplest examples in this subject.
Option 1 :
/* Method 1 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT 2 AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO
Option 2:
/* Method 2 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT COL1+1 AS Col2 FROM CTE1)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO
Please note the difference between options. If there is any other option than above mentioned two options, please leave your comment here.
Reference : Pinal Dave (https://blog.sqlauthority.com)
73 Comments. Leave new
Do we have WITH clause in SQL Server 2008 R2
can i fire query like this in sql server 2008
Select * from emp;
please suggest me i am new to sql server 2008.
thank you again…
Use SELECT TOP 99.99999999999 PERCENT * FROM….. It worked for me…. It gives the same COUNT comparing with simple SELECT * FROM statement….
Looking all over for this answer and this comes close, how do I incorporate this CTE:
SELECT TarnScore.CGCODINGMASTEROID, TarnScore.Score
AS “TARN Score”
FROM (
SELECT DISTINCT ST2.CGCODINGMASTEROID,
(
SELECT SUM(ted.TARNSCORE)
FROM HealthBI.dbo.LZO_CGCODINGDETAIL AS ST1
LEFT join CommDB.dbo.tblTARNEDCodes as ted on ST1.DESCRIPTION = ted.DESCRIPTION
WHERE ST1.CGCODINGMASTEROID = ST2.CGCODINGMASTEROID
AND ted.[TARN Possible] ‘None’
—ORDER BY ST1.CGCODINGMASTEROID
) [Score]
FROM HealthBI.dbo.LZO_CGCODINGDETAIL ST2
) AS [TarnScore]
Into this SQL Code (note its the final bit where this look similar and the join would occur I think)
;WITH CTE
AS (
SELECT ed.AttendDateTime AS [ED Attend Date],
ip.HEYNo AS [HEY Number],
ip.NHSNo1 AS [NHS Number],
ip.Forename,
ip.Surname,
ip.DOB,
ed.ComplaintFreetext AS [Additional Comments],
ed.LOCAL_ACCIDENT_AND_EMERGENCY_DIAGNOSIS_DESCRIPTION,
ed.StreamDesc AS [Major/Minor/Paeds],
CASE WHEN ip.DateOfDeath IS NULL THEN ‘N’ ELSE ‘Y’ END AS [Died In Hospital],
ed.DepartDateTime,
ip.AdmitDateTime,
CASE WHEN ip.DischDateTime IS NULL THEN ‘N’ ELSE ‘Y’ END AS [Discharged?],
ip.DischDateTime AS [Discharge Date/Time],
ed.EDAttendanceOID,
ce.IDENTIFYINGOID,
ce.IDENTIFYINGTYPE,
cm.OID AS [CodingMasterOID],
e.OID AS [EncounterOID]
FROM dbo.ED_ATTENDANCE AS ed
LEFT JOIN dbo.IP_ADMISSION AS ip ON ed.MFPatientID = ip.MFPatientID
INNER JOIN HealthBI.dbo.LZO_AEATTENDANCE AS lae ON ed.EDAttendanceOID = lae.OID
INNER JOIN HealthBI.dbo.LZO_ENCOUNTER AS e ON lae.ENCOUNTEROID = e.OID
INNER JOIN HealthBI.dbo.LZO_PATIENTCAREEVENTS AS ce ON e.OID = ce.ENCOUNTEROID
AND ce.IDENTIFYINGTYPE = ‘Create Attendence’
LEFT JOIN HealthBI.dbo.LZO_CGCODINGMASTER AS cm ON ce.IDENTIFYINGOID = cm.IDENTIFYINGOID
AND ce.IDENTIFYINGTYPE = cm.IDENTIFYINGTYPE
—- ok to here
WHERE ed.AttendDate >= ’16 january 2016′
AND ed.AttendDate < '17 january 2016'
AND (DATEDIFF(HOUR, ed.DepartDateTime, ip.AdmitDateTime) BETWEEN – 5 AND 5)
AND (ed.HospitalCode = 'RWA01')
)
—————————————-
— below concatinates multiple rows/fields into one long string
SELECT Main.CGCODINGMASTEROID,
Left(Main.DESCRIPTIONS, Len(Main.DESCRIPTIONS) – 1) AS "ED Descriptions",
c.*
FROM (SELECT DISTINCT ST2.CGCODINGMASTEROID,
(SELECT ST1.DESCRIPTION + ', ' AS [text()]
FROM HealthBI.dbo.LZO_CGCODINGDETAIL AS ST1
LEFT join CommDB.dbo.tblTARNEDCodes as ted on ST1.DESCRIPTION = ted.DESCRIPTION
WHERE ST1.CGCODINGMASTEROID = ST2.CGCODINGMASTEROID
AND ted.[TARN Possible] ‘None’
ORDER BY ST1.CGCODINGMASTEROID
FOR XML PATH(”)
) [DESCRIPTIONS]
FROM HealthBI.dbo.LZO_CGCODINGDETAIL ST2
) AS [Main]
RIGHT JOIN CTE AS c ON Main.CGCODINGMASTEROID = c.CodingMasterOID
I need the SQLserver 2005 program or query for the below mentioned table (date diff)
SEQ1 PRCS_DT SEQ2
1 2012-07-22 0
2 2012-07-23 1
3 2012-07-24 1
4 2012-07-26 2
5 2012-07-29 3
I have a copy of it
How do I convert a string to number in a store procedure that uses an open query
You use a function to convert the string to a number