Useful queries
Country overlap (big boss query)
SELECT country.country, COUNT(DISTINCT affiliation.project_id)
FROM affiliation
INNER JOIN organisation ON affiliation.organisation_id = organisation.organisation_id
INNER JOIN itea_office.country ON organisation.country_id = country.country_id
WHERE affiliation.project_id IN (SELECT project_id FROM project_version WHERE type_id = 2 AND approved = 1)
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE programcall_id IN
(SELECT programcall_id FROM programcall WHERE program_id = 3))
AND affiliation.project_id IN (SELECT project_id
FROM affiliation
WHERE affiliation.organisation_id IN
(SELECT organisation_id FROM organisation WHERE organisation.country_id = 150))
GROUP BY country.country;
Active users in promotional events
SELECT *
FROM contact
WHERE contact_id IN (SELECT contact_id
FROM registration
INNER JOIN registration_desk
ON registration.registration_id = registration_desk.registration_id
WHERE meeting_id IN (11, 14, 15, 13, 10)
AND registration_desk.date_desk IS NOT NULL)
List of users in projects
SELECT contact_id
FROM contact
WHERE contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id = 17
AND project_id IN (SELECT project_id
FROM project_version))
OR contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 17
AND project_id IN (SELECT project_id
FROM project_version)))
OR contact_id IN (SELECT contact_id
FROM workpackage
WHERE workpackage.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 17
AND project_id IN (SELECT project_id
FROM project_version)))
OR contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 17
AND project_id IN
(SELECT project_id
FROM project_version))))
Amount of PL in a call
SELECT COUNT(*)
FROM contact
WHERE contact_id IN (SELECT contact_id
FROM project
WHERE project_id IN (SELECT project_id FROM project_version)
AND project.programcall_id = 18);
Amount of TC in a call
SELECT COUNT(*)
FROM contact
WHERE contact_id IN (SELECT affiliation.contact_id
FROM affiliation
JOIN project ON affiliation.project_id = project.project_id
WHERE project.project_id IN (SELECT project_id FROM project_version)
AND project.programcall_id = 18
AND affiliation.date_end IS NULL);
Amount of WPL in a call
SELECT COUNT(*)
FROM contact
WHERE contact_id IN (SELECT workpackage.contact_id
FROM workpackage
JOIN project ON workpackage.project_id = project.project_id
WHERE project.project_id IN (SELECT project_id FROM project_version)
AND project.programcall_id = 18);
Other contacts in a call
SELECT COUNT(*)
FROM contact
WHERE contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation.affiliation_id
FROM affiliation
JOIN project ON affiliation.project_id = project.project_id
WHERE project.project_id IN (SELECT project_id FROM project_version)
AND project.programcall_id = 18
AND affiliation.date_end IS NULL));
List of users which became visited the promotional events and were not active during the calls before
SELECT *
FROM contact
WHERE (
contact_id NOT IN (SELECT contact_id
FROM contact
WHERE contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id < 17)
OR contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id < 17))
OR contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN
(SELECT project_id
FROM project
WHERE project.programcall_id < 17))))
)
AND contact_id IN (SELECT contact_id
FROM registration
INNER JOIN registration_desk
ON registration.registration_id = registration_desk.registration_id
WHERE meeting_id IN (11, 14, 15, 13, 10)
AND registration_desk.date_desk IS NOT NULL)
List of new organisations by type in a call
SELECT COUNT(DISTINCT (affiliation.organisation_id)),
@call := programcall.programcall_id,
(SELECT COUNT(DISTINCT (affiliation.organisation_id))
FROM organisation
JOIN affiliation ON organisation.organisation_id = affiliation.organisation_id
JOIN project ON affiliation.project_id = project.project_id
WHERE project.programcall_id = @call
AND (affiliation.project_id IN (SELECT project_id
FROM project_version
WHERE type_id = 2
AND approved = 1) AND
affiliation.project_id NOT IN (SELECT project_id
FROM project_version
WHERE type_id = 4))
AND affiliation.date_end IS NULL
AND organisation.type_id = 3) as amountOfSME
FROM organisation
JOIN affiliation
ON organisation.organisation_id = affiliation.organisation_id
JOIN project ON affiliation.project_id = project.project_id
JOIN programcall ON project.programcall_id = programcall.programcall_id
WHERE (affiliation.project_id IN (SELECT project_id
FROM project_version
WHERE type_id = 2
AND approved = 1)
AND
affiliation.project_id NOT IN (SELECT project_id
FROM project_version
WHERE type_id = 4))
AND affiliation.date_end IS NULL
AND organisation.type_id = 3
AND affiliation.organisation_id NOT IN (SELECT affiliation.organisation_id
FROM organisation
JOIN affiliation
ON organisation.organisation_id = affiliation.organisation_id
JOIN project ON affiliation.project_id = project.project_id
WHERE project.programcall_id
< programcall.programcall_id)
GROUP BY programcall.programcall_id
List of users who visited the promotional events and are new
SELECT *
FROM contact
WHERE (
contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id = 17) OR
contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 17)) OR
contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 17)))
)
AND contact_id IN (SELECT contact_id
FROM registration
INNER JOIN registration_desk
ON registration.registration_id = registration_desk.registration_id
WHERE meeting_id IN (11, 14, 15, 13, 10)
AND registration_desk.date_desk IS NOT NULL)
AND contact_id NOT IN (SELECT contact_id
FROM contact
WHERE contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id < 17)
OR contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id < 17))
OR contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN
(SELECT project_id
FROM project
WHERE project.programcall_id < 17))))
List of active users in a call
SELECT *
FROM contact
WHERE (
contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id = 20) OR
contact_id IN (SELECT contact_id
FROM project_rationale
WHERE project_rationale.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20)) OR
contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20)) OR
contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20)))
)
List of active users in a call (with at least submitted project)
SELECT *
FROM contact
WHERE (
contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1)) OR
contact_id IN (SELECT contact_id
FROM project_rationale
WHERE project_rationale.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))) OR
contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))) OR
contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))))
)
List of active project leaders in a call
SELECT *
FROM contact
WHERE (
contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id = 20))
List of active project leaders in a call (with at least a submitted PO)
SELECT *
FROM contact
WHERE (
contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1)))
List of active technical contacts leaders in a call
SELECT DISTINCT *
FROM contact
WHERE (
contact_id IN
(SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20)));
List of active technical contacts leaders in a call (with at least a submitted PO)
SELECT DISTINCT *
FROM contact
WHERE (
contact_id IN
(SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))));
List of work package leaders in a call
SELECT *
FROM contact
WHERE (
contact_id IN
(SELECT contact_id
FROM workpackage
WHERE workpackage.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20)));
List of work package leaders in a call (with at least a submitted PO)
SELECT *
FROM contact
WHERE (
contact_id IN
(SELECT contact_id
FROM workpackage
WHERE workpackage.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))));
List of country coordinators in a call
SELECT *
FROM contact
WHERE (
contact_id IN
(SELECT contact_id
FROM project_rationale
WHERE project_rationale.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20)));
List of country coordinators in a call (with at least a submitted PO)
SELECT *
FROM contact
WHERE (
contact_id IN
(SELECT contact_id
FROM project_rationale
WHERE project_rationale.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))));
List of affiliations in a call
SELECT *
FROM contact
WHERE contact_id IN
(SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20)));
List of affiliations in a call (with at least a submitted PO)
SELECT *
FROM contact
WHERE contact_id IN
(SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))));
List of users who are new in the call
SELECT *
FROM contact
WHERE (
contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id = 20) OR
contact_id IN (SELECT contact_id
FROM project_rationale
WHERE project_rationale.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20)) OR
contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20)) OR
contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20)))
)
AND contact_id NOT IN (SELECT contact_id
FROM contact
WHERE contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id < 20)
OR contact_id IN (SELECT contact_id
FROM project_rationale
WHERE project_rationale.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id < 20))
OR contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id < 20))
OR contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN
(SELECT project_id
FROM project
WHERE project.programcall_id < 20))))
List of users who are new in the call (with at least a submitted PO and registered in 2016)
SELECT *
FROM contact
WHERE (
contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1)) OR
contact_id IN (SELECT contact_id
FROM project_rationale
WHERE project_rationale.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))) OR
contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))) OR
contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))))
)
AND contact_id NOT IN (SELECT contact_id
FROM contact
WHERE contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id < 20)
OR contact_id IN (SELECT contact_id
FROM project_rationale
WHERE project_rationale.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id < 20))
OR contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id < 20))
OR contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN
(SELECT project_id
FROM project
WHERE project.programcall_id < 20))))
AND YEAR(contact.date_created) = 2016;
List of organisations which visited the promotional event and are active in the call
SELECT *
FROM contact_organisation
JOIN organisation ON organisation.organisation_id = contact_organisation.organisation_id
WHERE contact_id IN
(SELECT contact_id
FROM contact
WHERE (
contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id = 17) OR
contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 17)) OR
contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 17)))
)
AND contact_id IN (SELECT contact_id
FROM registration
INNER JOIN registration_desk
ON registration.registration_id = registration_desk.registration_id
WHERE meeting_id IN (11, 14, 15, 13, 10)
AND registration_desk.date_desk IS NOT NULL))
GROUP BY organisation.organisation_id
List of PL and their activity in projects
SELECT contact.contact_id,
contact.email,
contact.firstname,
contact.middlename,
contact.lastname,
organisation.organisation,
country.country,
GROUP_CONCAT(project.project),
(SELECT COUNT(DISTINCT programcall.programcall_id)
FROM programcall
LEFT JOIN project ON programcall.programcall_id = project.programcall_id
LEFT JOIN project p2 ON programcall.programcall_id = p2.programcall_id
LEFT JOIN affiliation ON affiliation.project_id = p2.project_id
WHERE (project.contact_id = contact.contact_id)
OR (affiliation.contact_id = contact.contact_id)) AS nr_of_calls
FROM contact
JOIN project ON project.contact_id = contact.contact_id
JOIN contact_organisation ON contact_organisation.contact_id = contact.contact_id
JOIN organisation on organisation.organisation_id = contact_organisation.organisation_id
JOIN country ON organisation.country_id = country.country_id
WHERE project.programcall_id = 17
AND project.project_id IN
(SELECT project_version.project_id
FROM project_version
WHERE date_submitted IS NOT NULL
AND type_id = 1)
GROUP BY contact.contact_id
List of TC and their activity in projects
SELECT contact.contact_id,
contact.email,
contact.firstname,
contact.middlename,
contact.lastname,
organisation.organisation,
country.country,
GROUP_CONCAT(project.project),
(SELECT COUNT(DISTINCT programcall.programcall_id)
FROM programcall
LEFT JOIN project ON programcall.programcall_id = project.programcall_id
LEFT JOIN project p2 ON programcall.programcall_id = p2.programcall_id
LEFT JOIN affiliation ON affiliation.project_id = p2.project_id
WHERE (project.contact_id = contact.contact_id)
OR (affiliation.contact_id = contact.contact_id)) AS nr_of_calls
FROM contact
JOIN affiliation ON affiliation.contact_id = contact.contact_id
JOIN project ON affiliation.project_id = project.project_id
JOIN contact_organisation ON contact_organisation.contact_id = contact.contact_id
JOIN organisation ON organisation.organisation_id = contact_organisation.organisation_id
JOIN country ON organisation.country_id = country.country_id
WHERE project.programcall_id = 17
AND project.project_id IN
(SELECT project_version.project_id
FROM project_version
WHERE date_submitted IS NOT NULL
AND type_id = 1)
GROUP BY contact.contact_id
List of active contacts in a call and registered for a PO event (with at least a submitted PO)
SELECT *
FROM contact
WHERE (
contact_id IN (SELECT contact_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1)) OR
contact_id IN (SELECT contact_id
FROM project_rationale
WHERE project_rationale.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))) OR
contact_id IN (SELECT contact_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))) OR
contact_id IN (SELECT contact_id
FROM associate
WHERE associate.affiliation_id IN (SELECT affiliation_id
FROM affiliation
WHERE affiliation.date_end IS NULL
AND affiliation.project_id IN (SELECT project_id
FROM project
WHERE project.programcall_id = 20
AND project_id IN (SELECT project_id FROM project_version WHERE type_id = 1))))
)
AND contact_id IN (SELECT contact_id
FROM nda
JOIN programcall_nda ON programcall_nda.nda_id = nda.nda_id
WHERE programcall_nda.programcall_id = 20
AND nda.date_approved IS NOT NULL
AND nda.contact_id IN
(SELECT contact_id
FROM registration
WHERE overbooked = 0
AND date_end IS NULL
AND meeting_id = 32))
New visitors in session table
SELECT COUNT(DISTINCT (session.contact_id)) as totalVisits,
YEAR(date_start) AS yearOfVisit,
(SELECT COUNT(DISTINCT (session.contact_id))
FROM session
WHERE web_id IN (3, 4, 5)
AND YEAR(date_start) = yearOfVisit
AND contact_id NOT IN (SELECT contact_id FROM session WHERE YEAR(date_start) < yearOfVisit)) as newVisits
FROM session
JOIN contact
ON contact.contact_id = session.contact_id
WHERE web_id IN (3, 4, 5)
GROUP BY YEAR(date_start)
New visitors per event
SELECT COUNT(registration.contact_id) as totalVisits,
meeting.meeting_id as currentMeetingId,
meeting.date_from as currentMeetingDate,
registration.meeting_id,
meeting.meeting as meeting,
(SELECT COUNT(registration.contact_id)
FROM registration
JOIN
meeting ON meeting.meeting_id = registration.meeting_id
JOIN
registration_desk ON registration.registration_id = registration_desk.registration_id
WHERE meeting.meeting_id = currentMeetingId
and (registration_desk.date_desk is not null or meeting.type = 2)
and registration.date_end is null
and registration.overbooked = 0
AND contact_id NOT IN (SELECT contact_id
FROM registration
JOIN
meeting ON meeting.meeting_id = registration.meeting_id
JOIN
registration_desk
ON registration.registration_id = registration_desk.registration_id
WHERE (registration_desk.date_desk is not null or meeting.type = 2)
and registration.date_end is null
and registration.overbooked = 0
and meeting.date_from < currentMeetingDate)) as newVisitors
FROM registration
JOIN registration_desk ON registration.registration_id = registration_desk.registration_id
JOIN meeting ON registration.meeting_id = meeting.meeting_id
WHERE (registration_desk.date_desk is not null or meeting.type = 2)
and registration.date_end is null
and registration.overbooked = 0
GROUP BY registration.meeting_id
ORDER BY meeting.date_from;
Returning visitors per event
Make sure you desk all the participants of online events
SELECT COUNT(registration.contact_id) as totalVisits,
meeting.meeting_id as currentMeetingId,
meeting.date_from as currentMeetingDate,
registration.meeting_id,
meeting.meeting as meeting,
(SELECT COUNT(registration.contact_id)
FROM registration
JOIN
meeting ON meeting.meeting_id = registration.meeting_id
JOIN
registration_desk ON registration.registration_id = registration_desk.registration_id
WHERE meeting.meeting_id = currentMeetingId
and (registration_desk.date_desk is not null or meeting.type = 2)
and registration.date_end is null
and registration.overbooked = 0
AND contact_id IN (SELECT contact_id
FROM registration
JOIN
meeting ON meeting.meeting_id = registration.meeting_id
JOIN
registration_desk ON registration.registration_id = registration_desk.registration_id
WHERE (registration_desk.date_desk is not null or meeting.type = 2)
and registration.date_end is null
and registration.overbooked = 0
and meeting.date_from < currentMeetingDate)) as returningVisitors
FROM registration
JOIN registration_desk ON registration.registration_id = registration_desk.registration_id
JOIN meeting ON registration.meeting_id = meeting.meeting_id
WHERE (registration_desk.date_desk is not null or meeting.type = 2)
and registration.date_end is null
and registration.overbooked = 0
GROUP BY registration.meeting_id
ORDER BY meeting.date_from;
Returning organisations per event
SELECT COUNT(DISTINCT (contact_organisation.organisation_id)) as totalOrganisations,
meeting.meeting_id as currentMeetingId,
registration.meeting_id,
meeting.meeting as meeting,
(SELECT COUNT(DISTINCT (contact_organisation.organisation_id))
FROM registration
JOIN
registration_desk ON registration.registration_id = registration_desk.registration_id
JOIN
contact_organisation ON registration.contact_id = contact_organisation.contact_id
WHERE registration.meeting_id = currentMeetingId
and registration_desk.date_desk is not null
and registration.date_end is null
and registration.is_active = 1
and registration.overbooked = 0
AND registration.contact_id IN (SELECT contact_id
FROM registration
JOIN
registration_desk
ON registration.registration_id = registration_desk.registration_id
WHERE registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
and registration.meeting_id < currentMeetingId)) as returningOrganisations
FROM registration
JOIN registration_desk ON registration.registration_id = registration_desk.registration_id
JOIN contact_organisation ON registration.contact_id = contact_organisation.contact_id
JOIN meeting ON registration.meeting_id = meeting.meeting_id
WHERE registration_desk.date_desk is not null
and registration.date_end is null
and registration.is_active = 1
and registration.overbooked = 0
GROUP BY registration.meeting_id;
Returning organisations per event (on date)
SELECT COUNT(DISTINCT (contact_organisation.organisation_id)) as totalOrganisations,
meeting.date_from as currentMeetingDate,
meeting.meeting_id as currentMeetingId,
registration.meeting_id,
meeting.meeting as meeting,
(SELECT COUNT(DISTINCT (contact_organisation.organisation_id))
FROM registration
JOIN
meeting ON meeting.meeting_id = registration.meeting_id
JOIN
registration_desk ON registration.registration_id = registration_desk.registration_id
JOIN
contact_organisation ON registration.contact_id = contact_organisation.contact_id
WHERE meeting.meeting_id = currentMeetingId
and registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
AND registration.contact_id IN (SELECT contact_id
FROM registration
JOIN
meeting ON meeting.meeting_id = registration.meeting_id
JOIN
registration_desk
ON registration.registration_id = registration_desk.registration_id
WHERE registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
and meeting.date_from < currentMeetingDate)) as returningOrganisations
FROM registration
JOIN registration_desk ON registration.registration_id = registration_desk.registration_id
JOIN contact_organisation ON registration.contact_id = contact_organisation.contact_id
JOIN meeting ON registration.meeting_id = meeting.meeting_id
WHERE registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
GROUP BY registration.meeting_id
ORDER BY meeting.date_from;
Visitors also present in 1 of the 2 previous events
SELECT COUNT(registration.contact_id) as totalVisits,
meeting.meeting_id as currentMeetingId,
registration.meeting_id,
meeting.meeting as meeting,
(SELECT COUNT(registration.contact_id)
FROM registration
JOIN
registration_desk ON registration.registration_id = registration_desk.registration_id
WHERE registration.meeting_id = currentMeetingId
and registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
AND contact_id IN (SELECT contact_id
FROM registration
JOIN
registration_desk ON registration.registration_id = registration_desk.registration_id
WHERE registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
and registration.meeting_id < currentMeetingId
and registration.meeting_id >= (currentMeetingId - 2))) as returningVisitsAlsoPresentInOneOfThePastTwoEvents
FROM registration
JOIN registration_desk ON registration.registration_id = registration_desk.registration_id
JOIN meeting ON registration.meeting_id = meeting.meeting_id
WHERE registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
and meeting.meeting_id >= 3
GROUP BY registration.meeting_id;
New visits per country in events
SELECT COUNT(registration.contact_id) as totalVisits,
meeting.meeting_id as currentMeetingId,
registration.meeting_id,
meeting.meeting as meeting,
country.country,
country.country_id AS currentCountryId,
(SELECT COUNT(registration.contact_id)
FROM registration
JOIN registration_desk ON registration.registration_id = registration_desk.registration_id
JOIN contact ON registration.contact_id = contact.contact_id
JOIN contact_organisation on contact.contact_id = contact_organisation.contact_id
JOIN organisation ON organisation.organisation_id = contact_organisation.organisation_id
JOIN country ON organisation.country_id = country.country_id
WHERE registration.meeting_id = currentMeetingId
and registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
and country.country_id = currentCountryId
AND registration.contact_id NOT IN (SELECT registration.contact_id
FROM registration
JOIN registration_desk
ON registration.registration_id = registration_desk.registration_id
JOIN contact ON registration.contact_id = contact.contact_id
JOIN contact_organisation
on contact.contact_id = contact_organisation.contact_id
JOIN organisation
ON organisation.organisation_id = contact_organisation.organisation_id
WHERE registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
and registration.meeting_id < currentMeetingId
and organisation.country_id = currentCountryId)) as newVisitsPerCountry
FROM registration
JOIN registration_desk ON registration.registration_id = registration_desk.registration_id
JOIN meeting ON registration.meeting_id = meeting.meeting_id
JOIN contact ON registration.contact_id = contact.contact_id
JOIN contact_organisation on contact.contact_id = contact_organisation.contact_id
JOIN organisation ON organisation.organisation_id = contact_organisation.organisation_id
JOIN country ON organisation.country_id = country.country_id
WHERE registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
GROUP BY registration.meeting_id, country.country_id;
New visitors per country where a country has not been present before
SELECT COUNT(registration.contact_id) as totalVisits,
meeting.meeting_id as currentMeetingId,
registration.meeting_id,
meeting.meeting as meeting,
country.country,
country.country_id AS currentCountryId,
(SELECT COUNT(registration.contact_id)
FROM registration
JOIN registration_desk ON registration.registration_id = registration_desk.registration_id
JOIN contact ON registration.contact_id = contact.contact_id
JOIN contact_organisation on contact.contact_id = contact_organisation.contact_id
JOIN organisation ON organisation.organisation_id = contact_organisation.organisation_id
JOIN country ON organisation.country_id = country.country_id
WHERE registration.meeting_id = currentMeetingId
and registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
and country.country_id = currentCountryId
AND country.country_id NOT IN (SELECT organisation.country_id
FROM registration
JOIN registration_desk
ON registration.registration_id = registration_desk.registration_id
JOIN contact ON registration.contact_id = contact.contact_id
JOIN contact_organisation
on contact.contact_id = contact_organisation.contact_id
JOIN organisation
ON organisation.organisation_id = contact_organisation.organisation_id
WHERE registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
and registration.meeting_id < currentMeetingId)
AND registration.contact_id NOT IN (SELECT registration.contact_id
FROM registration
JOIN registration_desk
ON registration.registration_id = registration_desk.registration_id
JOIN contact ON registration.contact_id = contact.contact_id
JOIN contact_organisation
on contact.contact_id = contact_organisation.contact_id
JOIN organisation
ON organisation.organisation_id = contact_organisation.organisation_id
WHERE registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
and registration.meeting_id < currentMeetingId
and organisation.country_id = currentCountryId)) as newVisitsPerCountry
FROM registration
JOIN registration_desk ON registration.registration_id = registration_desk.registration_id
JOIN meeting ON registration.meeting_id = meeting.meeting_id
JOIN contact ON registration.contact_id = contact.contact_id
JOIN contact_organisation on contact.contact_id = contact_organisation.contact_id
JOIN organisation ON organisation.organisation_id = contact_organisation.organisation_id
JOIN country ON organisation.country_id = country.country_id
WHERE registration_desk.date_desk is not null
and registration.date_end is null
and registration.overbooked = 0
GROUP BY registration.meeting_id, country.country_id;
List of Project Leaders and Project Mentors Catrene
SELECT salutation,
firstname,
middlename,
lastname,
organisation,
country.country,
email,
project,
'leader'
FROM contact
JOIN contact_organisation ON contact.contact_id = contact_organisation.contact_id
JOIN organisation ON contact_organisation.organisation_id = organisation.organisation_id
JOIN country ON organisation.country_id = country.country_id
JOIN `general`.title ON title.title_id = contact.title_id
JOIN project ON project.contact_id = contact.contact_id
WHERE contact.contact_id IN (SELECT contact_id FROM project)
UNION
SELECT salutation,
firstname,
middlename,
lastname,
organisation,
country.country,
email,
project,
'mentor'
FROM contact
JOIN `general`.title ON title.title_id = contact.title_id
JOIN contact_organisation ON contact.contact_id = contact_organisation.contact_id
JOIN organisation ON contact_organisation.organisation_id = organisation.organisation_id
JOIN `general`.country ON organisation.country_id = country.country_id
JOIN project_review ON project_review.contact_id = contact.contact_id
JOIN project ON project_review.project_id = project.project_id
WHERE project.project_id IN (SELECT project_id FROM project_review WHERE type_id = 6)
Partners per call
SELECT COUNT(DISTINCT (organisation.organisation_id)), programcall.programcall_id
FROM affiliation
JOIN organisation ON organisation.organisation_id = affiliation.organisation_id
JOIN project ON affiliation.project_id = project.project_id
JOIN programcall ON project.programcall_id = programcall.programcall_id
WHERE (affiliation.project_id in (SELECT project_id FROM project_version WHERE type_id = 2 and approved = 1) AND
affiliation.project_id not in (SELECT project_id FROM project_version WHERE type_id = 4))
AND affiliation.date_end IS NULL
GROUP BY programcall.programcall_id
Partners in active projects per type
SELECT COUNT(DISTINCT (organisation.organisation_id)), organisation_type.type
FROM affiliation
JOIN organisation ON organisation.organisation_id = affiliation.organisation_id
JOIN organisation_type ON organisation_type.type_id = organisation.type_id
JOIN project ON affiliation.project_id = project.project_id
WHERE (affiliation.project_id in (SELECT project_id FROM project_version WHERE type_id = 2 and approved = 1) AND
affiliation.project_id not in (SELECT project_id FROM project_version WHERE type_id = 4))
AND affiliation.date_end IS NULL
GROUP BY organisation_type.type_id
Partners in a region
SELECT *
FROM organisation
JOIN contact_organisation ON organisation.organisation_id = contact_organisation.organisation_id
JOIN contact ON contact.contact_id = contact_organisation.contact_id
JOIN contact_address on contact_address.contact_id = contact.contact_id
JOIN affiliation on affiliation.organisation_id = organisation.organisation_id
JOIN project ON affiliation.project_id = project.project_id
JOIN programcall ON programcall.programcall_id = project.programcall_id
WHERE contact_address.country_id = 150
AND contact_address.zipcode >= 5500
and contact_address.zipcode <= 6029
AND affiliation.date_end IS NULL
AND project.project_id IN (SELECT project_id FROM project_version WHERE type_id = 2 and approved = 1)
and project.project_id not in (SELECT project_id FROM project_version WHERE type_id = 4)
GROUP BY organisation.organisation_id
Organisations of type in a list of projects (including technical contact)
SELECT organisation,
project.project,
organisation_type.type,
country.country,
contact.firstname,
contact.middlename,
contact.lastname
FROM organisation
JOIN affiliation on affiliation.organisation_id = organisation.organisation_id
JOIN project ON affiliation.project_id = project.project_id
JOIN contact ON affiliation.contact_id = contact.contact_id
JOIN country ON country.country_id = organisation.country_id
JOIN organisation_type ON organisation_type.type_id = organisation.type_id
WHERE affiliation.date_end IS NULL
AND organisation.type_id = 3
AND project.project_id IN (10032, 1137, 10211, 10224, 10238, 10080, 10038, 10230, 1113, 10216, 10203)
List of registrants with unknown organisation type
SELECT organisation.organisation_id, organisation.organisation
FROM registration
JOIN contact_organisation ON contact_organisation.contact_id = registration.contact_id
JOIN organisation ON contact_organisation.organisation_id = organisation.organisation_id
WHERE meeting_id = 32
AND date_end IS NULL
AND organisation.type_id = 0;
Insert NDA for bodies
INSERT INTO nda (date_approved, date_signed, contact_id, date_created, approve_contact_id)
SELECT NOW(), NOW(), contact_id, NOW(), contact_id
FROM contact
WHERE contact_id IN (SELECT contact_id
FROM selection_contact
WHERE selection_id IN (45, 46, 47, 51, 128)
UNION
SELECT contact_id
FROM funder);
INSERT INTO programcall_nda (programcall_id, nda_id)
SELECT 36, nda_id
FROM nda
WHERE nda_id > 16928
Correct organisations for contacts from other portals
INSERT INTO contact_organisation (contact_id, organisation_id)
SELECT DISTINCT(contact.contact_id), invoice.organisation_id
FROM contact
INNER JOIN invoice ON invoice.contact_id = contact.contact_id
WHERE contact.contact_id NOT IN (SELECT contact_id
FROM contact_organisation)
AND contact.contact_id <> 1;
INSERT INTO contact_organisation (contact_id, organisation_id)
SELECT DISTINCT contact_id, organisation_id
FROM affiliation
WHERE contact_id NOT IN (SELECT contact_id FROM contact_organisation)
AND contact_id <> 1;
Last modified: 11 April 2025