t-sql 2012 change logic to not be a cte

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
        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 )
     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 
     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 31st, 2015 5:29pm

Option 1: A CTE is a like a dynamic virtual table. Theoretically you an always replace CTE with a real temporary table (create a temp table -> insert the same SET that you get in the CTE -> use the temp table in the next query), but in most cases this will be bad idea. The CTE is not a real table and behind the scenes once the SQL Server query engine "read" the query, it will parse the text and create one execution plan. To get a good enough execution plan the Server query engine might change the order of the steps that we wrote in the query. The actual execution might be different from one database to another.

But using a two step query, which you first use temp table and then use it, you limit the execution plan to use these steps (sometimes it is good, but most of the time it will not).

Option 2: You can use sub query instead of CTE. A subquery is an inline code, which mean it will parse as part of the external query.

Option 3: In some cases you can replace CTE with inline table-valued functions.

* Those are general options. I could not test your code without DDL+DML so you will need to check if one of those fit our needs

** CTE can be recursive and maybe that will be the solution that you need. I did not understand where you needed nested option in your

Free Windows Admin Tool Kit Click here and download it now
March 31st, 2015 6:31pm

Not sure what you mean by "nested" but a CTE can be joined to other table and can be referenced when updating other tables.

The following is just a trivial sample but it demonstrates the idea.

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
GO
CREATE TABLE #temp (
	SomeValue INT NOT NULL,
	AnotherValue INT NULL 
	)
INSERT #temp (SomeValue) VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9)

SELECT * FROM #temp t

;WITH CTE AS (
	SELECT 
		t.SomeValue,
		ROW_NUMBER() OVER (ORDER BY t.SomeValue DESC) AS RevRN
	FROM #temp t
)
	UPDATE t SET AnotherValue = cte.RevRN
	FROM #temp t
	JOIN CTE
		ON t.SomeValue = CTE.SomeValue

SELECT * FROM #temp t

HTH,

Jason

March 31st, 2015 9:05pm
>> I would like to know how to change the logic for a CTE in T-SQL 2012 to not be a CTE. <<

There is no logic in a CTE; There is only data. Duh! Table Expression?? 

>> I would like to change the following logic to not be a CTE since CTEs can not be nested.<<

Where did you get this silly idea?? the CTEs come into existence in the scope in the order they are declared. I will guess that want to replace proceural code with a table?? 

You have been posting crap code and need to stop until you can learn enough to ask a question. Your stuff is full of ISO-11179 violations, camelCase, reserved words, and other errors. 

A forum is not a good place to learn basics. 
Free Windows Admin Tool Kit Click here and download it now
March 31st, 2015 10:08pm

Thank you for your answer so far! Can you should me another way to just use the sql I listed above to use only 1 update statement if that is possible? The following is what I mean by an nested cte:

Here is what I mean by a nested cte. This cte does not work since it has one cte embedded with another CTE. This sql would not work correct? Can you show me how to change the sql listed below?

with ABSResults as
(
 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
         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 )
      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  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
      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
)
update AtnDet
 set ABS = ABSResults.UnDays
 from dbo.AtnDet Atrn
 Join ABSResults on Atrn.perID = ABSResults.perID
  and Atrn.[date] = ABSResults.[date]
 Where Atrn.ABS <> ABSResults.UnDays or Atrn.ABS is null

March 31st, 2015 11:02pm

Here is what I mean by a nested cte. This cte does not work since it has one cte embedded with another CTE. This sql would not work correct? Can you show me how to change the sql listed below?

with ABSResults as
(
 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
         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 )
      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  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
      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
)
update AtnDet
 set ABS = ABSResults.UnDays
 from dbo.AtnDet Atrn
 Join ABSResults on Atrn.perID = ABSResults.perID
  and Atrn.[date] = ABSResults.[date]
 Where Atrn.ABS <> ABSResults.UnDays or Atrn.ABS is null

Free Windows Admin Tool Kit Click here and download it now
March 31st, 2015 11:16pm

CTEs don't "nest" like that... The syntax looks more like this...

 ;WITH Daily_CTE AS (
	>>Your SELECT big query here<<
  ), 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 Atrn.ABS = ABSResults.UnDays
FROM
	dbo.AtnDet Atrn
	JOIN ABSResults
		ON Atrn.perID = ABSResults.perID AND Atrn.[date] = ABSResults.[date]
WHERE
	Atrn.ABS <> ABSResults.UnDays OR Atrn.ABS IS NULL

March 31st, 2015 11:33pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics