CODEWITHSUNDEEP
sqlsql-serverdateselectwhere-clause
Delicate Hiba
Delicate Hiba

Reputation: 63

How to apply WHERE clause to multiple SELECT statements in SQL Server

I am creating an query that selects data from multiple tables. I have completed all the query but now I have to apply the WHERE clause to the whole query. I have 9 select statements, and these are working fine. Data is being selected from different tables. Now I want to declare date session and I want all data to be filtered according to the date provided. I am using the below query:

SELECT 
    (SELECT COUNT(DISTINCT OrderItems.ProductID)
     FROM OrderItems) AS 'TotalSoldItemsDistinct', 
    (SELECT COUNT(OrderItems.ProductID)
     FROM OrderItems) AS 'TotalSoldItemsInDistinct',
    (SELECT COUNT(Orders.OrderID)
     FROM Orders) AS 'TotalOrders', 
   (SELECT COUNT(Orders.OrderID)
    FROM Orders
    WHERE Orders.OrderStatusID = @CompleteOStatusID) AS 'CompleteOrders', 
   (SELECT COUNT(Orders.OrderID)
    FROM Orders
    WHERE Orders.OrderStatusID = @PendingOStatusID) AS 'PendingOrders', 
   (SELECT COUNT(Orders.ClientID)
    FROM Orders
    WHERE Orders.ClientID != @WalkingCustID) AS 'namedcustomers',
   (SELECT COUNT(Orders.ClientID)
    FROM Orders
    WHERE Orders.ClientID = @WalkingCustID) AS 'WalkingCustomers',
   (SELECT SUM(OrderItems.PurchasePrice)
    FROM OrderItems) AS 'TotalPurchasePrice',
   (SELECT SUM(OrderItems.SalePrice) 
    FROM OrderItems) AS 'TotalSalePrice'

I am selecting data from 2 tables named 'Orders' and 'OrderItems', I have column TransactionDate in 'Orders' table and column OrderDate in OrderItems table on that I want to use where filter. Can anybody please suggest how to apply filter to whole query?

Upvotes: 1

Views: 4290

Answers (2)

TriV
TriV

Reputation: 5148

You could try this

;with tempOrderItems AS
   (   
      SELECT  
         COUNT(DISTINCT OrderItems.ProductID) AS 'TotalSoldItemsDistinct',
         COUNT(OrderItems.ProductID) AS 'TotalSoldItemsInDistinct',
         SUM(OrderItems.PurchasePrice) AS 'TotalPurchasePrice',
         SUM(OrderItems.SalePrice)  AS 'TotalSalePrice'
      FROM OrderItems ori
      WHERE OrderDate  BETWEEN xxx AND yyy
   )
, tempOrders AS
   (
      SELECT 
         COUNT(o.OrderID) AS 'TotalOrders',
         SUM(CASE WHEN o.OrderStatusID = @CompleteOStatusID THEN 1 ELSE 0 END) AS 'CompleteOrders', 
         SUM(CASE WHEN o.OrderStatusID = @PendingOStatusID THEN 1 ELSE 0 END) AS 'PendingOrders', 
         SUM(CASE WHEN o.ClientID != @WalkingCustID THEN 1 ELSE 0 END) AS 'namedcustomers', 
         SUM(CASE WHEN o.ClientID = @WalkingCustID THEN 1 ELSE 0 END) AS 'WalkingCustomers'
      FROM Orders o
      WHERE TransactionDate BETWEEN xxx AND yyy

   )   
SELECT * FROM tempOrderItems 
CROSS JOIN tempOrders

Upvotes: 1

Oswin Noetzelmann
Oswin Noetzelmann

Reputation: 9555

It is not fully clear what you want as a result, but here 2 approaches.

  1. Try the following for selecting data from 2 tables at the same time (replace the date with your criteria):

    SELECT * FROM Orders AS o INNER JOIN OrderItems AS i WHERE o.TransactionDate = '2015-02-12' AND i.OrderDate = '2015-02-12';

The SELECT * selects all columns from both tables as a result and the WHERE ... AND ...-clause filters for results only with your defined date.

  1. Try the following for selecting order item data only for Data that matches the date on a specific order.

    SELECT i.* FROM Orders AS o INNER JOIN OrderItems AS i WHERE o.TransactionDate = i.OrderDate AND o.OrderID = '12345';

The SELECT i.* tells the query to only return the columns of the OrderItems. And the WHERE o.TransactionDate = i.OrderDate ensures that only order items from the same date of the order with the OrderID "12345" are returned (which is defined with the AND o.OrderID = '12345'. This would work given you have a field "OrderID" on your Order table and you want to use it as a criteria.

Upvotes: 0

Related Questions