CREATE VIEW vwlearningassignmentcorrelation AS (
 SELECT lma.id AS assignment_id,
    lma.userid AS user_id,
    lma.moduleid AS module_id,
    lma.state AS assignment_state,
    lma.isoverdue AS is_assignment_overdue,
    lma.dateassigned AS assignment_date,
    lma.datedue AS assignment_due_date,
    lma.daterecommendedforcompletion AS recommended_completion_date,
    lma.percentagescore AS assignment_percentage_score,
    u.name AS user_name,
    u.email AS user_email,
    u.department AS user_department,
    u.title AS user_title,
    lm.name AS module_name,
    lm.description AS module_description,
    lm.type AS module_type,
    lar.id AS result_id,
    lar.assessmentpercentagescore AS result_assessment_score,
    lar.completionpercentage AS result_completion_percentage,
    lar.passpercent AS required_pass_percentage,
    lar.lengthinminutes AS completion_duration_minutes,
    lar.datecreated AS result_created_date,
    lar.datemodified AS result_modified_date,
        CASE
            WHEN (lar.id IS NOT NULL) THEN 'Completed'::text
            WHEN (lma.isoverdue = true) THEN 'Overdue'::text
            WHEN ((lma.state)::text = 'Assigned'::text) THEN 'In Progress'::text
            ELSE 'Unknown'::text
        END AS lifecycle_status,
        CASE
            WHEN ((lar.assessmentpercentagescore IS NOT NULL) AND (lar.passpercent IS NOT NULL)) THEN
            CASE
                WHEN (lar.assessmentpercentagescore >= lar.passpercent) THEN 'Passed'::text
                ELSE 'Failed'::text
            END
            ELSE 'Not Assessed'::text
        END AS assessment_result,
        CASE
            WHEN ((lar.datecreated IS NOT NULL) AND (lma.dateassigned IS NOT NULL)) THEN EXTRACT(days FROM (lar.datecreated - lma.dateassigned))
            ELSE NULL::numeric
        END AS days_to_completion,
        CASE
            WHEN ((lar.datecreated IS NOT NULL) AND (lma.datedue IS NOT NULL)) THEN
            CASE
                WHEN (lar.datecreated > lma.datedue) THEN 'Completed Late'::text
                ELSE 'Completed On Time'::text
            END
            ELSE NULL::text
        END AS completion_timeliness
   FROM (((learningmoduleassignments lma
     JOIN userdetails u ON (((lma.userid)::text = (u.id)::text)))
     JOIN learningmodules lm ON (((lma.moduleid)::text = (lm.id)::text)))
     LEFT JOIN learningassignmentresults lar ON ((((lma.userid)::text = (lar.userid)::text) AND ((lma.moduleid)::text = (lar.moduleid)::text))))
  WHERE (((u.state)::text <> 'deleted'::text) AND ((lm.state)::text <> 'deleted'::text))
  ORDER BY lma.dateassigned DESC, u.name, lm.name
)