Konvertieren Sie hässliche T SQL-Abfrage zu CTE

Ich habe Schwierigkeiten, meine TSQL-Abfrage zu einem cte zu machen. Ich bin mir ziemlich sicher, dass es ein Kandidat dafür ist. Ich habe cte's in anderen Abfragen verwendet, aber diese muss data aus mehreren Quellen zu bekommen und das verursacht mir einige Trauer. Hier ist was ich das habe

declare @projid int = 0 declare @proj varchar = (select projectid from Projects where projectid = @projid) declare @maxid int = (select max(projectid) from Projects) declare @projsize bigint = (select projectsizeexpitem from Projects where projectid = @projid) declare @numofdays int = (select DATEDIFF(DAY, MIN(starttime), GETDATE()) from transactions where projectid = @projid) declare @dailyrate bigint = (select (SUM(transactionitemsmigrated)/@numofdays) from Transactions where projectid = @projid) declare @complete bigint = (select SUM(transactionitemsmigrated) from transactions where projectid = @projid) declare @daysremaining int = (select (CAST(GETDATE() AS int) + ((@projsize-@complete)/@dailyrate))) WHILE @projid < @maxid BEGIN IF @projid IN (select projectid from projects) BEGIN set @proj = (select projectname from Projects where projectid = @projid) set @maxid = (select max(projectid) from Projects) set @projsize = (select projectsizeexpitem from Projects where projectid = @projid) set @numofdays = (select DATEDIFF(DAY, MIN(starttime), GETDATE()) from transactions where projectid = @projid) set @dailyrate = (select (SUM(transactionitemsmigrated)/@numofdays) from Transactions where projectid = @projid) set @complete = (select SUM(transactionitemsmigrated) from transactions where projectid = @projid) set @daysremaining = (select (CAST(GETDATE() AS int) + ((@projsize-@complete)/@dailyrate))) select [Project] = (select projectname from projects where projectid = @projid), [TotalItems] = @projsize, [DaysActive] = @numofdays, [DailyRate] = @dailyrate, [TotalComplete] = @complete, [ItemsRemaining] = @projsize - @complete, [DaysRemaining] = ((@projsize-@complete)/@dailyrate), [CompDate] = CAST(CAST(@daysremaining AS datetime) AS date) SET @projid = @projid + 1 END ELSE BEGIN SET @projid = @projid + 1 END END 

Das Problem mit diesem ist, dass es jede Iteration der SELECT-statement als separate Ergebnis-Tabelle zurückgibt. Ich möchte sie alle zusammen für die Aggregation haben. Hier habe ich versucht:

 declare @projid int = 0 declare @proj varchar = (select projectid from Projects where projectid = @projid) declare @maxid int = (select max(projectid) from Projects) declare @projsize bigint = (select projectsizeexpitem from Projects where projectid = @projid) declare @numofdays int = (select DATEDIFF(DAY, MIN(starttime), GETDATE()) from transactions where projectid = @projid) declare @dailyrate bigint = (select (SUM(transactionitemsmigrated)/@numofdays) from Transactions where projectid = @projid) declare @complete bigint = (select SUM(transactionitemsmigrated) from transactions where projectid = @projid) declare @daysremaining int = (select (CAST(GETDATE() AS int) + ((@projsize-@complete)/@dailyrate))) ;WITH cte AS ( select [ID] = 1, [Project] = (select projectname from projects where projectid = @projid), [TotalItems] = @projsize, [DaysActive] = @numofdays, [DailyRate] = @dailyrate, [TotalComplete] = @complete, [ItemsRemaining] = @projsize - @complete, [DaysRemaining] = ((@projsize-@complete)/@dailyrate), [CompDate] = CAST(CAST(@daysremaining AS datetime) AS date) UNION ALL select [ID] + 1, [Project] = (select projectname from projects where projectid = @projid), [TotalItems] = @projsize, [DaysActive] = @numofdays, [DailyRate] = @dailyrate, [TotalComplete] = @complete, [ItemsRemaining]= @projsize - @complete, [DaysRemaining] = ((@projsize-@complete)/@dailyrate), [CompDate] = CAST(CAST(@daysremaining AS datetime) AS date) from cte where [ID] < @maxid ) select * from cte where @projid <= @maxid OPTION (MAXRECURSION 100) 

BEARBEITEN: Hast du es. Hier ist was ich benutzt habe. Vielen Dank an @ Amit-Sukralia für seinen Kommentar, der mich zur Lösung gebracht hat!

 SELECT [Project] = projectName, [TotalItems] = projectsizeexpitem, [DaysActive] = (select DATEDIFF(DAY, MIN(starttime), GETDATE()) from transactions where projectid = p.projectid), [DailyRate] = (select (SUM(transactionitemsmigrated)/(DATEDIFF(DAY, MIN(starttime), GETDATE()) )) from Transactions where projectid = p.projectid), [TotalComplete] = (select SUM(transactionitemsmigrated) from transactions where projectid = p.projectid), [ItemsRemaining] = projectsizeexpitem - (select SUM(transactionitemsmigrated) from transactions where projectid = p.projectid), [ExpectedCompDate] = CAST((GETDATE() + ((p.projectsizeexpitem-(select SUM(transactionitemsmigrated) from transactions where projectid = p.projectid))/ (select (SUM(transactionitemsmigrated)/(DATEDIFF(DAY, MIN(starttime), GETDATE()) )) from Transactions where projectid = p.projectid))) as int) - CAST(GETDATE() as int) FROM Projects p 

Du kannst so etwas wie unten ausprobieren. Es braucht kein CTE:

 SELECT ROW_NUMBER() OVER (PARTITION BY projectid) AS ID, projectName AS [Project], (select max(projectid) from Projects) AS MaxId, projectsizeexpitem AS [TotalItems], (select DATEDIFF(DAY, MIN(starttime), GETDATE()) from transactions where projectid = p.projectid) AS [DaysActive], (select (SUM(transactionitemsmigrated)/(DATEDIFF(DAY, MIN(starttime), GETDATE()) )) from Transactions where projectid = p.projectid) AS [DailyRate], (select SUM(transactionitemsmigrated) from transactions where projectid = p.projectid) AS [TotalComplete], projectsizeexpitem - (select SUM(transactionitemsmigrated) from transactions where projectid = p.projectid) AS [ItemsRemaining], (select (CAST(GETDATE() AS int) + ((projectsizeexpitem - (select SUM(transactionitemsmigrated) from transactions where projectid = p.projectid))/ (select (SUM(transactionitemsmigrated)/(DATEDIFF(DAY, MIN(starttime), GETDATE()) )) from Transactions where projectid = p.projectid)))) AS [DaysRemaining] from Projects p