[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS SQL Query + certain number of entries

Posted on 2011-05-03
11
Medium Priority
?
238 Views
Last Modified: 2012-05-11
I would like to run a query on a table of customer orders, i want to set a time frame, so looking at the last 3 months, and pulling a list of customer that have placed 7 orders in that time frame.

So would like to display the 7 orders in the last 3 month, so some might have place 7 in last 30 days, other may have too all 3 months, but i want to display the most recent, but only 7 of them.

How would i do this?
0
Comment
Question by:dkilby
  • 5
  • 5
11 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 35515776
how is your table look like?

select column1,column2 (
select column1,  column2, row_number() over(partition by yourPKcolumn order by date desc) rn
) x
where x.rn < 8


0
 

Author Comment

by:dkilby
ID: 35515947
customerID, orderdate, ordernumber are the fields i am looking to utilize

wouldnt your query pull anything less than 8, so if someone only placed 1 order they would show up?
0
 
LVL 18

Expert Comment

by:sventhan
ID: 35515982

yes and for the recent 7 orders

select customerID, orderdate, ordernumber (
select customerID, orderdate, ordernumber, row_number() over(partition by Customerid order by orderdate desc) rn
) x
where x.rn < 8
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:dkilby
ID: 35516011
where do i put the table name? Customers (from Customers)
0
 
LVL 18

Expert Comment

by:sventhan
ID: 35516026
sorry forgot the tablename

select customerID, orderdate, ordernumber (
select customerID, orderdate, ordernumber, row_number() over(partition by Customerid order by orderdate desc) rn
from customers
) x
where x.rn < 8
0
 

Author Comment

by:dkilby
ID: 35516100
is there anyway to only pull customers with 7 orders, and if they have more than 7 only pull latest 7 ?
0
 
LVL 18

Expert Comment

by:sventhan
ID: 35516285
Try this,

select customerID, orderdate, ordernumber (
select customerID, orderdate, ordernumber, row_number() over(partition by Customerid order by orderdate, ordernumber desc) rn
from customers
) x
where x.rn < 8
0
 

Author Comment

by:dkilby
ID: 35516347
no that still pulls any customer with less than 8 orders
0
 
LVL 18

Expert Comment

by:sventhan
ID: 35516361
could you post the sample data and expected result?
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35517018
I think you are looking for the latest 7 orders for every customer who ordered at least 7 orders in the last 3 months. If so, you can try this.
SELECT customerID, 
       orderdate, 
       ordernumber 
  FROM (SELECT customerID, 
               orderdate, 
               ordernumber, 
               COUNT(*) OVER (PARTITION BY customerID) 
               cnt, 
               ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY orderdate 
               DESC) rn 
          FROM Customers 
         WHERE orderdate >= DATEADD(MONTH, -3, GETDATE())) t1 
 WHERE cnt >= 7 
       AND rn <= 7 
 ORDER BY rn   

Open in new window

0
 

Author Closing Comment

by:dkilby
ID: 35517258
exactly what i need - thank you
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

829 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