Solved

Select Max

Posted on 2013-06-09
9
266 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 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

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 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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