May 1, 2008 at 11:31 am
/*CREATE procedure UL_transaction_calculations
as */
/* Documentation */
/* */
/* Declare temporary variables */
DECLARE @currentdate datetime
/* Declare transaction table variables */
DECLARE @datediff_week float
DECLARE @Row_Date datetime
/* Set variables */
SELECT @currentdate = getdate()
/* Declare cursor */
DECLARE UL_transaction_cursor CURSOR FOR
SELECT Row_Date
FROM UL_Phone_Stats
/* Open ul_transaction_cursor */
OPEN ul_transaction_cursor
/* First read for ul transaction */
FETCH NEXT FROM ul_transaction_cursor INTO
@Row_Date
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS = 0) /* Record exists */
BEGIN
select @datediff_week = datediff (week,@Row_Date,@currentdate)
print @datediff_week
END
/* Write transaction count records
INSERT INTO UL_TRANSACTION_CALCS
(date_recorded,
date_work_performed,
approval_time,
approval_count,
close_time,
close_count,
inforce_time,
inforce_count,
lta_time,
lta_count,
mail_time,
mail_count,
newapp_time,
newapp_count,
part2_time,
part2_count,
rushes_time,
rushes_count,
associate_name,
team_id,
login_id,
champ_production_time,
champ_production_count,
[Month],
Follow_Up_time,
Follow_Up_count)
values
(@currentdate,
@last_activity_time,
@approvals_CALC,
@approvals,
@close_CALC,
@close,
@inforce_CALC,
@inforce,
@LTA_CALC,
@lta,
@mail_CALC,
@mail,
@newapp_CALC,
@newapp,
@PartII_CALC,
@PartII,
@rushes_CALC,
@rushes,
@name_conv,
@team_conv,
@phone_logid_name,
@champ_production_time,
@champ_production_count,
@date_month_format,
@follow_up_CALC,
@follow_up) */
/* Read next request record */
FETCH NEXT FROM ul_transaction_cursor INTO
@Row_Date
END /* End the WHILE loop */
CLOSE ul_transaction_cursor
DEALLOCATE ul_transaction_cursor
GO
May 1, 2008 at 1:38 pm
May I ask what this is intended to do and why it's a cursor instead of just using Insert ... Select ...?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2008 at 2:24 pm
I am looking access data from a view and based on row date pull last 6 full weeks of data and populate a table with that data broken out by week.
May 1, 2008 at 2:38 pm
You should be able to do that with a single insert statement, without using a cursor. I'd have to see the table definitions, some sample data (a few rows), and the expected output, to be able to help out on that much. But it should be possible.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 8, 2008 at 12:22 pm
Sorry for nor replying sooner I have been out past few days. Anyway I have attached requested information. A couple notes:
-Table A and B need to be joined by Row_date Login_ID. I realize it would help if Row_date was a datetime field, but it is imported form a text file.
-Percentages in Expected Output tab are created by converting dividing champ_production_time/Staffed_Time where Staffed_Time has been converted to minutes.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply