1 DECLARE @Totalapp int,
2 @10days int;
3 SELECT @Totalapp = Count(0) from dbo.company;
4 WITH
5 CTE_ApplicationStatus AS (
6 SELECT
7 ROW_NUMBER() OVER (PARTITION BY a.Id ORDER BY a.Id, asl.DateCreated ASC) Row#,
8 asl.ApplicationStatus,
9 a.Id,
10 asl.DateCreated
11 FROM
12 mcf.[Application] a
13 INNER JOIN
14 mcf.ApplicationStatusLog asl ON asl.ApplicationId = a.Id
15 WHERE
16 asl.Id IS NOT NULL
17 )
18 SELECT @10days = COUNT(0) FROM CTE_ApplicationStatus
19
20 SELECT
21 @TotalApp/@10days * 100 as conversion,
22 DATEDIFF(DAY,sc.DateCreated ,sc2.DateCreated) DaysElapsed
23
24
25 FROM
26 CTE_ApplicationStatus sc
27 LEFT OUTER JOIN
28 CTE_ApplicationStatus sc2
29 ON
30 sc2.Id = sc.Id AND sc2.Row# = sc.Row# + 1
invalid object name for 'CTE_ApplicationStatus' on line 20? I am trying to get the count of the first CTE and then use it in the select statement on line 20 but i seem to be getting an error message
