Solved: Trying to correct a SQL query for SSRS report

I have a query the produces this report in SSRS that I have attached.

I am trying to bring in cc.valid_to so that I can change the expressions for a conditional formatting to reference against that but when I do it via SELECT it changes the results in the cell and does not show the "cell_value" for several records. How can I bring it in without affecting anything else?

`/******************************
** Obje: Query 
** Name: MSSC_StudentsProgressReport
** Desc: SQL Query to retrieve a list of contact and category records where the category is one of the following: 
**       'EDPROB','EDASSG','EDEXPX','EDEXPG','EDTGGD'  
**       This query will be used in the (SSRS) Students Progress Report
** Auth: Tunde Shaba (TS) - Advanced Business Solution
** Date: 28/07/2015
**************************
** Change History
**************************
** PR   Date        Author  Description 
** --   --------   -------  --------------------------------------
** 1    28/07/2015 TS       Query created and used in Students Progress Report v1
** 2    11/09/2015 TS       Query updated to allow table headers to be ordered as per PCR 001 and used in Students Progress Report v2.2
** 3    14/09/2015 TS       Query updated as per mail from Jayson to display expected grades under a single column and target grades under a single column (v2.3)
*******************************/

SELECT DISTINCT
     c.label_name
    ,cc.contact_number
    ,av.activity
    ,av.activity_value
    ,a.activity_desc
    ,activity_value_desc=--PR3: av.activity_value_desc
                        CASE
                            WHEN av.activity IN ('EDEXPG','EDTGGD') THEN 'Grade'
                            ELSE av.activity_value_desc
                        END
    ,cell_value=
                CASE 
                    WHEN cc.activity = av.activity AND cc.activity_value = av.activity_value AND cc.activity IN ('EDEXPG', 'EDTGGD') THEN cc.activity_value 
                    WHEN cc.activity = av.activity AND cc.activity_value = av.activity_value AND cc.activity NOT IN ('EDEXPG', 'EDTGGD') THEN CONVERT(VARCHAR(10),cc.activity_date,126)
                    ELSE NULL
                END
    , activity_sort=CASE av.activity --PR2: Used in report to sort activities column order on the report
                                        WHEN 'EDPROB' THEN 1
                                        WHEN 'EDASSG' THEN 2
                                        WHEN 'EDEXPX' THEN 3
                                        WHEN 'EDEXPG' THEN 4
                                        WHEN 'EDTGGD' THEN 5
                                    END
    ,activity_value_sort=CASE --PR2: Used in report to sort activity values column order on the report
                            WHEN av.activity = 'EDPROB' AND av.activity_value = 'ENQY' THEN 1
                            WHEN av.activity = 'EDPROB' AND av.activity_value = 'DIAG' THEN 2
                            WHEN av.activity = 'EDPROB' AND av.activity_value = 'INDC' THEN 3
                            WHEN av.activity = 'EDPROB' AND av.activity_value = 'PLNG' THEN 4
                            WHEN av.activity = 'EDPROB' AND av.activity_value = 'PRCS' THEN 5
                         END
FROM contact_categories cc
    INNER JOIN contacts c ON cc.contact_number = c.contact_number
    INNER JOIN activities a ON a.activity = cc.activity
        AND a.activity IN ('EDPROB','EDASSG','EDEXPX','EDEXPG','EDTGGD')
CROSS JOIN activity_values av --PR2: MSSC want the report to display all activity value column headers

WHERE av.activity IN ('EDPROB','EDASSG','EDEXPX','EDEXPG','EDTGGD')
    AND cc.valid_from <= CONVERT(DATE, GETDATE())
    AND cc.valid_to >= CONVERT(DATE, GETDATE())
ORDER BY 7 DESC, 5, 6`

Open in new window