Solved

Select Max

Posted on 2013-06-09
9
261 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
  • 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 150 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 40

Accepted Solution

by:
Sharath earned 150 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now