Trying to correct a SQL query for SSRS report

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

SSRS Report
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

MSSC_supportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
I assume that by "bring it in" you mean add it to the SELECT clause which should only have an effect if you have multiple valid_to values per contact_number and thus the DISTINCT is causing additional rows to be brought in.  Otherwise, you are already referencing that column in the WHERE clause.  Is the query you posted the before or after?

Have you tried running the query against one of the data points that is changing on you to try to determine what is happening?

This is going to be fairly difficult to diagnose on this side without actual data.
MSSC_supportAuthor Commented:
Hi Brian,

The query is before. When I run the report the actual information is populated to cell data that is in effect pivoted to lay out all the information I am after. I want in some way a method of referencing activity_date so that I do an expression against valid_to. I do not need to see it but just need to run an expression against it.
Brian CroweDatabase AdministratorCommented:
There just isn't enough information to go off of here.  I'm looking at your solution through a keyhole.  I have almost no idea how your data relates or the structure of your report other than a screenshot.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Try Now
PortletPaulEE Topic AdvisorCommented:
>>"I'm looking at your solution through a keyhole."
absolutely

& I'm not too keen on this structure:
        select distinct ...(several case expressions) ... cross join

I suspect you need a GROUP BY (instead of distinct) and that you may want only the most recent cc.valid_to.

To really help we need to look through a large open garage door instead of that keyhole.

To open the door, provide:
       "sample data" (per table) and
       "expected result"

Then we can propose queries to suit.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MSSC_supportAuthor Commented:
I am currently reviewing it to see if there is another way of doing this. If it fails I will provide the sample data and expected result.
PortletPaulEE Topic AdvisorCommented:
Do you still need help with this question?
MSSC_supportAuthor Commented:
Managed to get this resolved.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.