?
Solved

Select Max

Posted on 2013-06-09
9
Medium Priority
?
268 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

762 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