T-sql 2012 row over partition

Transact-SQL
1

In the t-sql 2012 listed below there is a value that is determined in the Sped_Max column. There are times when the
calculation of SUM(ISNULL(CONVERT(INT, C1023.value), 0)) + SUM(ISNULL(CONVERT(INT, c1024.value), 0))) DESC = 0. Can you tell me why me the value
actually = 0, that nothing is displayed from the query Listed below?

SELECT c1020.PersonID AS Student_PersonID, C1020.Value AS Program_Code, ROW_NUMBER() OVER (PARTITION BY C1020.PersonID
ORDER BY (SUM(ISNULL(CONVERT(INT, C1023.value), 0)) + SUM(ISNULL(CONVERT(INT, c1024.value), 0))) DESC) AS Sped_Max
FROM Test.dbo.cstu C1020
LEFT JOIN
Test.dbo.cstu C1021 ON C1020.personid = C1020.personID AND C1021.attributeID = 1021 AND C1020.date = C1021.date
LEFT JOIN
Test.dbo.cstu C1022 ON C1020.personid = C1022.personID AND C1022.attributeID = 1022 AND C1020.date = C1022.date LEFT JOIN
Test.dbo.cstu C1023 ON C1020.personid = C1023.personID AND C1023.attributeID = 1023 AND C1020.date = C1023.date LEFT JOIN
Test.dbo.cstu C1024 ON C1020.personid = C1024.personID AND C1024.attributeID = 1024 AND C1020.date = C1024.date
where c1020.attributeID = 1020 and C1022.Value IS NULL
GROUP BY C1020.PersonID, C1020.Value

2

Do you mean:
a) The SUM in the order by clause is not displayed, or
b) the value for Sped_Max is not displayed, or
c) there is no row corresponding to the case when the SUM in the order by clause is zero?

If it is (c) one reason might be that the rows are eliminated by the conditions in the where clause.

3

I mean the value from the value for Sped_Max is not displayed. There are lots of cases where the sum will be zero. The where clause does not eliminate rows in this case. Can you tell me why Sped_Max does not display a value?

4

Order by clause in the row_number function will not eliminate a row or cause the value to be suppressed. In fact, if I am not mistaken, row_number function will always show a value for every row. So I don't know why you are not seeing the value. Are you looking at it in SSMS? Can you create a simplified example that someone can run that shows the problem?