Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Petronas
Posting Yak Master
134 Posts |
Posted - 2013-03-19 : 10:21:15
|
Hi All,I have to pick 500 records for any given date parameter ,it could be for the past 4 years or for the past year.If the data is for past 4 years then the records pulled have to be equally distributed ,that is there should be equal or about equal records for 2012, 2011 , 2010 and 2009. I tried using order by newid() in by order clause but it does not show me data for all the years. How do I use the WHILE LOOP to get me the data. here is my base query from which I need to pull the 500 records :select Order_Date,Order_ID,Project_ID,Marketing_IDfrom Orderswhere order_date >='1/1/2009'and order_date <'1/1/2013'All help is appreciated. Thanks so much,Petronas |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-03-19 : 11:01:39
|
select select Order_Date,Order_ID,Project_ID,Marketing_IDfrom (select row_number() over (partition by year(Order_Date) order by Order_Date) as rn,* from Orders where order_date >='20090101' and order_date < '20130101')dtwhere rn <= 500/4 Too old to Rock'n'Roll too young to die. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-19 : 11:08:20
|
I guess this is a continuation of:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=183797If webfred's doesn't quite do it then:- What sql server version are you using?- Are you looking for an even distribution by date throughout the date range or, assuming you have hundreds of orders each day, can they all be from a single day of each year in the daterange? Another way to ask that is if the range is 1/1/12 through 12/31/12 can they all be from 1/1/12 or do you want them evenly spread throughout 2012?Be One with the OptimizerTG |
|
|
Petronas
Posting Yak Master
134 Posts |
Posted - 2013-03-19 : 12:31:01
|
Thanks Webfred and TJ for your replies. The data is spread over each day for several years. I cannot use Webfred's solution because I could be pulling the data either for just last year or last three years , four years or maybe just last month. I have written the below script to pull records based on the distinct year. This is my first attempt at cursors and I am not sure if I am using the counter @max<=450 (highlighted in yellow)in the correct block. When I execute the below code I get no data , though there are about millions of records for the past 4 years. I am using SQL Server 2008. I appreciate you spending your time in helping me out . Create table #Temp_final ( Order_Date datetime, Order_ID int, Project_ID varchar(5), Marketing_ID int )Declare @Order_date datetime, @year int, @max intselect @max = 0Declare Insert_Cursor Cursor For SELECT distinct(year(order_date)) From #Temp_MRN Open Insert_Cursor FETCH NEXT FROM insert_cursor INTO @yearWHILE (@@FETCH_STATUS = 0)BEGIN while @max<=450Begin Insert into #Temp_final select top 50 Order_Date, Order_ID, Project_ID, Marketing_ID from #Temp_MRN o where not exists (Select Order_id #Temp_final where Order_ID = o.Order_ID) set @max=@max+1 End Next_Record: FETCH NEXT FROM Insert_Cursor INTO @yearENDCLOSE insert_cursorDEALLOCATE insert_cursorSELECT * FROM #Temp_finalThanks,Petronas |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-19 : 13:55:12
|
see if this works:--return even distribution of 500 for the date rangeselect d.order_date ,rnfrom ( --get the first date in each distributed block select order_date ,row_number() over (partition by nt order by order_date) rn from ( --distribute the data into 500s select order_date ,ntile(500) over (order by order_date) nt from orders where order_date >='1/1/2009' and order_date <'1/1/2013' ) d ) dwhere rn = 1 Be One with the OptimizerTG |
|
|
Petronas
Posting Yak Master
134 Posts |
Posted - 2013-03-20 : 12:54:19
|
Thanks TG for your solution.It did not divide the records in equal numbers for each year. For 2008 to 2012 - for 2012 I got only 5 records. I used the below and though it does not give me the equal sets for each year it is evenly pulling the data over the year. The client still wants equal number of records for the years ( could be 2 years or could be for 5 years). Not sure how can that be done. Thanks again for your time. select mr.* into #Temp_final from (select *,ROW_NUMBER() over (partition by year(order_date) order by year(order_date)) as RowNumfrom Orders) mrwhere RowNum between 1 and 450order by year(order_date)select top 450 *From #Temp_final order by NEWID(),order_date desc |
|
|
|
|
|
|
|