Skip to content

Instantly share code, notes, and snippets.

@sroehrl

sroehrl/academy-competency-lo-co_report.sql

Last active December 10, 2019 15:08
Show Gist options
  • Save sroehrl/af44d2e9760bc4e9c1ee7289dae883ac to your computer and use it in GitHub Desktop.
Save sroehrl/af44d2e9760bc4e9c1ee7289dae883ac to your computer and use it in GitHub Desktop.
Download ZIP
Raw
academy-competency-lo-co_report.sql
SELECT
lo.id,
"Envision" as academy,
competency.competency,
lo.name,
csfv.value,
co.id as co_id,
co.name as co_name
FROM learning_objective lo
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = (
SELECT id FROM tag g WHERE g.name = 'Envision' AND g.delete_date IS NULL
)
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL
JOIN (
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL
WHERE tag.name != 'Envision'
GROUP BY sr.learning_objective_id
) as competency ON competency.learning_objective_id = lo.id
LEFT JOIN content_object co on co.id = co_lo.content_object_id
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND (
csfv.value = 'Fundamental' OR
csfv.value = 'Intermediate' OR
csfv.value = 'Advanced'
)
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL
UNION
SELECT
lo.id,
"Empower" as academy,
competency.competency,
lo.name,
csfv.value,
co.id as co_id,
co.name as co_name
FROM learning_objective lo
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = (
SELECT id FROM tag g WHERE g.name = 'Empower' AND g.delete_date IS NULL
)
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL
JOIN (
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL
WHERE tag.name != 'Empower'
GROUP BY sr.learning_objective_id
) as competency ON competency.learning_objective_id = lo.id
LEFT JOIN content_object co on co.id = co_lo.content_object_id
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND (
csfv.value = 'Fundamental' OR
csfv.value = 'Intermediate' OR
csfv.value = 'Advanced'
)
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL
UNION
SELECT
lo.id,
"Engage" as academy,
competency.competency,
lo.name,
csfv.value,
co.id as co_id,
co.name as co_name
FROM learning_objective lo
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = (
SELECT id FROM tag g WHERE g.name = 'Engage' AND g.delete_date IS NULL
)
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL
JOIN (
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL
WHERE tag.name != 'Engage'
GROUP BY sr.learning_objective_id
) as competency ON competency.learning_objective_id = lo.id
LEFT JOIN content_object co on co.id = co_lo.content_object_id
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND (
csfv.value = 'Fundamental' OR
csfv.value = 'Intermediate' OR
csfv.value = 'Advanced'
)
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL
UNION
SELECT
lo.id,
"Enrich – T&C" as academy,
competency.competency,
lo.name,
csfv.value,
co.id as co_id,
co.name as co_name
FROM learning_objective lo
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = (
SELECT id FROM tag g WHERE g.name = 'Tender & Contracting' AND g.delete_date IS NULL
)
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL
JOIN (
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL
WHERE tag.name != 'Tender & Contracting'
GROUP BY sr.learning_objective_id
) as competency ON competency.learning_objective_id = lo.id
LEFT JOIN content_object co on co.id = co_lo.content_object_id
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND (
csfv.value = 'Fundamental' OR
csfv.value = 'Intermediate' OR
csfv.value = 'Advanced'
)
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL
UNION
SELECT
lo.id,
"Enrich – Pricing" as academy,
competency.competency,
lo.name,
csfv.value,
co.id as co_id,
co.name as co_name
FROM learning_objective lo
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = (
SELECT id FROM tag g WHERE g.name = 'Pricing Model' AND g.delete_date IS NULL
)
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL
JOIN (
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL
WHERE tag.name != 'Pricing Model'
GROUP BY sr.learning_objective_id
) as competency ON competency.learning_objective_id = lo.id
LEFT JOIN content_object co on co.id = co_lo.content_object_id
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND (
csfv.value = 'Fundamental' OR
csfv.value = 'Intermediate' OR
csfv.value = 'Advanced'
)
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL
UNION
SELECT
lo.id,
"Enrich – ChM Channel Marketing" as academy,
competency.competency,
lo.name,
csfv.value,
co.id as co_id,
co.name as co_name
FROM learning_objective lo
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = (
SELECT id FROM tag g WHERE g.name = 'ChM Channel Marketing' AND g.delete_date IS NULL
)
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL
JOIN (
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL
WHERE tag.name != 'ChM Channel Marketing'
GROUP BY sr.learning_objective_id
) as competency ON competency.learning_objective_id = lo.id
LEFT JOIN content_object co on co.id = co_lo.content_object_id
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND (
csfv.value = 'Fundamental' OR
csfv.value = 'Intermediate' OR
csfv.value = 'Advanced'
)
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL
UNION
SELECT
lo.id,
"Enrich – ChM KAM" as academy,
competency.competency,
lo.name,
csfv.value,
co.id as co_id,
co.name as co_name
FROM learning_objective lo
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = (
SELECT id FROM tag g WHERE g.name = 'ChM KAM' AND g.delete_date IS NULL
)
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL
JOIN (
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL
WHERE tag.name != 'ChM KAM'
GROUP BY sr.learning_objective_id
) as competency ON competency.learning_objective_id = lo.id
LEFT JOIN content_object co on co.id = co_lo.content_object_id
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND (
csfv.value = 'Fundamental' OR
csfv.value = 'Intermediate' OR
csfv.value = 'Advanced'
)
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL
UNION
SELECT
lo.id,
"Enrich – ChM Retail" as academy,
competency.competency,
lo.name,
csfv.value,
co.id as co_id,
co.name as co_name
FROM learning_objective lo
JOIN learning_objective_tag r ON r.learning_objective_id = lo.id AND r.tag_id = (
SELECT id FROM tag g WHERE g.name = 'ChM Retail' AND g.delete_date IS NULL
)
JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = lo.id AND co_lo.delete_date IS NULL
JOIN (
SELECT sr.learning_objective_id, GROUP_CONCAT(tag.name,' || ') as competency FROM tag
JOIN learning_objective_tag sr ON sr.tag_id = tag.id AND sr.delete_date IS NULL
WHERE tag.name != 'ChM Retail'
GROUP BY sr.learning_objective_id
) as competency ON competency.learning_objective_id = lo.id
LEFT JOIN content_object co on co.id = co_lo.content_object_id
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND (
csfv.value = 'Fundamental' OR
csfv.value = 'Intermediate' OR
csfv.value = 'Advanced'
)
WHERE lo.customer_id = UNHEX("1446D53954E111E99D64107B444D9FC4") AND lo.delete_date IS NULL
ORDER BY academy, competency
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment