Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
avidthinker
Frequent Visitor

Finding matches against a calendar table (SQL)

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.

 

The Date is the only common field between each

 

Calendar         Data

Table A           Table B Historical Last Scan   Result Table

Date               Date          Product                 Date               Product     Y/N

4/1/22            4/1/22       A                           4/1/22            A               Y

3/1/22            1/1/22       B                           4/1/22            B               N

2/1/22            2/1/22       A                           3/1/22            A               Y

1/1/22            1/1/22       A                           3/1/22            B               N

                                                                      2/1/22            A               N
                                                                      2/1/22            B               Y

                                                                      1/1/22            A               Y

                                                                      1/1/22            B               N

Was able to use cross join and left join to show the following which is not what is wanted on the resulting table

 

4/1/22       A         Y

4/1/22       null     null     (no B match)

3/1/22      B          Y
3/1/22     null       null     (no A match)

2/1/22      A           Y

2/1/22     null       null     (no B match)

1/1/22     A            Y

1/1/22     null       null    (no B match)

 

 

 

 

N Represents no matches

Y Represents a match in Table 2 (Data) on the Date from Calendar Table

Eventually want to do a count on the Y or N, confused on how to construct the query to pull the data in

 

Assistance is very much appreciated

2 REPLIES 2
AnkitKukreja
Super User
Super User

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.

 

For Power BI trainings or support dm or reach out to me on LinkedIn.
If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
https://topmate.io/ankit_kukreja

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)

 

avidthinker_0-1672121688363.png

Calendar Table

avidthinker_1-1672121741510.png

 

Results

avidthinker_2-1672121900110.png

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.

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.