Sql-server – reason the CTE with a basic subquery in the second stage of the CTE is failing

ctesql server

Ok… anyone aid with this sorry…i've been playing with some of my works reports and i thought id found a way to mix two results into one when i came across a small issue…

if i run my CTE code below without the lower subquery it runs straight away…

WITH CTE_RealAudit (UserName,WorkstationName,LoginDate,LoginTime,"Days","Hours","Minutes","Seconds")
AS
(
SELECT UserName AS 'User',WorkstationName AS 'Workstation',
RIGHT('0'+CONVERT(varchar(2),DATEPART(dd, DATEADD(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+'/'+RIGHT('0'+CONVERT(varchar(2),DATEPART(m,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+'/'+CONVERT(varchar(4),DATEPART(yy,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))) AS 'Login Date',
RIGHT('0'+CONVERT(varchar(2),DATEPART(hh,DATEADD(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+':'+RIGHT('0'+CONVERT(varchar(2),DATEPART(mi,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+':'+RIGHT('0'+CONVERT(varchar(2),DATEPART(ss,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2) AS "Login Time",
CONVERT(varchar(2), SUM(EndTime-StartTime)/(3600*24)) AS 'Days',
CONVERT(varchar(2), SUM(EndTime-StartTime)/3600) AS 'Hours',
CONVERT(varchar(2), (SUM(EndTime-StartTime) % 3600) / 60) AS 'Minutes',
CONVERT(varchar(2), (SUM(EndTime-StartTime) % 60 )) AS 'Seconds'
    FROM IMuse.vAuditUsageAndNames
        WHERE StartTime BETWEEN DATEDIFF(ss,'19700101',DATEADD(dd,-3000,CONVERT(date,SYSDATETIME()))) 
        AND DATEDIFF(ss,'19700101',CONVERT(date,SYSDATETIME())) 
        AND LEFT(UserName,2) <> N'P-'
        AND LEFT(UserName,3) <> N'EP-'
        AND EndTime <> StartTime
    GROUP BY UserName,WorkstationName,StartTime
)

SELECT * FROM CTE_RealAudit
    WHERE "Hours" <> N'0' 
    AND "Minutes" <> N'0

but if i add a subquery to the last select such as 

SELECT * FROM CTE_RealAudit
    WHERE "Hours" <> N'0' 
    AND "Minutes" IN    
    (select Minutes from cte_realaudit)

or my goal

SELECT * FROM CTE_RealAudit
    WHERE "Hours" <> N'0' 
    AND "Minutes" IN    
    (SELECT "Minutes" from CTE_RealAudit
        where "Hours" >= N'0'
        and "Minutes" >= N'0')

it completely freaks out and takes 20+ minutes? anyone aid in why this is occurring?

newbie here so sorry if im doing something totally stupid.

thanks

Best Answer

Let's pretend for a moment that CTE_RealAudit is a normal table...

You're querying it twice! Once to get the list of Minutes values, and to get all the other fields for those rows that have a Minutes value (oh, and surely it always will have, unless you have NULLs) in the list. So yes, that's going to take a lot longer than querying it once.

So that's why it's taking much longer. But I don't think you need to query it twice at all - have a good think about the logic you actually want, especially as it's not a normal table, but rather a complex subquery over a view.

Related Question