SQL Query not producing desired results-CodePudding

Hi everyone I am new to SQL and I am trying to generate a new table from a sales fact table by applying certain filters which are:

payment_date can not be NULL
order_status can not be either "cancelled" nor trash
seller_id != 2119
And the date at which the order was created has to be between ""2022-01-01" and "2022-01-15"

I am getting good results with all the filters with the exception of the last one, my query is not filtering the range of dates I am looking for.

My query is as follows:

SELECT * FROM `test_orders_new`
WHERE (NOT(((`payment_date`) IS NULL)) 
      AND `seller_id` != 2119.0 
      AND `order_status` != 'cancelled' 
      AND `order_status` != 'trash' 
      AND CAST(`order_created_at` AS DATE) BETWEEN CAST('2022-01-01' AS DATE) AND CAST('2022-01-15' AS DATE))

I suspect it has something t do with the type of the column I am using for "order_created_at". Here is a sample of what the data looks like.

order_seller_id order_id     order_created_at         payment_date order_status seller_id total_item_quantity
1        86329-1425    86329 2022-01-01T09:50:43Z 2022-01-01T09:50:43Z    completed      1425                   2
2        86331-2537    86331 2022-01-01T15:40:13Z 2022-01-01T15:40:13Z    completed      2537                   1
3          86332-75    86332 2022-01-02T08:45:17Z 2022-01-02T08:45:17Z    completed        75                   1
4        86341-1724    86341 2022-01-02T22:56:28Z 2022-01-02T22:56:28Z    completed      1724                   1
5         86529-249    86529 2022-01-04T09:23:31Z 2022-01-04T09:23:31Z    completed       249                   1
6        86560-1994    86560 2022-01-05T13:33:00Z 2022-01-05T13:33:00Z    completed      1994                   1

Any suggestions on how this query can be made to work is greatly appreciated.

CodePudding user response：

I would use this version:

SELECT COUNT(DISTINCT order_seller_id)
FROM test_orders_new
WHERE payment_date IS NOT NULL AND
      seller_id != 2119 AND
      order_status NOT IN ('cancelled', 'trash') AND
      order_created_at >= '2022-01-01' AND order_created_at < '2022-01-16';