better sql counting the number of items with a where clause

I have this query and I know there is a better way to write it. Here is the query which counts the orders to find out what is left in Inventory.

DECLARE @reserveDate as Datetime = '10/5/2011 10:20'

SELECT p.Name 
     , p.Quantity
     , (SELECT COUNT(*) 
          FROM [Order] o 
         WHERE o.ProductId = p.Id 
           AND o.Completed = 1) as Completed 
     , (SELECT COUNT(*) 
          FROM [Order] o 
         WHERE o.ProductId = p.Id 
           AND o.Completed <> 1 
           AND o.ModifiedDate >= @reserveDate) as Reserved 
     , (SELECT COUNT(*) 
          FROM [Order] o 
         WHERE o.ProductId = p.Id 
           AND o.Completed <> 1 
           AND o.ModifiedDate < @reserveDate) as ReserveExpired  
   --, (Quantity - Completed - Reserved) as Available
  FROM Product p 

====================================

here is a script for the tables

IF EXISTS ( SELECT  * FROM  sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Order]') AND type IN ( N'U' ) )    DROP TABLE [Order]
IF EXISTS ( SELECT  * FROM  sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type IN ( N'U' ) )  DROP TABLE [Product]

-- Product --
PRINT N' [Product]  ' 
CREATE TABLE Product
    (
      [Id] INT NOT NULL IDENTITY PRIMARY KEY,
      [Name] NVARCHAR(50) ,
      [Quantity] INT ,
    ); 
SET IDENTITY_INSERT Product ON 
INSERT  INTO Product ( [Id] , [Name] ,  [Quantity]) VALUES  ( '1', 'Tea Package',  7000 )
INSERT  INTO Product ( [Id] , [Name] ,  [Quantity]) VALUES  ( '2', 'Sugar Package',  8000)
SET IDENTITY_INSERT Product OFF 



-- Order --
PRINT N' [Order]'

CREATE TABLE [Order]
    (
      [Id] INT NOT NULL IDENTITY PRIMARY KEY ,
      [ProductId] INT   ,
      [Completed] Bit,
      [ModifiedDate] DATETIME 
    ); 
ALTER TABLE [Order] ADD CONSTRAINT FK_Product_Order FOREIGN KEY (ProductId) REFERENCES [Product] (Id) 
GO  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 1,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 1,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 0,  开发者_StackOverflow中文版 '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 0,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 0,   '10/5/2011 11:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 0,   '10/6/2011 11:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 0,   '10/6/2011 11:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 2, 1,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 2, 1,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 2, 0,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 2, 0,   '10/6/2011 10:10'  )  

---

You can replace the subqueries using:

   SELECT p.Name 
        , p.Quantity
        , SUM(CASE WHEN o.completed = 1 THEN 1 ELSE 0 END) AS Completed 
        , SUM(CASE WHEN o.completed <> 1 AND o.ModifiedDate >= @reserveDate THEN 1 ELSE 0 END) AS Reserved 
        , SUM(CASE WHEN o.completed <> 1 AND o.ModifiedDate < @reserveDate THEN 1 ELSE 0 END) AS ReserveExpired  
        , p.Quantity - 
          SUM(CASE WHEN o.completed = 1 THEN 1 ELSE 0 END) -
          SUM(CASE WHEN o.completed <> 1 AND o.ModifiedDate >= @reserveDate THEN 1 ELSE 0 END) AS available
     FROM Product p 
LEFT JOIN ORDER o ON o.productid = p.id
 GROUP BY p.Name, p.Quantity

Alternately, the following is equivalent & easier to read:

SELECT x.name, 
       x.quantity,
       x.completed,
       x.reserved,
       x.reserveexpired,
       x.quantity - x.completed - x.reserved AS available
  FROM (SELECT p.Name 
             , p.Quantity
             , SUM(CASE WHEN o.completed = 1 THEN 1 ELSE 0 END) AS Completed 
             , SUM(CASE WHEN o.completed <> 1 AND o.ModifiedDate >= @reserveDate THEN 1 ELSE 0 END) AS Reserved 
             , SUM(CASE WHEN o.completed <> 1 AND o.ModifiedDate < @reserveDate THEN 1 ELSE 0 END) AS ReserveExpired  
          FROM Product p 
     LEFT JOIN ORDER o ON o.productid = p.id
      GROUP BY p.Name, p.Quantity) x

---

You can subtract items within T-SQL itself, something like:

SELECT A - B AS C FROM TABLE WHERE ID=1

Although I'm not sure the best way to do that with your sub queries. I also don't know if you need to keep the actual Quantity, Completed, Reserved, & RerservedExpired values in your programming or if you just need those to calculate the Available Qty. If you need them outside SQL, then the subtraction inside won't help any.

---

You could use CROSS APPLY/OUTER APPLY operators:

CREATE INDEX aaa
ON [Order](ProductId)
INCLUDE (Completed,ModifiedDate);

PRINT '***** Sol1 *****'
SELECT 
     p.Name 
    ,p.Quantity
    ,ISNULL(ca.Completed,0) Completed
    ,ISNULL(ca.Reserved,0) Reserved
    ,ISNULL(ca.ReserveExpired,0) ReserveExpired
    ,p.Quantity - ISNULL(ca.Completed,0) - ISNULL(ca.Reserved,0) Available
FROM Product p 
OUTER APPLY
(
    SELECT 
         SUM(CASE WHEN o.Completed = 1 THEN 1 ELSE 0 END) Completed
        ,SUM(CASE WHEN o.Completed <> 1 AND o.ModifiedDate >= @reserveDate THEN 1 ELSE 0 END) Reserved
        ,SUM(CASE WHEN o.Completed <> 1 AND o.ModifiedDate < @reserveDate THEN 1 ELSE 0 END) ReserveExpired
    FROM [Order] o --WITH(FORCESEEK) or WITH(INDEX=aaa)
    WHERE o.ProductId = p.Id 
) ca;

PRINT '***** Sol2 *****'
SELECT 
     p.Name 
    ,p.Quantity
    ,ISNULL(q.Completed,0) Completed
    ,ISNULL(q.Reserved,0) Reserved
    ,ISNULL(q.ReserveExpired,0) ReserveExpired
    ,p.Quantity - ISNULL(q.Completed,0) - ISNULL(q.Reserved,0) Available
FROM    Product p
LEFT MERGE JOIN --or LEFT JOIN
(
    SELECT   o.ProductId
            ,SUM(CASE WHEN o.Completed = 1 THEN 1 ELSE 0 END) Completed
            ,SUM(CASE WHEN o.Completed <> 1 AND o.ModifiedDate >= @reserveDate THEN 1 ELSE 0 END) Reserved
            ,SUM(CASE WHEN o.Completed <> 1 AND o.ModifiedDate < @reserveDate THEN 1 ELSE 0 END) ReserveExpired     
    FROM    [Order] o 
    GROUP BY o.ProductId
) q ON p.Id = q.ProductId;