Creating SQL Views by Teaming with Microsoft Copilot

I have been using Copilot chat that is included free with my Office 365 subscription to help me build new SQL Queries. When I first started, I would either take an existing query (for example, one of the awesome free SQL Views from Victoria Yudin) or I would just send over column names and table names. Honestly doing this over and over has helped me improve and build on my SQL Skills so now, I create several SQL queries that only include the fields that I need vs. editing out info. This allows me to start with clean and precise queries that already have the preferred column names, Case Statements, etc. I then let Copilot take over with creating the more complex joins, filters, etc. When we finished, I had Copilot write the blog below. I added some screenshots and additional comments, but 95% was ready to go!

Launching Copilot from Microsoft Edge Browser

Introduction:

In this blog, we'll walk through the process of creating a comprehensive SQL query that combines information from various tables related to SOP transactions and AR apply information. This query aims to provide a detailed view of both unposted and historical transactions, including applied amounts, document types, and other relevant details.

Step-by-Step Process:

• Initial Query Setup: We started with a basic query that pulled information from the SOP10100 table, including customer details, SOP type, document number, date, customer PO, document amount, and site ID. We also included a case statement to categorize the SOP type.

   SELECT
       SOP10100.CUSTNMBR [Customer ID],
       CUSTNAME [Customer Name],
       CASE SOP10100.SOPTYPE
           WHEN 1 THEN 'Quote'
           WHEN 2 THEN 'Order'
           WHEN 3 THEN 'Invoice'
           WHEN 4 THEN 'Return'
           WHEN 5 THEN 'Back Order'
           WHEN 6 THEN 'Fulfillment Order'
           ELSE 'Unknown'
       END AS [SOP Type],
       SOPNUMBE [Document Number],
       SOP10100.DOCDATE [Date],
       SOP10100.CSTPONBR [Customer PO],
       SOP10100.DOCAMNT [Document Amount],
       SOP10100.LOCNCODE [Site ID]
   FROM SOP10100

• Adding Comments and Joining Tables: We added the SOP10106 table to include transaction-level comments and joined it on both SOPNUMBE and SOPTYPE.

   JOIN SOP10106 ON SOP10100.SOPNUMBE = SOP10106.SOPNUMBE AND SOP10100.SOPTYPE = SOP10106.SOPTYPE

I forgot to include the 2nd table when I first reached out to Copilot…but it could tell that I was asking about Microsoft Dynamics GP data and offered a connection to a related table!

Copilot already started helping by combining a related table.

Adding table with Sales Comment/User Defined fields.

• Combining Current and Historical Transactions: To include historical transactions, we used a UNION ALL statement to combine data from the SOP30200 table with the same structure as the SOP10100 table.

   UNION ALL
   SELECT
       SOP30200.CUSTNMBR [Customer ID],
       CUSTNAME [Customer Name],
       CASE SOP30200.SOPTYPE
           WHEN 1 THEN 'Quote'
           WHEN 2 THEN 'Order'
           WHEN 3 THEN 'Invoice'
           WHEN 4 THEN 'Return'
           WHEN 5 THEN 'Back Order'
           WHEN 6 THEN 'Fulfillment Order'
           ELSE 'Unknown'
       END AS [SOP Type],
       SOP30200.SOPNUMBE [Document Number],
       SOP30200.DOCDATE [Date],
       SOP30200.CSTPONBR [Customer PO],
       SOP30200.DOCAMNT [Document Amount],
       SOP30200.LOCNCODE [Site ID],
       SOP10106.CMMTTEXT [Comment]
   FROM SOP30200

• Adding Applied Information: We included applied information from the RM20201 and RM30201 tables, joining them on CUSTNMBR and APTODCNM. We also added columns for applied amounts, discount amounts, write-off amounts, and document types.

   LEFT JOIN RM20201 ON SOP10100.CUSTNMBR = RM20201.CUSTNMBR AND SOP10100.SOPNUMBE = RM20201.APTODCNM

Fixing Error for query line that didn’t reference the Table.

• Handling Ambiguous Column Names: We encountered an issue with ambiguous column names, which we resolved by specifying the table name in the column reference.

   SOP10100.SOPNUMBE [Document Number]

• Including Check Number and Amounts: We added the check number and original and current amounts from the RM20101 and RM30101 tables, joining them appropriately.

   LEFT JOIN RM20101 ON RM20201.APFRDCNM = RM20101.DOCNUMBR AND RM20201.CUSTNMBR = RM20101.CUSTNMBR
   LEFT JOIN RM20101 AS RM20101_TO ON SOP10100.SOPNUMBE = RM20101_TO.DOCNUMBR AND SOP10100.CUSTNMBR = RM20101_TO.CUSTNMBR

Fixing Non-Existent Columns - Adding in fields that did not exist on all tables

Pitfalls and Challenges:

• Ambiguous Column Names: We had to ensure that column names were explicitly referenced with their table names to avoid ambiguity.

• Non-Existent Columns: We initially included columns that did not exist in the referenced tables, which required adjustments.

• Complex Joins: Managing multiple joins across different tables required careful attention to ensure data integrity and accuracy.

Final Query:

The final query combined all the necessary information from various tables, providing a comprehensive view of SOP transactions with AR apply information. I will be testing with my client this week! If you need help with combining data into a custom SQL View that can be used in SmartList Designer, SmartList Builder, PowerBI, or Excel. Let me know! Me and my “Copilot” are ready to help!

P.S. From Amber - Bonus Feature with Using Copilot

One feature I wanted to point out is that Copilot will save your Chat History. You can rename the chats so that you can go back to them later! Just hover over the chat in the history pane. You will see 3 dots, click them and choose “Rename”. I love this feature!