April 17, 2019 at 8:41 pm
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
April 17, 2019 at 9:25 pm
You have a complex query there, that's not formatted well. I've tried to clean it up below to make this easier to read.
In terms of what you are asking, it is hard to be sure without checking the data. When you say that the calculation = 0, is this for all rows? Or is this for a specific row? With a left join, you can have missing values, so I would suggest you check the row you expect to show something, that it exists in all tables. Then produce a data set of this row for three tables with DDL and we can try and determine the issue.
SELECT
Student_PersonID = c1020.PersonID
, Program_Code = C1020.Value
, Sped_Max = ROW_NUMBER() OVER (PARTITION BY C1020.PersonID
ORDER BY (SUM(ISNULL(
CONVERT(
INT
, C1023.value
)
, 0
)
)
+ SUM(ISNULL(
CONVERT(
INT
, c1024.value
)
, 0
)
)
) DESC
)
FROM
Test.dbo.cstu AS C1020
LEFT JOIN Test.dbo.cstu AS C1021
ON C1020.personid = C1020.personID
AND C1021.attributeID = 1021
AND C1020.date = C1021.date
LEFT JOIN Test.dbo.cstu AS C1022
ON C1020.personid = C1022.personID
AND C1022.attributeID = 1022
AND C1020.date = C1022.date
LEFT JOIN Test.dbo.cstu AS C1023
ON C1020.personid = C1023.personID
AND C1023.attributeID = 1023
AND C1020.date = C1023.date
LEFT JOIN Test.dbo.cstu AS 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;
April 18, 2019 at 2:06 pm
The sum will be zero since there will be lots of left joins where there is no value and the result will be null. Can you tell me why the Sped_Max does not display any values?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply