Using Conditional Aggregate in SQL Server 2008

Question

I had a query with too many select statements. Hence I used conditional aggregation. The query refers the data from Instock table in SQL Server 2008 and the final query looks like:

select 
    SUM(CASE WHEN MONTH = '2013-8-1' THEN CurrentStock ELSE 0 END) as CurrentStock,
    Isnull(Sum(CASE WHEN MONTH = '2014-2-1' THEN CPU ELSE 0 END), 0) - Isnull(Sum(CASE WHEN MONTH = '2013-8-1' THEN CPU ELSE 0 END), 0) as CPU,
    SUM(CASE WHEN Month = '2013-8-1' THEN NonCPUWIP ELSE 0 END) as NonCPU,
    Isnull(Sum(CASE WHEN MONTH = '2014-2-1' THEN ScrapDiscarded ELSE 0 END), 0) - Isnull(Sum(CASE WHEN MONTH = '2013-8-1' THEN ScrapDiscarded ELSE 0 END), 0) as Scrap,
    SUM(CASE WHEN Month = '2013-8-1' THEN WaitingForApproval ELSE 0 END) as WFA  
from 
    Instock
where 
    Vendor = 'abc'

The issue is the one last query which needs to be included. It gets the data from OutStock table but I want to include that query in the above one so that it returns result as single row. Im able to meet the requirement using nested query but I want the same result using conditional aggregation for database performance smoothness.

The query that needs to be included is:

select 
    COUNT(Product) as Despatched 
from 
    OutStock 
where 
    DispatchDate between '2013-8-1' and '2014-2-1' 
    and Vendor = 'abc'`

So combining both I want to execute as a single query which will return a single row

Answer 1

Just add the second query in the column list of first query since it is going to return only one row. Try this.

SELECT Sum(CASE
             WHEN MONTH = '2013-8-1' THEN CurrentStock
             ELSE 0
           END)                                 AS CurrentStock,
       Isnull(Sum(CASE WHEN MONTH = '2014-2-1' THEN CPU ELSE 0 END), 0) - 
       Isnull(Sum(CASE WHEN MONTH = '2013-8-1' THEN CPU ELSE 0 END), 0) AS CPU,
       Sum(CASE WHEN Month = '2013-8-1' THEN NonCPUWIP ELSE 0 END) AS NonCPU,
       Isnull(Sum(CASE WHEN MONTH = '2014-2-1' THEN ScrapDiscarded ELSE 0 END), 0) - 
       Isnull(Sum(CASE WHEN MONTH = '2013-8-1' THEN ScrapDiscarded ELSE 0 END), 0)AS Scrap,
       Sum(CASE WHEN Month = '2013-8-1' THEN WaitingForApproval ELSE 0 END) AS WFA,
       (SELECT Count(STBSerialNUMBER)
        FROM   OutStock
        WHERE  DispatchDate BETWEEN '2013-8-1' AND '2014-2-1'
                AND Vendor = 'abc' ) as Despatched
FROM   Instock I
WHERE  Vendor = 'abc'

Update :

If you want the result count of each model then convert the subquery it to correlated subquery. should be something like

  (SELECT Count(STBSerialNUMBER)
   FROM   OutStock O
   WHERE o.model = i.model  -- here is the change
   AND O.DispatchDate BETWEEN '2013-8-1' AND '2014-2-1'
   AND o.Vendor = 'abc')

Here for each model in Instock table the respective count in the outstock will be caluclated.

Or you can also use Left Join. First calculate the count of each model in Outstock table join the result back to the Instock table using the model

   ....
   From Instock I Left Join 
   (SELECT Count(STBSerialNUMBER) OutStock_count,Model
   FROM   OutStock O
   WHERE  O.DispatchDate BETWEEN '2013-8-1' AND '2014-2-1'
   AND o.Vendor = 'abc'
   Group by Model) O on o.model = i.model