t-sql 2012 row over partition

  • wendy elizabeth

    SSCoach

    Points: 16915

    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

  • Emmitt Albright

    SSC Veteran

    Points: 218

    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;
  • wendy elizabeth

    SSCoach

    Points: 16915

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply