Create Sample Data Script

--script 1

--create test data

USE DerivcoTestQ1

CREATE TABLE #FirstnamesTemp
 (
 Firstname VARCHAR(50) NOT NULL
 )

INSERT INTO #FirstnamesTemp
 SELECT DISTINCT TOP 49
 firstname
 FROM ZOKApplicationManager.dbo.Applicant_Applicant
 WHERE firstname IS NOT NULL
INSERT INTO #FirstnamesTemp ( firstname )
VALUES ( 'Tom' )


CREATE TABLE #LastnamesTemp
 (
 Lastname VARCHAR(50) NOT NULL
 )

INSERT INTO #LastnamesTemp
 SELECT DISTINCT TOP 49
 lastname
 FROM ZOKApplicationManager.dbo.Applicant_Applicant
 WHERE lastname IS NOT NULL
INSERT INTO #LastnamesTemp ( Lastname )
VALUES ( 'Tom' )

SELECT firstname,
 lastname,
 UPPER(RTRIM(LTRIM(firstname)) + RTRIM(LTRIM(lastname))) AS userlogin
INTO #names
FROM #Firstnamestemp
 CROSS JOIN #lastnamestemp 

CREATE TABLE #TempData
 (
 ID BIGINT IDENTITY(1, 1)
 NOT NULL,
 Firstname VARCHAR(50) NOT NULL,
 Lastname VARCHAR(50) NOT NULL,
 Userlogin VARCHAR(50) NOT NULL,
 Balance MONEY NULL
 )


INSERT INTO #TempData
 (
 firstname,
 lastname,
 userlogin
 )
 SELECT firstname,
 lastname,
 userlogin
 FROM #names

DECLARE @counter INT
SET @counter = 0
WHILE @counter < ( SELECT COUNT(*)
 FROM #Tempdata
 )
 BEGIN
 SET @counter = @counter + 1
 UPDATE #tempdata
 SET balance = ( SELECT CONVERT(MONEY, RAND() * 10000) Random_Number
 )
 WHERE balance IS NULL
 AND ID = @counter
 
 END
GO

INSERT INTO dbo.[user]
 (
 firstname,
 lastname,
 userlogin,
 balance
 )
 SELECT firstname,
 lastname,
 userlogin,
 balance
 FROM #tempdata

DROP TABLE #firstnamestemp
DROP TABLE #lastnamestemp
DROP TABLE #names
DROP TABLE #tempdata

SELECT *
FROM dbo.[user]

CREATE TABLE #AccountsTemp
 (
 id BIGINT IDENTITY(1, 1)
 NOT NULL,
 institution VARCHAR(50) NOT NULL,
 accountnumber VARCHAR(50) NULL
 )

INSERT INTO #AccountsTemp
 (
 institution,
 accountnumber
 )
 SELECT bankname,
 RTRIM(LTRIM(LEFT(UPPER(bankname), 3)))
 FROM zokapplicationmanager.dbo.lookup_bank 

--select * from #AccountsTemp

UPDATE #AccountsTemp
SET accountnumber = accountnumber + CONVERT(VARCHAR, id)

INSERT INTO dbo.Account
 (
 institution,
 accountnumber
 )
 SELECT institution,
 accountnumber
 FROM #accountstemp

DROP TABLE #AccountsTemp 


DECLARE @counter INT
SET @counter = 0
WHILE @counter < 30
 BEGIN
 DECLARE @noofaccounts INT
 DECLARE @accountid INT
 DECLARE @userid INT
 DECLARE accountid_cursor CURSOR
 FOR SELECT account.accountid
 FROM dbo.account
 OPEN accountid_cursor
 FETCH NEXT FROM accountid_cursor INTO @accountid
 WHILE @@Fetch_Status = 0
 BEGIN 



 SET @noofaccounts = ( SELECT COUNT(*)
 FROM dbo.UserAccountMap
 WHERE UserAccountMap.AccountID = @accountid
 )
 SET @userid = CONVERT(BIGINT, RAND() * 2500)
 IF @noofaccounts < 30 
 BEGIN
 
 SELECT UserAccountMap.userid
 FROM dbo.UserAccountMap
 WHERE UserAccountMap.AccountID = @accountid
 AND UserAccountMap.UserID = @userid
 IF @@Rowcount = 0 
 BEGIN
 INSERT INTO dbo.useraccountmap ( accountid, userid )
 VALUES ( @accountid, @userid )
 END
 END
 ELSE 
 IF @accountid % 2 = 0 
 BEGIN
 SELECT UserAccountMap.userid
 FROM dbo.UserAccountMap
 WHERE UserAccountMap.AccountID = @accountid
 AND UserAccountMap.UserID = @userid
 IF @@Rowcount = 0 
 BEGIN
 INSERT INTO dbo.useraccountmap ( accountid, userid )
 VALUES ( @accountid, @userid )
 SET @counter = @noofaccounts 
 END 
 END

 FETCH accountid_cursor INTO @accountid


 END 

 CLOSE accountid_cursor
 DEALLOCATE accountid_cursor 
 END

SELECT *
FROM dbo.UserAccountMap

--script 2

USE DerivcoTestQ2_5

--datagen for questions 2-5

--insert users
INSERT INTO [User]
 SELECT userlogin,
 firstname,
 lastname,
 balance
 FROM derivcotestq1.dbo.[user]


--insert accounttype
INSERT INTO accounttype ( accounttypename )
VALUES ( 'Big Spender' )
INSERT INTO accounttype ( accounttypename )
VALUES ( 'Moderate Spender' )
INSERT INTO accounttype ( accounttypename )
VALUES ( 'Small Spender' )
INSERT INTO accounttype ( accounttypename )
VALUES ( 'Erratic Spender' )



--insert account
DECLARE @Accounttypeid BIGINT 
DECLARE @counter INT
SET @counter = 0
WHILE @counter < ( SELECT COUNT(*)
 FROM derivcotestq1.dbo.account
 )
 BEGIN
 SET @counter = @counter + 1
 SET @Accounttypeid = ( SELECT CONVERT(BIGINT, RAND() * 4) + 1
 )

 INSERT INTO account
 (
 accountnumber,
 institution,
 accounttypeid
 )
 SELECT accountnumber,
 institution,
 @Accounttypeid
 FROM derivcotestq1.dbo.account
 WHERE accountid = @counter
 END
go

--insert user accountmap

INSERT INTO useraccountmap ( userid, accountid )
 SELECT userid,
 accountid
 FROM derivcotestq1.dbo.useraccountmap
go
--select * from account


--insert processusertype
INSERT INTO processusertype ( processusertypename )
VALUES (
 'Transaction Authoriser'
 )
INSERT INTO processusertype ( processusertypename )
VALUES (
 'Transaction Deleter'
 )
INSERT INTO processusertype ( processusertypename )
VALUES (
 'Transaction Updater'
 )
go

-- insert processuser
DECLARE @processusertypeid BIGINT
DECLARE @userlogin VARCHAR(50)
DECLARE @processuserlogin VARCHAR(50)
DECLARE @processuserid VARCHAR(3)
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 50
 BEGIN
 SET @counter = @counter + 1
 SET @processusertypeid = ( SELECT CONVERT(BIGINT, RAND() * 3) + 1
 )
 SET @userlogin = ( SELECT userlogin
 FROM [user]
 WHERE userid = CONVERT(BIGINT, RAND() * 2500)
 )
 SET @processuserid = CONVERT(VARCHAR, ( SELECT ISNULL(MAX(processuserid), 0)
 + 1
 FROM processuser
 ))
 SET @processuserlogin = ( SELECT CASE @processusertypeid
 WHEN 1
 THEN 'TranAuth' + @userlogin
 + @processuserid
 WHEN 2
 THEN 'TranDel' + @userlogin
 + @processuserid
 WHEN 3
 THEN 'TranUp' + @userlogin
 + @processuserid
 END
 )

 INSERT INTO processuser
 (
 processuserlogin,
 processusertypeid
 )
 VALUES (
 UPPER(@processuserlogin),
 @processusertypeid
 ) 


 END
GO

--insert game data
CREATE TABLE #GameNames
 (
 GameID BIGINT IDENTITY(1, 1)
 NOT NULL,
 Gamenames VARCHAR(50) NOT NULL
 )

INSERT INTO #GameNames ( Gamenames )
VALUES ( ' Poker' )
INSERT INTO #GameNames ( Gamenames )
VALUES ( ' Yatzee' )
INSERT INTO #GameNames ( Gamenames )
VALUES ( ' Rummy' )
INSERT INTO #GameNames ( Gamenames )
VALUES ( ' Blackjack' )
INSERT INTO #GameNames ( Gamenames )
VALUES ( ' Craps' )
INSERT INTO #GameNames ( Gamenames )
VALUES ( ' Spinner' )
INSERT INTO #GameNames ( Gamenames )
VALUES ( ' Bingo' )
INSERT INTO #GameNames ( Gamenames )
VALUES ( ' Keno' )
INSERT INTO #GameNames ( Gamenames )
VALUES ( ' Sports Bet' )
INSERT INTO #GameNames ( Gamenames )
VALUES ( ' Lotto' )

