Select Max

Hello,
can you please help,
I need to find the MIN ORDERNO, from last Monday till last Friday.
I need to find the Max ORDERNO, from last Monday till last Friday.

Can you please help.

Select Orderno, Orderdate,Accountnumber,Caller,PickupCompanyName,PickupStreet,PickupUnit,PickupCity,PickupPostalCode,PickupProvince,PickupCountry,DeliveryCompanyName,DeliveryStreet,DeliveryUnit,DeliveryCity,DeliveryPostalCode,DeliveryProvince,DeliveryCountry
from ActiveOrders
where AccountNumber not in (6,511,3333) and Orderdate Between
UNNION ALL
Select Orderno, Orderdate,Accountnumber,Caller,PickupCompanyName,PickupStreet,PickupUnit,PickupCity,PickupPostalCode,PickupProvince,PickupCountry,DeliveryCompanyName,DeliveryStreet,DeliveryUnit,DeliveryCity,DeliveryPostalCode,DeliveryProvince,DeliveryCountry
from FinalizedOrders
where AccountNumber not in (6,511,3333) and  Orderdate Between

Thanks,
W.E.BAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
Another way.
;WITH Orders AS (
	Select Orderno, Orderdate,Accountnumber,Caller,PickupCompanyName,PickupStreet,PickupUnit,PickupCity,PickupPostalCode,PickupProvince,PickupCountry,DeliveryCompanyName,DeliveryStreet,DeliveryUnit,DeliveryCity,DeliveryPostalCode,DeliveryProvince,DeliveryCountry
	from ActiveOrders 
	where AccountNumber not in (6,511,3333)
		and Orderdate >= DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-7)
		and Orderdate < DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-2)

	UNION ALL

	Select Orderno, Orderdate,Accountnumber,Caller,PickupCompanyName,PickupStreet,PickupUnit,PickupCity,PickupPostalCode,PickupProvince,PickupCountry,DeliveryCompanyName,DeliveryStreet,DeliveryUnit,DeliveryCity,DeliveryPostalCode,DeliveryProvince,DeliveryCountry
	from FinalizedOrders 
	where AccountNumber not in (6,511,3333)
		and Orderdate >= DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-7)
		and Orderdate < DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-2)
),
cte as (
select *,row_number() over (order by Orderno) rn1,
        row_number() over (order by Orderno desc) rn2  
  from Orders)
SELECT *
  from cte
 where rn1 = 1 or rn2 = 1

Open in new window

0
 
ThomasianCommented:
You can filter the records to return orderdates between last Monday to last Friday by adding the following conditions
Orderdate >= DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-7)
and Orderdate < DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-2)

Open in new window

As for getting the Min and Max OrderNos, do you want to get the min and max of the whole recordset or the min and max of each account number?

Also, do you need the whole record (including the orderdate, accountnumber, etc.) or just the orderno?
0
 
W.E.BAuthor Commented:
Hello,
Thanks for your help

do you want to get the min and max of the whole recordset?
Correct, this is the requirement for now  -- (I'm pretty sure I will be asked to seperate by client in the future)

 do you need the whole record (including the orderdate, accountnumber, etc.) ?
Corect

Thanks again.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ThomasianConnect With a Mentor Commented:
;WITH Orders AS (
	Select Orderno, Orderdate,Accountnumber,Caller,PickupCompanyName,PickupStreet,PickupUnit,PickupCity,PickupPostalCode,PickupProvince,PickupCountry,DeliveryCompanyName,DeliveryStreet,DeliveryUnit,DeliveryCity,DeliveryPostalCode,DeliveryProvince,DeliveryCountry
	from ActiveOrders 
	where AccountNumber not in (6,511,3333)
		and Orderdate >= DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-7)
		and Orderdate < DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-2)

	UNION ALL

	Select Orderno, Orderdate,Accountnumber,Caller,PickupCompanyName,PickupStreet,PickupUnit,PickupCity,PickupPostalCode,PickupProvince,PickupCountry,DeliveryCompanyName,DeliveryStreet,DeliveryUnit,DeliveryCity,DeliveryPostalCode,DeliveryProvince,DeliveryCountry
	from FinalizedOrders 
	where AccountNumber not in (6,511,3333)
		and Orderdate >= DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-7)
		and Orderdate < DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-2)
)
SELECT TOP 1 *
FROM Orders
ORDER BY Orderno

Open in new window

0
 
W.E.BAuthor Commented:
Thank you,
this gaves me the Minimum Order Number, correct?
 
What about the Max Number?

Thanks
0
 
ThomasianCommented:
;WITH Orders AS (
	Select Orderno, Orderdate,Accountnumber,Caller,PickupCompanyName,PickupStreet,PickupUnit,PickupCity,PickupPostalCode,PickupProvince,PickupCountry,DeliveryCompanyName,DeliveryStreet,DeliveryUnit,DeliveryCity,DeliveryPostalCode,DeliveryProvince,DeliveryCountry
	from ActiveOrders 
	where AccountNumber not in (6,511,3333)
		and Orderdate >= DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-7)
		and Orderdate < DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-2)

	UNION ALL

	Select Orderno, Orderdate,Accountnumber,Caller,PickupCompanyName,PickupStreet,PickupUnit,PickupCity,PickupPostalCode,PickupProvince,PickupCountry,DeliveryCompanyName,DeliveryStreet,DeliveryUnit,DeliveryCity,DeliveryPostalCode,DeliveryProvince,DeliveryCountry
	from FinalizedOrders 
	where AccountNumber not in (6,511,3333)
		and Orderdate >= DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-7)
		and Orderdate < DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()+1),-2)
)
SELECT TOP 1 *
FROM Orders
ORDER BY Orderno

UNION ALL 

SELECT TOP 1 *
FROM Orders
ORDER BY Orderno DESC

Open in new window

0
 
W.E.BAuthor Commented:
Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'UNION'.

I commit the first ORDER BY Orderno

then I GET the same order number (twice) from Last Monday.

Thanks
0
 
W.E.BAuthor Commented:
Thank you very much.
0
 
ThomasianCommented:
Sorry, I missed that.
Shareth_123's query should work
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.