Return columns from different tables that are after a given date

Question

I need to write a query that returns product orders that were open during April of 2018 and are still open and also returns product orders that were open during April of 2018 and are no longer open.

The rows need to include in the results of the name of the customer that placed the order, the id for the order, and the date the order was filled.

Here is the table info

    CREATE TABLE dbo.ProductOrders
    (
        POID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY ,
        ProductId INT NOT NULL
            CONSTRAINT FK_ProductOrders_ProductId_ref_Products_ProductId
            FOREIGN KEY REFERENCES dbo.Products ( ProductId ) ,
        CustomerId INT NOT NULL ,
        OrderedQuantity INT ,
        Filled BIT NOT NULL
            CONSTRAINT DF_ProductOrders_Filled
                DEFAULT ( 0 ) ,
        DateOrdered DATETIME
            CONSTRAINT DF_ProductOrders_DateOrdered
                DEFAULT ( GETDATE()) ,
        DateFilled DATETIME
            CONSTRAINT DF_ProductOrders_DateFilled
                DEFAULT ( GETDATE())
    );

INSERT dbo.ProductOrders ( ProductId ,
                           CustomerId ,
                           OrderedQuantity ,
                           Filled ,
                           DateOrdered ,
                           DateFilled )
VALUES ( 2, 1, 1000, 0, '4/16/18 8:09:13', NULL ) ,
       ( 2, 1, 500, 1, '3/27/18 17:00:21', '6/24/18 13:29:01' ) ,
       ( 3, 3, 2000, 1, '12/01/04 13:28:58', '2/19/05 19:41:42' ) ,
       ( 1, 1, 632, 0, '5/23/18 4:25:52', NULL ) ,
       ( 4, 4, 901, 0, '3/30/18 21:30:28', NULL );


    CREATE TABLE dbo.Customers
    (
        CustomerId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY ,
        CustomerName NVARCHAR(100) ,
        Active BIT NOT NULL
            CONSTRAINT DF_Customers_Active
                DEFAULT ( 1 )
    );

INSERT dbo.Customers ( CustomerName ,
                       Active )
VALUES ( 'Bikes R'' Us', 1 ) ,
       ( 'Industrial Giant', 1 ) ,
       ( 'Widget-Works', 0 ) ,
       ( 'Custom Hangers', 1 );

This is my best attempt at it, I know this is not the right syntax but I'm not sure if I need a join between these to tables to make this work or how I would go about selecting orders that start at April 2018 and also are open or closed after that date.

select CustomerName, POID,  DataFilled, 
From ProductOrders, Customers
Where DateOrdered is >= April 2018

Answer 1

I think you want and join and filtering:

select c.customername, po.poid, po.dateordered, po.datefilled
from productorders po
inner join customers c on c.customerid = po.customerid
where 
    po.dateordered >= '20180401' 
    and po.dateordered < '20180501' 
    and po.datefilled < getdate()

This gives you orders that were ordered in April 2018 and are not open anymore as of now. To get orders that are still open, you would change the last condition to po.datefilled is null.