March 30, 2009 at 12:05 pm
Here with sending the stored procedure which i written fro miograting the data from one date to other databse in sqlserver.
In the below stored proc.At sequence number i hard coded the workflowactivity type identifier as 200 and 201.
if we are creating multiple records on 200 or 201.i have to close the previous record with end date as completion date.
To identify that which activity have multiple records created ,in the code we have workflow activity code for 200 SIFW.04 and 201 SIFW.05.
What query can i write for that.
Stored procedure is :
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@sequence_number tinyint ,
@workflow_activity_code varchar(10),
@workflow_activity_description varchar(100),
@loan_security_filing_date smalldatetime,
@loan_security_type_description varchar(100),
@security_instrument_type_description varchar(50),
@state_county_flp_code varchar(5),
@ucc_filing_number varchar(16),
@workflow_activity_completion_date smalldatetime,
@workflow_activity_completion_user_name varchar(50),
@workflow_completion_date smalldatetime,
@workflow_completion_user_name varchar(50),
@workflow_creation_date smalldatetime,
@workflow_identifier int,
@workflow_activity_identifier int,
@farm_loan_customer_identifier int,
@data_effective_start_date smalldatetime,
@data_effective_end_date smalldatetime
DECLARE @SIFinanceOp_To_Rep_Temp TABLE(
sequence_number tinyint,
workflow_activity_code varchar(10),
workflow_activity_description varchar(100),
loan_security_filing_date smalldatetime,
loan_security_type_description varchar(100),
security_instrument_type_description varchar(50),
state_county_flp_code varchar(5),
ucc_filing_number varchar(16),
workflow_activity_completion_date smalldatetime,
workflow_activity_completion_user_name varchar(50),
workflow_completion_date smalldatetime,
workflow_completion_user_name varchar(50),
workflow_creation_date smalldatetime,
workflow_identifier int,
farm_loan_customer_identifier int,
data_effective_start_date smalldatetime,
data_effective_end_date smalldatetime)
/* create cursor to run through the records in reporting_migration_transaction*/
DECLARE SIFinance_migration_cursor_select CURSOR READ_ONLY FOR
SELECT distinct primary_reference_type_identifier,secondary_reference_type_identifier
FROM reporting_migration_transaction
WHERE reference_type_code='WF'
AND dls_reporting_workflow_type_code='SIFW'
/* use same date for all inserted records*/
select @data_effective_start_date = getdate();
/* Open the cursor*/
OPEN SIFinance_migration_cursor_select
FETCH SIFinance_migration_cursor_select INTO @workflow_identifier, @workflow_activity_identifier
WHILE @@fetch_status = 0
BEGIN
/*
QUERY DATA HERE
*/
-- workflow_activity_description
select @workflow_activity_code = dv.domain_value_code,
@workflow_activity_description = dv.domain_value_name
from domain_value dv,workflow_activity wfa
where wfa.workflow_activity_type_identifier=dv.domain_value_identifier
and wfa.workflow_activity_identifier = @workflow_activity_identifier
--Loansecuritytypedescription
select @Loan_security_type_description = dv.domain_value_name
from domain_value dv, record_financing_statement rfs
where rfs.loan_security_type_identifier = dv.domain_value_identifier
and rfs.workflow_activity_identifier = @workflow_activity_identifier
--securityinstrumenttypedescription
select @security_instrument_type_description=dv.domain_value_name
from domain_value dv,record_financing_statement rfs
where rfs.security_instrument_type_identifier = dv.domain_value_identifier
and rfs.workflow_activity_identifier = @workflow_activity_identifier
--Sequence number
Declare @workflow_activity_type_identifier int
select @workflow_activity_type_identifier=workflow_activity_type_identifier from workflow_activity
where workflow_activity_identifier = @workflow_activity_identifier
select @sequence_number = case @workflow_activity_type_identifier
when 197 then 1
when 203 then 1
when 200 then(select count(*) from workflow_activity where workflow_activity_type_identifier = 200
and workflow_identifier = @workflow_identifier)
when 201 then(select count(*) from workflow_activity where workflow_activity_type_identifier = 201
and workflow_identifier = @workflow_identifier)
else 0
end
--Loansecurityfilingdate
--statecountyflpcode
--uccfilingnumber
--workflowactivitycompletiondate
--workflowactivitycompletionusername
--workflowidentifier
--workflowcompletiondate
--workflowcompletionusername
--workflowcreationdate
--Farmloancustomer
select @loan_security_filing_date = rfa.loan_security_filing_date,
@state_county_flp_code = rfa.state_county_flp_code,
@ucc_filing_number = rfa.ucc_filing_number,
@workflow_activity_completion_date = wfa.completion_date,
@workflow_activity_completion_user_name = wfa.completion_user_name,
@workflow_completion_date = wf.completion_date,
@workflow_completion_user_name = wf.completion_user_name,
@workflow_creation_date = wf.creation_date,
@farm_loan_customer_identifier = csla.farm_loan_customer_identifier
from record_financing_statement rfa,workflow_activity wfa, workflow wf, customer_servicing_location_association csla
where rfa.workflow_activity_identifier = wfa.workflow_activity_identifier
and csla.customer_servicing_location_association_identifier = wf.customer_servicing_location_association_identifier
and wfa.workflow_identifier = wf.workflow_identifier
and wfa.workflow_activity_identifier = @workflow_activity_identifier
and wf.workflow_identifier = @workflow_identifier
INSERT INTO @SIFinanceOp_To_Rep_Temp VALUES(
@sequence_number ,
@workflow_activity_code,
@workflow_activity_description,
@loan_security_filing_date,
@loan_security_type_description,
@security_instrument_type_description,
@state_county_flp_code,
@ucc_filing_number ,
@workflow_activity_completion_date,
@workflow_activity_completion_user_name,
@workflow_completion_date,
@workflow_completion_user_name ,
@workflow_creation_date,
@workflow_identifier ,
@farm_loan_customer_identifier,
@data_effective_start_date ,
@data_effective_end_date)
FETCH SIFinance_migration_cursor_select INTO @workflow_identifier, @workflow_activity_identifier
END /*while loop*/
CLOSE SIFinance_migration_cursor_select
DEALLOCATE SIFinance_migration_cursor_select
/* Now select all the data from temp table for return */
SELECT * FROM @SIFinanceOp_To_Rep_Temp
END
Thanks.
March 30, 2009 at 12:12 pm
Providing us with your code is the first step, but we need more to really help you. It would help if you provided the DDL (CREATE TABLE statements) for the source table(s), sample data (in the form of INSERT statements that can be cut, paste, and executed in SSMS), and expected results based on the sample data.
Please read the first article referenced below in my signature block regarding asking for assistance for more information.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply