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;