Generate Random Transaction Date

tjamal1

Hello, 

I have a table called Customer which contains customerID, I want to generate 1000 records transaction Data in this pattern

First Transaction every customer must be  credit Type

Then other transaction should be random  (either debit or credit) (depends on balance)

Credit means -> Add points

Debit means -> Deduct points from balance

Balance -> Points after every transaction 

Every Customer should contain -> 2 to 15 transactions

Here is the sample desired output for one customer 

TransactionID	CustomerID	TransactionType	Points	Balance
1	1	Credit	3500	3500
2	1	Debit	200	3300
3	1	Debit	100	3200
4	1	Debit	400	2800
5	1	Debit	600	2200
6	1	Credit	200	2400
7	1	Debit	50	2350

RolandSchubert

Of course thre is a way. To get points as a multiple of 50, simply replace RandInt(n) by RandInt(n) * 50 (the MAX() formula is there to ensure, that no 0 values are generated). Transaction Dates can be added by using DateTime functions (DateTimeToday gives the current date, using DateTimeAdd a number of days can added).

RandomData.yxmd