Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
12 | |
12 |