top of page

Random Booking Data

Nov 1, 2022
2 min read

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.

 
 
 

Recent Posts

See All
Welcome to DK Travel

To demonstrate some of my skills, I have made up a ficticious travel agency called DK Travel and they are going to be asking me for...

 
 
 
Some Initial Setup

Before I start answering requests from DK Travel, I need to set up an initial customer database and due to GDPR cannot use real data. I...

 
 
 
Case Study - Request 1

We at DK Travel would like to see some of our data displayed in a Tableau dashboard to allow us to get some basic insights such as :...

 
 
 

Comments


bottom of page