MS SQL Query + certain number of entries
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?
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?
ASKER
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?
wouldnt your query pull anything less than 8, so if someone only placed 1 order they would show up?
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
ASKER
where do i put the table name? Customers (from Customers)
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
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
ASKER
is there anyway to only pull customers with 7 orders, and if they have more than 7 only pull latest 7 ?
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
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
ASKER
no that still pulls any customer with less than 8 orders
could you post the sample data and expected result?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
exactly what i need - thank you
select column1,column2 (
select column1, column2, row_number() over(partition by yourPKcolumn order by date desc) rn
) x
where x.rn < 8