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
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
