Thanks I will see what I can do with this. I appreciate you time and your help.
-----Original Message----- From: John Stanley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 9:53 AM To: CF-Talk Subject: RE: Stored Proc Question Help Please The syntax I have attached actually uses two temp tables and then unions them at the end, but does use a cursor to loop with. Keep in mind this is SQL 6.5, and some of the functionality might be depricated or changed: create table #temp_origin_records ( summary_id int, service_type int, type char(25), company_id int, company_name char(255), score int, status char(2), status_int int, date_created datetime, probation char(2) ) create table #temp_dest_records ( summary_id int, service_type int, type char(25), company_id int, company_name char(255), score int, status char(2), status_int int, date_created datetime, probation char(2) ) Declare @company_id int --Declare @this_date datetime --Declare @that_date datetime --Select @this_date = '05/23/03' --Select @that_date = '05/24/03' Insert into #temp_origin_records (summary_id,service_type,type,company_id,company_name,sco re,status,status_int,date_created,probation) Select es.summary_id,es.service_type,lt.description,es.company_id,c.name,es.score,e s.status, status_int = case When es.status = 'R' then 1 else 0 end, es.date_created,es.probation from evaluatio n_summary es join company c on c.id = es.company_id join leg_type lt on lt.id = es.service_type where es.company_id > 0 and es.date_created >= @this_date AND date_created < @that_date and es.status = 'R' --and service_type = 1 Declare this _cursor CURSOR FOR Select company_id from #temp_origin_records Open this_cursor Fetch Next from this_cursor into @company_id While (@@fetch_status = 0) Begin Insert into #temp_dest_records (summary_id,service_type,type,company_id,company _name,score,status,status_int,date_created,probation) Select es.summary_id,es.service_type,lt.description,es.company_id,c.name,es.score,e s.status, status_int = case When es.status = 'R' then 1 else 0 end, es.date_created ,es.probation from evaluation_summary es join company c on c.id = es.company_id join leg_type lt on lt.id = es.service_type where summary_id = (select max(summary_id) from evaluation_summary where date_created < @this_date and company_i d = @company_id)-- and service_type = 1 Fetch Next from this_cursor into @company_id End close this_cursor deallocate this_cursor select * from #temp_origin_records union all select * from #temp_dest_records order by company_id, summar y_id desc drop table #temp_origin_records drop table #temp_dest_records -----Original Message----- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:29 AM To: CF-Talk Subject: Stored Proc Question Help Please This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
