Forum Discussion
Finding matches against a calendar table (SQL)
Hi avidthinker
I did not understand your output, as per the tables on left I see Product B for the date 3/1/22 and not for A but in your result it is vice versa.
Can you share your sample data in excel and the expected solution? That would help me and this in a better way.
But I think you can use left join in SQL with Case to create a new column and Y or N flagging.
Here is the data
Historical Data Table, each day a product is scanned for orders, we can assume each day the orders are unique (only occurs once)
Calendar Table
Results
If there are no orders in the Orders table on Date, it needs to say No Orders for a product
If there are orders in the Orders table, it needs to say Ordered for a product
There and Thousands of possible products
The resulting table should have many dates the same, many products the same and also Ordered or Not Ordered reoccuring many times.
Hopes this helps
Eventually need to build on this and count how many Ordered and Not Ordered per product per date.