TODO

Sample Data - Profiler Source System

Purpose

Generate sample data using a SQL insert script.

Motivation

Provide sample data to run the generated code against.

Applicability

• All design metadata

Design Pattern

N/A

Schema Type

N/A

Output Type

• SQL

Implementation guidelines

This template provides a sample data set for the ‘PROFILER’ system to facilitate running generating code with real data.

Considerations and consequences

N/A.

Extensions

N/A.

Code

/*
  Sample data for the PROFILER operational ('source') system.
*/

/* Create the sample content */
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_PERSONAL] ([CustomerID], [Given], [Surname], [Suburb], [State], [Postcode], [Country], [Pronoun], [DOB], [Referee_Offer_Made], [Valid_From]) VALUES(235892, N'Simon', N'Someone', N'Sydney', N'NSW', N'1000', N'Australia', N'M', CAST(N'1960-12-10' AS Date), 1, CAST(N'2023-01-01' AS Date))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_PERSONAL] ([CustomerID], [Given], [Surname], [Suburb], [State], [Postcode], [Country], [Pronoun], [DOB], [Referee_Offer_Made], [Valid_From]) VALUES(258279, N'Jason', N'Doe', N'Indooropilly', N'QLD', N'4000', N'Australia', N'M', CAST(N'1980-01-04' AS Date), 1, CAST(N'2023-01-01' AS Date))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_PERSONAL] ([CustomerID], [Given], [Surname], [Suburb], [State], [Postcode], [Country], [Pronoun], [DOB], [Referee_Offer_Made], [Valid_From]) VALUES(321799, N'Julie', N'Sray', N'London', N'N/A', N'0000', N'UK', N'M', CAST(N'1951-01-04' AS Date), 1, CAST(N'2023-01-01' AS Date))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_PERSONAL] ([CustomerID], [Given], [Surname], [Suburb], [State], [Postcode], [Country], [Pronoun], [DOB], [Referee_Offer_Made], [Valid_From]) VALUES(683492, N'Mary', N'Smith', N'Bulimba', N'QLD', N'3000', N'Australia', N'F', CAST(N'1977-04-12' AS Date), 0, CAST(N'2023-01-01' AS Date))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_PERSONAL] ([CustomerID], [Given], [Surname], [Suburb], [State], [Postcode], [Country], [Pronoun], [DOB], [Referee_Offer_Made], [Valid_From]) VALUES(885325, N'Michael', N'Evans', N'Bourke', N'NWS', N'2000', N'Australia', N'M', CAST(N'1985-04-19' AS Date), 0, CAST(N'2023-01-01' AS Date))
-- Backdated adjustment example, first row
--INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_PERSONAL] ([CustomerID], [Given], [Surname], [Suburb], [State], [Postcode], [Country], [Pronoun], [DOB], [Contact_Number], [Referee_Offer_Made], [Valid_From]) VALUES(100000, N'Jonathan', NULL, N'London', N'N/A', N'0000', N'UK', N'M', CAST(N'1951-01-04' AS Date), 123, 1, CAST(N'2010-02-01' AS Date))
--
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_CONTACT] ([CustomerID], [Contact_Number], [Valid_From]) VALUES(235892, 9874634,CAST(N'2023-01-01' AS Date))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_CONTACT] ([CustomerID], [Contact_Number], [Valid_From]) VALUES(258279, 41234, CAST(N'2023-01-01' AS Date))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_CONTACT] ([CustomerID], [Contact_Number], [Valid_From]) VALUES(321799, 23555, CAST(N'2023-01-01' AS Date))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_CONTACT] ([CustomerID], [Contact_Number], [Valid_From]) VALUES(683492, 41234, CAST(N'2023-01-01' AS Date))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_CONTACT] ([CustomerID], [Contact_Number], [Valid_From]) VALUES(885325, 89235, CAST(N'2023-01-01' AS Date))

INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[OFFER] ([OfferID], [Offer_Long_Description]) VALUES(450, N'20% off all future purchases')
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[OFFER] ([OfferID], [Offer_Long_Description]) VALUES(462, N'10% off all future purchases')
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[OFFER] ([OfferID], [Offer_Long_Description]) VALUES(469, N'Free movie tickets')
--
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[PLAN] ([Plan_Code], [Plan_Desc], [Renewal_Plan_Code]) VALUES(N'AVG', N'Average / Mix plan', 'SUPR')
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[PLAN] ([Plan_Code], [Plan_Desc], [Renewal_Plan_Code]) VALUES(N'HIGH', N'Highroller / risk embracing', 'SUPR')
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[PLAN] ([Plan_Code], [Plan_Desc], [Renewal_Plan_Code]) VALUES(N'LOW', N'Risk avoiding', 'MAXM')
--
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUST_MEMBERSHIP] ([CustomerID], [Plan_Code], [Start_Date], [End_Date], [Status], [Comment]) VALUES(235892, N'HIGH', CAST(N'2012-05-12T00:00:00.000' AS DateTime), CAST(N'2015-12-31T00:00:00.000' AS DateTime), N'High', N'Trial')
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUST_MEMBERSHIP] ([CustomerID], [Plan_Code], [Start_Date], [End_Date], [Status], [Comment]) VALUES(321799, N'AVG', CAST(N'2010-01-01T00:00:00.000' AS DateTime), CAST(N'2014-10-28T00:00:00.000' AS DateTime), N'Open', N'None')
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUST_MEMBERSHIP] ([CustomerID], [Plan_Code], [Start_Date], [End_Date], [Status], [Comment]) VALUES(683492, N'LOW', CAST(N'2012-12-12T00:00:00.000' AS DateTime), CAST(N'2020-02-27T00:00:00.000' AS DateTime), N'Active', N'None')
--
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_OFFER] ([CustomerID], [OfferID]) VALUES(235892, 450)
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_OFFER] ([CustomerID], [OfferID]) VALUES(258279, 450)
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[CUSTOMER_OFFER] ([CustomerID], [OfferID]) VALUES(321799, 469)
--
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[ESTIMATED_WORTH] ([Plan_Code], [Date_effective], [Value_Amount]) VALUES(N'AVG', CAST(N'2016-06-06T00:00:00.000' AS DateTime), CAST(10 AS Numeric(18, 0)))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[ESTIMATED_WORTH] ([Plan_Code], [Date_effective], [Value_Amount]) VALUES(N'HIGH', CAST(N'2011-01-01T00:00:00.000' AS DateTime), CAST(1545000 AS Numeric(18, 0)))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[ESTIMATED_WORTH] ([Plan_Code], [Date_effective], [Value_Amount]) VALUES(N'LOW', CAST(N'2012-05-04T00:00:00.000' AS DateTime), CAST(450000 AS Numeric(18, 0)))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[ESTIMATED_WORTH] ([Plan_Code], [Date_effective], [Value_Amount]) VALUES(N'LOW', CAST(N'2013-06-19T00:00:00.000' AS DateTime), CAST(550000 AS Numeric(18, 0)))
--
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[PERSONALISED_COSTING] ([Member], [Segment], [Plan_Code], [Date_effective], [Monthly_Cost]) VALUES(258279, N'LOW', N'HIGH', CAST(N'2014-01-01T00:00:00.000' AS DateTime), CAST(150 AS Numeric(18, 0)))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[PERSONALISED_COSTING] ([Member], [Segment], [Plan_Code], [Date_effective], [Monthly_Cost]) VALUES(683492, N'HIGH', N'AVG', CAST(N'2013-01-01T00:00:00.000' AS DateTime), CAST(450 AS Numeric(18, 0)))
INSERT [{{conventions.sourceObjectDatastore}}].[{{conventions.sourceObjectLocation}}].[PERSONALISED_COSTING] ([Member], [Segment], [Plan_Code], [Date_effective], [Monthly_Cost]) VALUES(885325, N'MED', N'AVG', CAST(N'2013-01-01T00:00:00.000' AS DateTime), CAST(475 AS Numeric(18, 0)))