SQL How to create a job that runs once a day and updates a table based on what is missing?

I have a simple table called Z_N_FullRetailTable that has an Item_Number, Color_Code, and Retail Price.

I created this table manually. However, sometimes we add items to our collection so I would need this table to be updated with the new item and it’s retail price.

How I want to do that is to run a query against our Orders table. See all the items that were ordered that do not exist in the Retail table, and add them to the retail table with it’s first sale price as it’s retail price.

This is the query I wrote up. It finds all the items that were ordered except the ones that already exist in the retail table.

Select LTRIM(RTRIM(TT.Item_Number)) Sku, LTRIM(RTRIM(TT.Color_Code)), 
TT.Retail Retail
From 
(
Select Distinct 
Item_Number
,Color_Code
From Order_Detail O
Where O.Customer_Number = 'Ecom2x' AND ISNUMERIC(O.Customer_Purchse_Order_Number) <> 0 

EXCEPT

Select 
Z.Sku
,Z.Color
From Z_N_FullRetailTable Z
) T --Gets List of Missing SKUs

LEFT JOIN
(
Select Distinct 
Item_Number
,Color_Code
,MAX(O.Price) OVER (Partition by O.Item_Number) Retail
From Order_Detail O
Where O.Customer_Number = 'Ecom2x' AND ISNUMERIC(O.Customer_Purchse_Order_Number) <> 0 
) TT ON T.Item_Number = TT.Item_Number and T.Color_Code = TT.Color_Code

How would I turn this into a job that runs once a day?

Also, is there any way to make it so it get’s its max price from the orders for the year 2019 and not for the orders from all time. But if that item was not ordered in 2019, then it goes back and gets its max price from the orders from 2018, and so on. Or would this be too complicated?

Answer

Personal note: you should get in a habit of using the schema in your code, for example:

From Order_Detail O 

should be

From dbo.Order_Detail O

For your “how do I make this a job to run once a day” question:

Once you have your query perfected, I’d turn it into a stored procedure. Then, from within SSMS, under SQL Server Agent, then Jobs – right click on Jobs, click New Job. In the General tab:

• Name the job something descriptive
• Set the owner to be ‘sa’

In the Steps tab:

• Click New…
• Name the step
• Choose the database your new stored procedure exists in.
• In the command text area, type the command: EXEC dbo.StoredProcedureName
• Click Ok

In the Schedules Tab

• Click New…
• Name the Schedule and select your options for frequency and time
• Hit Ok

For your “get max price by latest year” sort of question, I’d do something like this:

; WITH prices AS (SELECT Item_Number, Price
 , rn = ROW_NUMBER() OVER (Partition by Item_Number ORDER BY Year(OrderDate?) DESC, Price DESC))
SELECT * 
FROM prices
WHERE rn = 1