create & update account numbersfor existing records

[Jassim Rahma]

I have the following code to update the account number in the account summary & clients tables when creating a new client profile but I have a major problem now with existing records as the account concept was proposed lately.. I want to know how can I use the same code to loop through all existing (already created) records and define an account number for it? note: I want a random weekday and dayofyear so not all records will have the same weekday creation..

INSERT INTO clients (company_id, client_name, client_cpr, client_passport, client_gender, client_nationality, client_date_of_birth, client_visa_number, client_visa_expiry, client_workpermit_number, client_workpermit_expiry, client_telephone, client_fax, client_mobile, send_sms, client_occupation, client_email, send_emails, client_salary, home_country_address, client_is_vip, client_is_blacklist) VALUES (@company_id, @client_name, @client_cpr, @client_passport, @client_gender, @client_nationality, @client_date_of_birth, @client_visa_number, @client_visa_expiry, @client_workpermit_number, @client_workpermit_expiry, @client_telephone, @client_fax, @client_mobile, @send_sms, @client_occupation, @client_email, @send_emails, @client_salary, @home_country_address, @client_is_vip, @client_is_blacklist)
SET @record_identity = SCOPE_IDENTITY()
INSERT INTO accounts_summary (client_category, client_id, cr_cpr_number, account_number, account_name) VALUES ('I', @record_identity, @client_cpr, SCOPE_IDENTITY(), @client_name)
SET @account_number_string = RIGHT('0' + convert(varchar(1), DATEPART(WEEKDAY, GETDATE())), 1)
SET @account_number_string = @account_number_string + RIGHT('000' + convert(varchar(3), DATEPART(DAYOFYEAR, GETDATE())), 3)
SET @account_number_string = @account_number_string + RIGHT('0000000' + convert(varchar(7), SCOPE_IDENTITY()), 7)
SET @account_number = convert(bigint, @account_number_string)
UPDATE accounts_summary SET account_number = @account_number WHERE account_id = SCOPE_IDENTITY()
UPDATE clients SET account_number = @account_number WHERE client_id = @record_identity

[Andy_L_J]

A note, No one will even look at this question until you edit the sql so that it fits within the page.

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

[Jassim Rahma]

I tried this code but it's looping forever and adding the first record from the clients into the accounts_summary thougsands of times.. why?

USE takhlees

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

DELETE FROM accounts_summary

-- reset primary key

INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000001, 'CASH', 'True', 'True')
INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000002, 'ACCOUNTS RECEIVABLE', 'True', 'True')

DECLARE @client_id int, @client_cpr varchar(50), @client_name varchar(255), @account_number_string VARCHAR(50), @account_number numeric(38,0)

DECLARE account_numbers CURSOR FOR
SELECT clients.client_id, clients.client_cpr, clients.client_name
FROM clients
ORDER BY clients.client_name

OPEN account_numbers

FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO accounts_summary (client_category, client_id, cr_cpr_number, account_number, account_name) VALUES ('I', @client_id, @client_cpr, SCOPE_IDENTITY(), @client_name)
-- get random from 1 to 7
SET @account_number_string = RIGHT('0' + convert(varchar(1), DATEPART(WEEKDAY, GETDATE())), 1)
-- get random from 1 to 365
SET @account_number_string = @account_number_string + RIGHT('000' + convert(varchar(3), DATEPART(DAYOFYEAR, GETDATE())), 3)
SET @account_number_string = @account_number_string + RIGHT('0000000' + convert(varchar(7), SCOPE_IDENTITY()), 7)
SET @account_number = convert(bigint, @account_number_string)
UPDATE accounts_summary SET account_number = @account_number WHERE account_id = SCOPE_IDENTITY()
UPDATE clients SET account_number = @account_number WHERE client_id = @client_id

FETCH NEXT FROM account\_numbers INTO @client\_id, @client\_cpr, @client\_name

END

CLOSE account_numbers
DEALLOCATE account_numbers