DECLARE @counter INT
SET @counter = 0
WHILE @counter < 30
 BEGIN
 SET @counter = @counter + 1
 INSERT INTO Game ( Gamename )
 SELECT CONVERT(VARCHAR(50), NEWID()) + ' ' + accountnumber
 + ' ' + #GameNames.Gamenames
 FROM account
 CROSS JOIN #GameNames
 END


DROP TABLE #Gamenames
go
--insert players

DECLARE @gameid BIGINT
DECLARE @userid BIGINT
DECLARE @GameCount BIGINT
DECLARE @EnableAccess INT
SET @GameCount = ( SELECT COUNT(*)
 FROM game
 )
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 100000
 BEGIN
 SET @counter = @counter + 1

 SET @userid = ( SELECT userid
 FROM [user]
 WHERE userid = CONVERT(BIGINT, RAND() * 2500) + 1
 )
 SET @gameid = ( SELECT gameid
 FROM game
 WHERE gameid = CONVERT(BIGINT, RAND() * @GameCount)
 + 1
 )
 SET @enableaccess = CONVERT(BIGINT, RAND() * 10)
 IF @enableaccess >= 5 
 BEGIN
 SET @enableaccess = 1
 END
 ELSE 
 BEGIN 
 SET @enableaccess = 0
 END

 IF @userid != 0
 AND @gameid != 0 
 BEGIN
 SELECT userid
 FROM player
 WHERE gameid = @gameid
 AND userid = @userid
 IF @@rowcount = 0 
 BEGIN
 INSERT INTO player
 (
 gameid,
 userid,
 accessenabled
 )
 VALUES (
 @gameid,
 @userid,
 @enableaccess
 ) 
 END
 END

 END
go
--insert transactions

DECLARE @userid BIGINT
DECLARE @Amount MONEY
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 100000
 BEGIN
 SET @userid = CONVERT(BIGINT, RAND() * 2500) + 1
 SET @Amount = CONVERT(MONEY, RAND() * 1000) + 1
 IF CONVERT(BIGINT, @Amount) = 888 
 BEGIN
 SET @Amount = 1000000
 END
 ELSE 
 IF CONVERT(BIGINT, @Amount) = 777 
 BEGIN
 SET @Amount = 100000
 END
 ELSE 
 IF CONVERT(BIGINT, @Amount) % 333 = 0 
 BEGIN
 SET @amount = 10000
 END
 ELSE 
 IF CONVERT(BIGINT, @Amount) % 5 = 0
 AND CONVERT(BIGINT, @Amount) > 800 
 BEGIN
 SET @amount = 1000
 END

 INSERT INTO [transaction] ( amount, userid )
 VALUES ( @amount, @userid )
 SET @Counter = @counter + 1

 END

go

--insert transaction logs 
DECLARE @processuserid BIGINT
DECLARE @maxprocessuserid BIGINT
SET @maxprocessuserid = ( SELECT MAX(processuserid)
 FROM processuser
 )
DECLARE @transactionid BIGINT
DECLARE @Transactionstatus TINYINT
DECLARE @MaxTransactions BIGINT
SET @MaxTransactions = ( SELECT MAX(transactionid)
 FROM [Transaction]
 )
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 10000
 BEGIN

 SET @TransactionID = CONVERT(BIGINT, RAND() * @MaxTransactions) + 1
 SET @processuserid = CONVERT(BIGINT, RAND() * @maxprocessuserid) + 1
 SET @Transactionstatus = CONVERT(TINYINT, RAND() * 10) 
 IF @Transactionstatus <= 7 
 BEGIN
 SET @TransactionStatus = 1
 END
 ELSE 
 BEGIN
 SET @TransactionStatus = 2
 END 

 SELECT processuserid
 FROM processuser
 WHERE processuserid = @processuserid
 AND processusertypeid != 2
 IF @@rowcount = 1 
 BEGIN

 SELECT transactionstatus
 FROM transactionlog
 WHERE transactionid = @transactionid
 AND transactionstatus = @transactionStatus 

 IF @@Rowcount = 0 
 BEGIN
 EXEC AddTransactionLog @TransactionID, @ProcessUserID,
 @TransactionStatus
 END

 SET @Counter = @Counter + 1
 END

 END