Counter & Audit - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Counter & Audit

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-08 : 09:48:01

fari writes "Need advice on how to best tackle this task. l've created a table called Run_Audit with the following fields
Run_Counter,Run_Date,No_Of_Records,Run,Status.

Run_Counter will be a number that increaments by 1 each time the procedure runs
Run_Date this will default to the system date l can uset the getdate function l guess
No_Of_Records will be the number of records grouped by pay_frequency
Run_Status was it successful ? Should insert a message eg

declare @Comp_msg varchar(255)
declare @fail_msg varchar(255)

set @Comp_msg ='The run completed successfully'
set @fail_msg ='The run did not complete successfully'

my problem is l dont know where to start with this procedure which is a modification of the Procedure below.Please
advice.

CREATE Procedure Collections_Cats_Modified
AS
BEGIN

Declare @Today int
Set @Today = (SELECT Day(GETDATE()))

DECLARE Collections_Cats_Cursor
CURSOR
FOR
SELECT CASE
WHEN Pay_Frequency = 4 And Payday =@Today THEN @Today
WHEN Pay_Frequency = 3 And Payday =@Today THEN @Today
WHEN Pay_Frequency = 2 And Payday =@Today THEN @Today
WHEN Pay_Frequency = 1 And Payday =@Today THEN @Today
ELSE 0
END AS "Pay_day",
n.loan_No AS Loan_No,
n.customer_No AS Customer_No,
c.first_name AS First_name,
c.second_name AS Second_name,
c.surname AS Surname,
c.initials AS Initials,
b.Bank_name AS Bank_name,
br.branch_code AS Branch_code,
d.bank_acc_type AS Bank_acc_type,
pay_sheet.pay_frequency AS Pay_Frequency,
n.monthly_Payment AS monthly_Payment
FROM Transaction_Record tr
INNER JOIN
Loan n ON tr.loan_No = n.loan_No
INNER JOIN
Customer c ON n.customer_No = c.customer_no
INNER JOIN
Bank_detail d ON c.customer_no = d.customer_no
INNER JOIN
Branch br ON d.Branch = br.Branch
INNER JOIN
Bank b ON br.Bank = b.Bank
INNER JOIN
pay_sheet ON c.customer_no = pay_sheet.customer_no
WHERE Pay_Sheet.Payday = @Today
And Pay_sheet.Pay_Frequency Between 1 And 4
And Pay_Sheet.Payday Between 1 And 31

OPEN Collections_Cats_Cursor
-- Perform the first fetch.
FETCH NEXT FROM Collections_Cats_Cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Collections_Cats_Cursor
END
CLOSE Collections_Cats_Cursor
DEALLOCATE Collections_Cats_Cursor
END;
GO"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources