Data Pipeline SQL Queries

Account 

SELECT 
	id, 
	name, 
	country_alpha2Code, 
	city, 
	region, 
	postalCode, 
	taxID, 
	phone, 
	fax, 
	logo, 
	modified, 
	modifiedBy_id, 
	created, 
	createdBy_id,
	customField01,
	customField02,
	customField03,
	customField04,
	customField05,
	customField06,
	customField07,
	customField08,
	customField09,
	customField10
from organization where modified between DATE_SUB('${TIME_NOW}',INTERVAL ${INTERVAL} MINUTE) and '${TIME_NOW}' order by modified ASC


Catalog Item

SELECT 
	identifier,
	name,
	type,
	status,
	priceCurrency,
	priceAmount 
FROM v_catalogitem


Category

SELECT 
	identifier,
	name
FROM v_category


Contact

SELECT
v_contact.id,
v_contact.email,
v_contact.firstName,
v_contact.lastName,
v_contact.street1,
v_contact.street2,
v_contact.city,
v_contact.region,
v_contact.postalCode,
v_contact.country_alpha2code,
v_contact.birthDate,
v_contact.title,
v_contact.department,
v_contact.salutation,
v_contact.officephone,
v_contact.mobilephone,
v_contact.homephone,
v_contact.fax,
v_contact.emailverified,
v_contact.enabled,
v_contact.staffEnabled,
v_contact.taxID,
v_contact.lastPasswordReset,
v_contact.lastSuccessfulLogin,
v_contact.name,
v_contact.numberOfSuccessfulLogins,
v_contact.numberOfForumPosts,
v_contact.created,
v_contact.organization_id,
contact.landingUrl,
contact.referrerUrl,
contact.provider_id,
contact.expertStatus,
contact.customField01,
contact.customField02,
contact.customField03,
contact.customField04,
contact.customField05,
contact.customField06,
contact.customField07,
contact.customField08,
contact.customField09,
contact.customField10,
contact.externalId,
contact.receivePromotionalOffers,
contact.receiveNewsletter,
contact.openId
from v_contact 
left join contact on v_contact.id=contact.id
where modified between DATE_SUB('${TIME_NOW}',INTERVAL ${INTERVAL} MINUTE) and '${TIME_NOW}' order by modified ASC


Course

select 
	id,
	name,
	type 
from v_course


Enrollment

SELECT 
	v_enrollment.status,
	v_enrollment.completionDate,
	v_enrollment.expiryDate,
	v_enrollment.score,
	v_enrollment.totalSecondsTracked,
	v_enrollment.confidence,
	v_enrollment.id,
	v_enrollment.component_id,
	v_enrollment.contact_id,
	v_enrollment.event_id,
	v_enrollment.created,
	v_enrollment.modified,
	v_enrollment.provider_id,
	v_enrollment.orderitem_id,
    enrolment.top_id,
    enrolment.parent_id,
    enrolment.customField01,
    enrolment.customField02,
    enrolment.customField03,
    enrolment.customField04,
    enrolment.customField05,
    enrolment.customField06,
    enrolment.customField07,
    enrolment.customField08,
    enrolment.customField09,
    enrolment.customField10,
	enrolment.uuid
from v_enrollment
left join enrolment 
on v_enrollment.id=enrolment.id
where v_enrollment.modified between DATE_SUB('${TIME_NOW}',INTERVAL ${INTERVAL} MINUTE) and '${TIME_NOW}' order by modified ASC


Event

SELECT
v_class.capacity,
v_class.course_id,
v_class.id,
v_class.location_id,
v_class.totalHours,
courseevent.provider_id,
v_class.type,
v_class.language,
v_class.equipmentProvided,
v_class.refreshmentsProvided,
v_class.foodProvided,
v_class.snacksProvided,
v_class.startTime,
v_class.status,
courseevent.sessionUrl,
DATE(MAX(eventschedule.endTime)) AS 'end',
TIME(MAX(eventschedule.startTime)) AS 'start',
eventschedule.durationInMinutes,
event.modified,
courseevent.courseVersion,
event.notes,
publiccourseevent.regularPriceAmount,
publiccourseevent.regularPriceCurrency,
event.customField01,
event.customField02,
event.customField03,
event.customField04,
event.customField05,
event.customField06,
event.customField07,
event.customField08,
event.customField09,
event.customField10
FROM v_class 
left join courseevent on v_class.id=courseevent.id
left join event on v_class.id=event.id
left join eventschedule on v_class.id=eventschedule.event_id
left join publiccourseevent on v_class.id=publiccourseevent.id
WHERE event.modified between DATE_SUB('${TIME_NOW}',INTERVAL ${INTERVAL} MINUTE) and '${TIME_NOW}' GROUP BY v_class.id


Instructor

SELECT 
courseevent_instructor.courseevent_id,
courseevent_instructor.contact_id,
event.modified
FROM courseevent_instructor
LEFT JOIN event ON courseevent_instructor.courseevent_id=event.id
WHERE modified between DATE_SUB('${TIME_NOW}',INTERVAL ${INTERVAL} MINUTE) and '${TIME_NOW}' order by modified ASC


Lead

SELECT 
	v_lead.id,
	v_lead.status,
	v_lead.created,
	v_lead.contact_id,
	v_lead.subject,
	v_lead.quantity,
requestforcontact.modified
FROM v_lead left join requestforcontact on v_lead.id=requestforcontact.id where modified between DATE_SUB(NOW(),INTERVAL ${INTERVAL} day) and NOW() order by modified ASC


Learning Component

SELECT
v_learningcomponent.id,
v_learningcomponent.name,
v_learningcomponent.language,
v_learningcomponent.type,
learningcomponent.modified,
learningcomponent.expiryDays,
learningcomponent.customField01,
learningcomponent.customField02,
learningcomponent.customField03,
learningcomponent.customField04,
learningcomponent.customField05,
learningcomponent.customField06,
learningcomponent.customField07,
learningcomponent.customField08,
learningcomponent.customField09,
learningcomponent.customField10
FROM v_learningcomponent 
left join learningcomponent on v_learningcomponent.id=learningcomponent.id 
WHERE modified between DATE_SUB('${TIME_NOW}',INTERVAL ${INTERVAL} MINUTE) and '${TIME_NOW}' order by modified ASC


Location

SELECT 
	v_location.id,
	v_location.city,
	v_location.region,
	v_location.country_alpha2Code,
	v_location.postalCode,
	v_location.name,
	v_location.timezone,
	v_location.online,
location.modified
FROM v_location left join location on v_location.id=location.id where modified between DATE_SUB(NOW(),INTERVAL ${INTERVAL} day) and NOW() order by modified ASC


Order

SELECT
	id,
	status,
	contact_id,
	account_id,
	payment_id,
	created,
	createdBy_id,
	modified,
	modifiedBy_id,
	total 
FROM v_order where modified between DATE_SUB(NOW(),INTERVAL ${INTERVAL} day) and NOW() order by modified ASC


Order Item

SELECT
v_orderitem.status,
v_orderitem.paid,
v_orderitem.priceAmount,
v_orderitem.discount,
v_orderitem.taxAmount,
v_orderitem.total,
v_orderitem.trainingCredits,
v_orderitem.id,
v_orderitem.target,
v_orderitem.contactId,
v_orderitem.organization_id,
v_orderitem.category,
v_orderitem.payment_id,
v_orderitem.order_id,
v_orderitem.created,
v_orderitem.provider_id,
v_orderitem.valueAmount,
v_orderitem.valueCurrency,
orderitem.targetType,
orderitem.targetId
FROM v_orderitem
left join orderitem 
on v_orderitem.id=orderitem.id
WHERE orderitem.modified between DATE_SUB('${TIME_NOW}',INTERVAL ${INTERVAL} MINUTE) and '${TIME_NOW}' order by orderitem.modified ASC


Payment

SELECT
	v_payment.id,
	v_payment.firstName,
	v_payment.lastName,
	v_payment.organization,
	v_payment.taxId,
	v_payment.taxcountry_alpha2Code,
	v_payment.phone,
	v_payment.email,
	v_payment.currency,
	v_payment.failureMessage,
	v_payment.status,
	v_payment.street1,
	v_payment.street2,
	v_payment.city,
	v_payment.region,
	v_payment.postalCode,
	v_payment.country_alpha2Code,
	v_payment.date,
	v_payment.number,
	v_payment.type,
	v_payment.amount,
	v_payment.order_id,
	v_payment.created,
	v_payment.valueAmount,
	v_payment.valueCurrency,
payment.modified  
FROM v_payment left join payment on v_payment.id=payment.id where modified between DATE_SUB(NOW(),INTERVAL ${INTERVAL} day) and NOW() order by modified ASC


Provider

SELECT 
	id,
	name
FROM v_provider


Training Credit Account

SELECT
	id,
	name,
	created,
	balance
FROM v_trainingcreditaccount


Training Credit Expiry

SELECT
	account_id,
	expiry,
	amount
FROM v_trainingcreditexpiry


Training Credit Redemption Request

SELECT 
	amount,
	id,
	status,
	account_id,
	order_id,
	requester_id,
	payment_id,
	created,
	expiry
FROM v_trainingcreditredemptionrequest


Training Credit Transaction

SELECT
	amount,
	reconciled,
	account_id,
	created,
	valueAmount,
	valueCurrency
FROM v_trainingcredittransaction

Course Requirement

select
courserequirement.id,
courserequirement.component_id,
courserequirement.pathway_id,
courserequirement.name AS 'component_name',
coursepathwaytype.name AS 'type'
from courserequirement 
left join coursepathway on coursepathway.id=courserequirement.pathway_id
left join coursepathwaytype on coursepathwaytype.id=coursepathway.type_id
where courserequirement.modified between DATE_SUB(NOW(),INTERVAL ${INTERVAL} hour) and NOW()



ERD Diagram