March 31, 2015 at 3:14 pm
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
March 31, 2015 at 3:22 pm
"CTEs cannot be nested" Are you sure? http://stackoverflow.com/questions/7306191/how-to-nest-cte-properly
and what's with all those NOLOCK hints?
https://www.simple-talk.com/sql/database-administration/grant-fritcheys-sql-server-howlers/
March 31, 2015 at 4:59 pm
A common table expression is just a derived table (except when we're talking about recursion). So you can just treat it as another table in the query:
WITH myCTE AS
(SELECT * FROM SomeTable)
SELECT * FROM myCTE;
is the same as
SELECT * FROM
(SELECT * FROM SomeTable) AS NotMyCTE;
So you can just move it. Just understand, that the magic of a CTE is that it can be referred to more than once in the following query. Standard derived tables cannot.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2015 at 9:33 am
CTEs can be nested, they just all have to be defined at the same time
with cte1 as(select name From sys.databases),
cte2 as(select name from cte1),
cte3 as(select name from cte2)
select * from cte3
If i understand your requirements correctly I think this will cover it.
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)
),
ABSResults as(
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
)
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply