SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using WHILE LOOP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Petronas
Posting Yak Master

133 Posts

Posted - 03/19/2013 :  10:21:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 03/19/2013 :  11:01:39  Show Profile  Visit webfred's Homepage  Reply with Quote
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.

Edited by - webfred on 03/19/2013 11:03:18
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/19/2013 :  11:08:20  Show Profile  Reply with Quote
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

133 Posts

Posted - 03/19/2013 :  12:31:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/19/2013 :  13:55:12  Show Profile  Reply with Quote
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

133 Posts

Posted - 03/20/2013 :  12:54:19  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000