Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Select Max

Posted on 2013-06-09
9
Medium Priority
?
269 Views
Last Modified: 2013-06-09
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,
0
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 22

Expert Comment

by:Thomasian
ID: 39233705
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
 

Author Comment

by:W.E.B
ID: 39233711
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
 
LVL 22

Assisted Solution

by:Thomasian
Thomasian earned 600 total points
ID: 39233715
;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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:W.E.B
ID: 39233722
Thank you,
this gaves me the Minimum Order Number, correct?
 
What about the Max Number?

Thanks
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 39233732
;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
 

Author Comment

by:W.E.B
ID: 39233740
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
 
LVL 41

Accepted Solution

by:
Sharath earned 600 total points
ID: 39233744
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
 

Author Closing Comment

by:W.E.B
ID: 39233752
Thank you very much.
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 39233753
Sorry, I missed that.
Shareth_123's query should work
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question