t-sql 2012 change logic to not be a cte

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

t-sql 2012 change logic to not be a cte

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2015-03-31 : 17:21:30

I would like to know how to change the logic for a cte in t-sql 2012 to not be a cte. The reason is I want to use the results from qry1 to update the Atrn.ABS value as show below:

with ABSResults as (qry1)
update Atrn
set ABS = ABSResults.ABS
from dbo.Atrn Atrn
Join ABSResults on Atrn.AtrnId = ABSResults.AtrnId
Where Atrn.ABS <> ABSResults.ABS or Atrn.ABS is null

I would like to change the following logic to not be a CTE since CTEs can not be nested. Thus would you show me how to change the following logic to not be a CTE so that I can use the results of the following in query1 in the update statement listed above?

WITH Daily_CTE AS
(SELECT Per.perID, Att.[date],
COALESCE(Att.code, 'UNV') AS code, DATEDIFF([minute], Per.startTime, Per.endTime) - COALESCE(Per.lunchTime, 0) - COALESCE
(Att.presentMinutes, 0) AS absentMinutes,
COALESCE(GrLevel.standardDay, Cal.sDay, 360) AS dayMinutes
FROM (SELECT Attance.calID, Attance.perID, Attance.PerID, Attance.[date],
Attance.presentMinutes, AttancE.code
FROM Attance AS Attance WITH (NOLOCK)
LEFT OUTER JOIN AttancE AS AttancE WITH (NOLOCK)
ON AttancE.excuseID = Attance.excuseID
AND AttancE.calID = Attance.calID
WHERE COALESCE(AttancE.[status], Attance.[status]) = 'A'
AND COALESCE(AttancE.code, 'UNV') IN ('156','TRU','UNV','567','LEG','ILL','GOT','PSP','SSS')) AS Att
INNER JOIN Cal AS Cal WITH (NOLOCK)
ON Cal.calID = Att.calID
INNER JOIN School AS School WITH (NOLOCK)
ON School.schID = Cal.schID
INNER JOIN SchelS AS SchelS WITH (NOLOCK)
ON SchelS.calID = Att.calID
INNER JOIN TermS AS TermS WITH (NOLOCK)
ON TermS.strID = SchelS.strID
INNER JOIN Term AS Term WITH (NOLOCK)
ON TermS.TermSID = Term.TermSID
AND Term.startDate <= Att.[date]
AND Term.endDate >= Att.[date]
INNER JOIN PerS AS PerS WITH (NOLOCK)
ON PerS.strID = SchelS.strID
INNER JOIN Per AS Per WITH (NOLOCK)
ON Per.PerSID = PerS.PerSID
AND Att.PerID = Per.PerID
INNER JOIN Enr AS Enr WITH (NOLOCK)
ON Att.calID = Enr.calID
AND Enr.perID = Att.perID
AND Att.[date] >= Enr.startDate
AND (Att.[date] <= Enr.endDate OR Enr.endDate IS NULL)
INNER JOIN GrLevel AS GrLevel WITH (NOLOCK)
ON GrLevel.name = Enr.grade
AND GrLevel.calID = Enr.calID
AND GrLevel.strID = Enr.strID
INNER JOIN Per AS Per WITH (NOLOCK)
ON Per.perID = Att.perID
INNER JOIN [Iden] AS Ident WITH (NOLOCK)
ON Ident.IdenID = Per.currentIdenID
AND Ident.perID = Per.perID
INNER JOIN AtnDet AS AtnDet WITH (NOLOCK)
ON CAST(AtnDet.STULINK AS int) = Per.perID
AND Ident.IdenID = Per.currentIdenID
AND Ident.perID = Per.perID
AND CAST(AtnDet.ABSDT AS datetime) = [date]
WHERE TermS.[primary] = 1
AND (Per.nonIns IS NULL OR Per.nonIns = 0)
AND Cal.endYear = (select endYear from SYear where active = 1)
)

SELECT perID, [date],

CAST(CASE
WHEN code IN ('GOT','SSS','UNV') THEN CASE
WHEN SUM(absentMinutes) / dayMinutes > 1 THEN 1
ELSE SUM(absentMinutes) / dayMinutes
END
ELSE 0
END AS DECIMAL(8,3)) UnDays

FROM Daily_CTE
GROUP BY perID, [date], code, dayMinutes
ORDER BY perID, [date] DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources