Jump to content

Need some help on using a variable


jleachomg
 Share

Recommended Posts

I have a nifty query that pulls data by orders.orderid > '85000' (and I update that number every week based on orders.) But I want it to automatically pull the most recent 3,000 orders instead of changing that number all the time, so I was hoping to set a variable which is Max(order.orderid)-3000, thus pulling the last 3000 order records. Here is my static select:

 

SELECT Orders.OrderID, Orders.OrderStatus, Orders.CustomerID, Orders.ShippingMethodID, Orders.BillingFirstName, Orders.BillingLastName, Orders.BillingAddress1, Orders.BillingAddress2, Orders.BillingCity, Orders.BillingState, Orders.BillingPostalCode, Orders.PaymentAmount, Orders.OrderDate, Orders.ShipDate, Orders.LastModified, fraud.score, CAST(Orders.OrderID AS VARCHAR) AS 'za_orderid_full', CAST(Orders.CustomerID AS VARCHAR) AS 'za_custid_full'FROM Orders JOIN Fraud ON Fraud.OrderID = Orders.OrderIDWHERE Orders.ShipDate IS NULL AND Orders.OrderID > '85000' AND Orders.OrderStatus <> 'Cancelled'

 

Can someone tell me how to declare and set that variable? Or suggest another way to accomplish this? Your help is greatly appreciated.

Link to comment
Share on other sites

Use LIMIT to get only the first 3000 records. You'll need to order by the orderID column in descending order, and then if you get the first 3000 records then that should do it. How to format the LIMIT clause will depend on which database system you're using, but you should be able to look that up.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...