Finding matches against a calendar table (SQL)

Question

I have 2 tables, historical table with billions of rows of data and when it last scanned information, also calendar table and needing a result table. I need to find against a product and its scan time (hoping to move this to SQL to pull directly using incremental refresh) for every calendar date, have a row either it exists in the data table (as Y) or not (as N) for each product.&nbsp;The Date is the only common field between each&nbsp;Calendar&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DataTable A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Table B Historical Last Scan&nbsp; &nbsp;Result TableDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Product&nbsp; &nbsp; &nbsp;Y/N4/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4/1/22&nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y3/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/22&nbsp; &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;N2/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2/1/22&nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y1/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/22&nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;N&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;N&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NWas able to use cross join and left join to show the following which is not what is wanted on the resulting table&nbsp;4/1/22&nbsp; &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y4/1/22&nbsp; &nbsp; &nbsp; &nbsp;null&nbsp; &nbsp; &nbsp;null&nbsp; &nbsp; &nbsp;(no B match)3/1/22&nbsp; &nbsp; &nbsp; B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Y3/1/22&nbsp; &nbsp; &nbsp;null&nbsp; &nbsp; &nbsp; &nbsp;null&nbsp; &nbsp; &nbsp;(no A match)2/1/22&nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Y2/1/22&nbsp; &nbsp; &nbsp;null&nbsp; &nbsp; &nbsp; &nbsp;null&nbsp; &nbsp; &nbsp;(no B match)1/1/22&nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Y1/1/22&nbsp; &nbsp; &nbsp;null&nbsp; &nbsp; &nbsp; &nbsp;null&nbsp; &nbsp;&nbsp;(no B match)&nbsp;&nbsp;&nbsp;&nbsp;N Represents no matchesY Represents a match in Table 2 (Data) on the Date from Calendar TableEventually want to do a count on the Y or N, confused on how to construct the query to pull the data in&nbsp;Assistance is very much appreciated

ankitkukreja · Answer

Hi&nbsp;avidthinker&nbsp;
&nbsp;
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.
&nbsp;
Can you share your sample data in excel and the expected solution? That would help me and this in a better way.
&nbsp;
But I think you can use left join in SQL with Case to create a new column and Y or N flagging.
&nbsp;

avidthinker · Answer

Here is the data&nbsp;&nbsp;Historical Data Table, each day a product is scanned for orders, we can assume each day the orders are unique (only occurs once)&nbsp;Calendar Table&nbsp;ResultsIf there are no orders in the Orders table on Date, it needs to say No Orders for a productIf there are orders in the Orders table, it needs to say Ordered for a productThere and Thousands of possible products&nbsp;The resulting table should have many dates the same, many products the same and also Ordered or Not Ordered reoccuring many times.&nbsp;Hopes this helpsEventually need to build on this and count how many Ordered and Not Ordered per product per date.&nbsp;&nbsp;

Forum Discussion

Finding matches against a calendar table (SQL)

2 Replies

Related content

Calendar Slicer with Different Business Calendars

Custom calendar

Tweaking Financial Calendar

Finding matching substring with DAX

Weeknum as per Calendar