Random Booking Data
Updated: Nov 2, 2022
As mentioned in the previous post, I can generate random data using T-SQL or Python. I have decided to do the work in Python and incorporate that into a T-SQL stored procedure. I have wrote Python code to
Pick Random Customer ID
Pick Random Booking Date by adding a random number of days onto 1/1/2018 to current date
Pick Random lead time in days and add to Booking Date to create Depart Date
Pick Random stay duration either 7 or 14 days and add to Depart Date for a Return Date
Pick Random Sales revenue between £1000 and £4000
Pick Random number of passengers between 1 and 4
Pick Random number between 1 and 5 and map to a resort
This python code also includes a loop so I can specify how many to create and this is added via a Python dictionary into a Pandas DataFrame.
I have added this Python code into a T-SQL stored procedure called
sp_GetBookingDataFromRandomiser
and added a parameter to pass in the number of bookings I want it generate.
I can call this procedure passing in the parameter as part of an Insert Exec statement to put the data in my tblBookings table. I have now generated over 14000 bookings. Here is the stored procedure code.
CREATE PROCEDURE sp_GetBookingDataFromRandomiser
@HowManyBookings INT
AS
BEGIN
EXEC sp_execute_external_script @language=N'Python', @script =N'
import random
import datetime
import pandas as pd
booking = pd.DataFrame()
def getbooking():
rand_id = random.randint(10000,10999) # customer id
rand_bookingdate = random.randint(1,1426) # days since 1/1/2018
rand_sales = random.randint(1000,4000) # sales revenue
rand_leadtime = random.randint(100,300) # days to add onto bookingdate as departdate
rand_duration = random.randint(1,2) * 7 # days to add onto depart date as returndate
rand_passengers = random.randint(1,4) # passengers on booking
rand_resort = random.randint(1,5) # random pick of resort
bookingdate = datetime.datetime.strptime("1/1/18", "%d/%m/%y")
bookingdate = bookingdate + datetime.timedelta(days=rand_bookingdate)
departdate = bookingdate + datetime.timedelta(days=rand_leadtime)
returndate = departdate + datetime.timedelta(days=rand_duration)
dict = {"id": rand_id,"bookingdate":bookingdate,"departdate":departdate,"returndate":returndate,"passengers":rand_passengers,"sales":rand_sales,"resort":rand_resort}return(dict)for x in range(0,HowMany):
booking = booking.append(getbooking(), ignore_index=True)
BookingData = booking
',
@output_data_1_name = N'BookingData',
@params = N'@HowMany INT',
@HowMany = @HowManyBookings
WITH RESULT SETS ((BookingDate DATETIME, DepartDate DATETIME, CustomerID INT, Passengers INT, ResortID INT, ReturnDate DATETIME, SalesAmount MONEY)) END
I call this procedure to populate my bookings table as follows
INSERT INTO tblBookings (BookingDate, DepartDate, CustomerID, PassengerCount, ResortID, ReturnDate, BookingAmount)
EXEC sp_GetBookingDataFromRandomiser 3000
UPDATE tblBookings SET LeadTime = DATEDIFF(DD,BookingDate,DepartDate) WHERE LeadTime IS NULL
I am updating the LeadTime column in my table with difference in days between Booking Date and Depart Date.

Comments