How to refer to a previously computed value in SQL Query statement

I am trying to add a CASE statement to the end of my SQL query to compute a value depending upon another table value and a previously computed value in the SELECT. The error is returned that DelivCount is an invalid column name. Is there a better way to do this or am I missign something?

SELECT jd.FullJobNumber, jd.ProjectTitle, jd.ClientName, jd.JobManager, jd.ProjectDirector, jd.ServiceGroup, jd.Status, jd.HasDeliverables, jd.SchedOutsideJFlo, jd.ReqCompleteDate,(SELECT COUNT(*)FROM DeliverablesSchedule ds WHERE jd.FullJobNumber = ds.FullJobNumber) as DelivCount, SchedType = 
    CASE 
        WHEN (jd.SchedOutsideJFlo = 'Yes')
            THEN 'outside'
        WHEN (jd.HasDeliverables = 'No ')
            THEN 'none'
        WHEN (DelivCount > 0)
            THEN 'has'
        WHEN (jd.HasDeliverables = 'Yes' AND DelivCount开发者_StackOverflow社区 = 0)
            THEN 'missing'
        ELSE 'unknown'
    END
FROM JobDetail jd

---

try this

SELECT 
Z.*, 
SchedType = 
    CASE 
        WHEN (Z.SchedOutsideJFlo = 'Yes')
            THEN 'outside'
        WHEN (Z.HasDeliverables = 'No ')
            THEN 'none'
        WHEN (Z.DelivCount > 0)
            THEN 'has'
        WHEN (Z.HasDeliverables = 'Yes' AND Z.DelivCount = 0)
            THEN 'missing'
        ELSE 'unknown'
    END
FROM
(
        SELECT 
        jd.FullJobNumber, 
        jd.ProjectTitle, 
        jd.ClientName, 
        jd.JobManager, 
        jd.ProjectDirector, 
        jd.ServiceGroup, 
        jd.Status, 
        jd.HasDeliverables, 
        jd.SchedOutsideJFlo, 
        jd.ReqCompleteDate,
        (SELECT COUNT(*)FROM DeliverablesSchedule ds WHERE jd.FullJobNumber = ds.FullJobNumber) as DelivCount 
        FROM JobDetail jd
)
Z

---

try this, which should run a lot faster:

SELECT 
    jd.FullJobNumber, jd.ProjectTitle, jd.ClientName, jd.JobManager, jd.ProjectDirector, jd.ServiceGroup, jd.Status, jd.HasDeliverables, jd.SchedOutsideJFlo, jd.ReqCompleteDate
        ,ds.DelivCount
        ,SchedType =CASE 
                        WHEN (jd.SchedOutsideJFlo = 'Yes')
                            THEN 'outside'
                        WHEN (jd.HasDeliverables = 'No ')
                            THEN 'none'
                        WHEN (ds.DelivCount > 0)
                            THEN 'has'
                        WHEN (jd.HasDeliverables = 'Yes' AND ds.DelivCount = 0)
                            THEN 'missing'
                        ELSE 'unknown'
                    END
    FROM JobDetail jd
        LEFT OUTER JOIN (SELECT
                             FullJobNumber, COUNT(*) AS DelivCount
                             FROM DeliverablesSchedule
                             GROUP BY FullJobNumber
                        ) ds ON jd.FullJobNumber = ds.FullJobNumber

The original query uses a subquery:

A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed.

by the very nature of a sub query, it must be run repeatedly, once for each row. I have rewritten the query to use a derived table, which is evaluated one time to find all of the counts and is then joined to the proper rows. This allows for the DelivCount value to be referred to as any column would be when joined from another table, and should speed up this query.