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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using WHILE LOOP

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_ID
from Orders
where 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_ID
from
(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')dt
where rn <= 500/4


Too old to Rock'n'Roll too young to die.
Go to Top of Page

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

If 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 Optimizer
TG
Go to Top of Page

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 int
select @max = 0

Declare Insert_Cursor Cursor For
SELECT distinct(year(order_date))
From #Temp_MRN

Open Insert_Cursor

FETCH NEXT FROM insert_cursor INTO @year

WHILE (@@FETCH_STATUS = 0)

BEGIN

while @max<=450
Begin
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 @year
END

CLOSE insert_cursor
DEALLOCATE insert_cursor

SELECT * FROM #Temp_final


Thanks,
Petronas
Go to Top of Page

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 range
select d.order_date
,rn
from (
--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
) d
where rn = 1


Be One with the Optimizer
TG
Go to Top of Page

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 RowNum
from Orders
) mr
where RowNum between 1 and 450
order by year(order_date)

select top 450 *
From #Temp_final
order by NEWID(),order_date desc
Go to Top of Page
   

- Advertisement -