I have a simple select statement displaying some data, I am about to add a field to track if my udf_qty_received field gets updated by a timestamp. Probably will name this field qtyrec_updated, I am trying to figure out the best way to display all the lines of a salesorder if one line has a timestamp of the day I open the excel file. So if SO#123 has 4 lines on it and one line the udf_qty_received gets updated today, then I want to display all 4 lines. We have maybe 1500 salesorders, and maybe 200 get updated daily that I want to display. I am thinking this can be done in SQL, but no idea on how. Below is my select statement.
USE [MAS_ACT]
GO
SELECT [SalesOrderNo]
,[BillToName]
,[UserCode]
,[DropShip]
,[ItemCode]
,[ItemCodeDesc]
,[WarehouseCode]
,[QuantityOrdered]
,[QuantityBackordered]
,[VendorNo]
,[UDF_VENDOR_NAME]
,[PurchaseOrderNo]
,[UDF_QTY_PULLED]
,[UDF_QTY_RECEIVED]
,[CommentText]
,[UDF_PURCHASING_NOTES]
FROM [dbo].[OpenOrders]
;WITH cteTodaysSalesOrderNos AS (
SELECT SalesOrderNo
FROM dbo.OpenOrders
WHERE qtyrec_updated >= CAST(GETDATE() AS date)
GROUP BY SalesOrderNo
)
SELECT oo.*
FROM cteTodaysSalesOrderNos ct
INNER JOIN dbo.OpenOrders oo ON oo.SalesOrderNo = ct.SalesOrderNo
Learning about views etc., so I can set this up as a separate view in our DB and link that view to an excel file to display it correct?
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE VIEW dbo.TodaysSalesOrderNos
AS
WITH cteTodaysSalesOrderNos AS (
SELECT SalesOrderNo
FROM dbo.OpenOrders
WHERE qtyrec_updated >= CAST(GETDATE() AS date)
GROUP BY SalesOrderNo
)
SELECT oo.*
FROM cteTodaysSalesOrderNos ct
INNER JOIN dbo.OpenOrders oo ON oo.SalesOrderNo = ct.SalesOrderNo
GO
Struggling to find where the correct syntax goes, I created a view "dbo.TodaysSalesOrderNos" but cant figure out where to enter the code? I am trying to figure it out through the design view.
Oh, I have no idea thru the design view. It has restrictions that using SQL/T-SQL, like above, does not. I've literally never used "design view" it to create a view (I'm a DBA, so I'm used to just writing CREATE VIEW).
I typed it out in a new query but it's like it will not let me execute the query? Could I not create views through this window?
Strange. Make sure you are in the correct user db.
USE [your_db_name] --<<--<<
SET ...
GO
CREATE VIEW ...
I was able to do the CREATE TO query but I had to save as a file couldnt create a view through it.
What error did you get when you ran the code?
Couldn't even run it, the execute button is greyed out
Guessing I have a lot to learn, all I had to do was right click and execute, I was trying to click the button in toolbar. Everything looks great!
