SQL statement help : Select data based on a condition-CodePudding

I want to base my select query on a condition so it is something like :

--THE LOGIC TO BE IMPLEMENTED
IF ( SELECT OrderPlacementDate from SellOrders WHERE SellOrders.ProductName = Products.ProductName UNION SELECT OrderPlacementDate from BuyOrders WHERE BuyOrders.ProductName = Products.ProductName is today then :

SELECT Products.ProductName, tblCommun.firstdate
FROM Products
OUTER APPLY (SELECT TOP 1 OrderPlacementDate as firstdate
             FROM ( SELECT OrderPlacementDate from SellOrders WHERE SellOrders.ProductName = Products.ProductName UNION SELECT OrderPlacementDate from BuyOrders WHERE BuyOrders.ProductName = Products.ProductName) t 
             ORDER BY OrderPlacementDate ASC) tblCommun;

The following query gives an output table for the list of all products ( where some of these product have no orders for today products have orders for today ) and the date timestamp when a first order was placed for every product :

SELECT Products.ProductName, tblCommun.firstdate
FROM Products
OUTER APPLY (SELECT TOP 1 OrderPlacementDate as firstdate
             FROM ( SELECT OrderPlacementDate from SellOrders WHERE SellOrders.ProductName = Products.ProductName UNION SELECT OrderPlacementDate from BuyOrders WHERE BuyOrders.ProductName = Products.ProductName) t 
             ORDER BY OrderPlacementDate ASC) tblCommun;

ProductName   | firstdate
---------------------------------------
Apple         | 2021-10-12 13:55:50.100
Orange        | 2021-10-15 08:40:14.670
Carrot        | 2021-10-29 08:45:20.110

Example from tables SellOrders and BuyOrders :

SellOrders

OrderID  | ProductName  | OrderPlacementDate
-------------------------------------------------
546      | Apple        | 2022-04-20 13:55:30.100
547      | Apple        | 2022-04-20 14:55:50.100
548      | Orange       | 2022-04-20 15:40:50.100
340      | Carrot       | 2022-04-19 13:55:50.100
230      | Carrot       | 2022-04-18 16:50:50.100

BuyOrders

OrderID  | ProductName  | OrderPlacementDate
-------------------------------------------------
500      | Apple        | 2022-04-20 10:55:30.100
540      | Apple        | 2022-04-20 12:55:50.100
267      | Carrot       | 2022-04-19 10:55:50.100
235      | Carrot       | 2022-04-18 15:50:50.100
229      | Orange       | 2022-04-18 13:40:50.100

THE GOAL: I want to display only the list of products where there was orders placed today.

Today is 2022-04-20, so the output table that I wish to have is :

ProductName   | firstdate
---------------------------------------
Apple         | 2021-10-12 13:55:50.100
Orange        | 2021-10-15 08:40:14.670

What is the best way to do that please ?

CodePudding user response：

Seems you could use some conditional aggregation in the HAVING to check there was an order today:

WITH Orders AS(
    SELECT SO.ProductID,
           SO.OrderPlacementDate
    FROM dbo.SellOrders SO
    UNION ALL
    SELECT BO.ProductID,
           BO.OrderPlacementDate
    FROM dbo.BuyOrders BO)
SELECT P.ProductName,
       MIN(O.OrderPlacementDate) AS FirstDate
FROM dbo.Product P
     JOIN Orders O ON P.ProductID = O.ProductID
GROUP BY P.ProductName
HAVING COUNT(CASE WHEN O.OrderPlacementDate >= CONVERT(date,GETDATE()) AND O.OrderPlacementDate < DATEADD(DAY,1,CONVERT(date,GETDATE())) THEN 1 END) > 0;

Or, alternatively, you could use an EXISTS. I'll leave you to test what is more performant on your environment.

WITH Orders AS(
    SELECT SO.ProductID,
           SO.OrderPlacementDate
    FROM dbo.SellOrders SO
    UNION ALL
    SELECT BO.ProductID,
           BO.OrderPlacementDate
    FROM dbo.BuyOrders BO)
SELECT P.ProductName,
       MIN(O.OrderPlacementDate) AS FirstDate
FROM dbo.Product P
     JOIN Orders O ON P.ProductID = O.ProductID
WHERE EXISTS (SELECT 1
              FROM Orders e
              WHERE e.ProductID = O.ProductID
                AND e.OrderPlacementDate >= CONVERT(date,GETDATE())
                AND e.OrderPlacementDate < DATEADD(DAY,1,CONVERT(date,GETDATE())))
GROUP BY P.ProductName;

db<>fiddle

CodePudding user response：

WITH CTE (
SELECT * FROM SellOrders WHERE CONVERT(date, OrderPlacementDate) = 
CONVERT(date, GETDATE())
UNION
SELECT * FROM BuyOrders WHERE CONVERT(date, OrderPlacementDate) = 
CONVERT(date, GETDATE())
) todaysOrders

SELECT DISTINCT p.ProductNmae, p.firstdate FROM Products p 
INNER JOIN todaysOrders t ON t.ProductName = p.ProductName

CodePudding user response：

Another approach is using outer apply to get the sells/buys for today

select p.ProductName,
       p.FirstDate
from   Products p
  outer apply (select top 1 s.OrderPlacementDate 
               from SellOrders s 
               where s.ProductName = p.ProductName
               and   convert(date, s.OrderPlacementDate) = convert(date, getdate())
              ) sell
  outer apply (select top 1 b.OrderPlacementDate 
               from BuyOrders b
               where b.ProductName = p.ProductName
               and   convert(date, b.OrderPlacementDate) = convert(date, getdate())
              ) buy
where sell.OrderPlacementDate is not null
or buy.OrderPlacementDate is not null

DBFiddle