user979331 user979331 - 6 months ago 49
SQL Question

SQL ROW_NUMBER() always return 1 for each row

I have this part of a query here:

(
SELECT ROW_NUMBER() OVER (ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS 'SortOrder'
FOR XML PATH(''), Type
)


This is for XML. My issue is that
ROW_NUMBER()
always returns 1, how come it's not returning a different number for each row?

FULL Query:

Select
(

Select cast ('<'+ V_CONSTAT_ACTUAL_DATES.JOB_NUMBER + '>' +
cast(

(Select


(
SELECT CONVERT(date, V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS 'closingDate'
FOR XML PATH(''), Type
),
(
SELECT DATEDIFF(dd,V_CONSTAT_BASE_DATES.ID67,V_CONSTAT_ACTUAL_DATES.DATE_TO_END)-1 AS 'DaysOfConstruction'
FOR XML PATH(''), Type
),
(
SELECT DATEDIFF(dd,GETDATE(),V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS 'DaysToClosing'
FOR XML PATH(''), Type
),
(
SELECT
CASE WHEN COALESCE(V_CONSTAT_ACTUAL_DATES.IDNOTES2, '') = '' THEN ' ' ELSE V_CONSTAT_ACTUAL_DATES.IDNOTES2 END AS 'notes'
FOR XML PATH(''), Type
),
(
SELECT DATEDIFF(dd,V_CONSTAT_BASE_DATES.ID187,V_CONSTAT_PROJ_DATES.ID187) AS 'ScheduleVariance'
FOR XML PATH(''), Type
),
(
SELECT SortOrder FROM
(SELECT ROW_NUMBER() OVER
(ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END)
AS 'SortOrder') AS SubQuery
FOR XML PATH(''), Type
)


for xml path(''))
as varchar(max)

)
+ '</'+ V_CONSTAT_ACTUAL_DATES.JOB_NUMBER + '>'
as xml)
)
from ((homefront.dbo.V_CONSTAT_PROJ_DATES V_CONSTAT_PROJ_DATES INNER JOIN homefront.dbo.V_CONSTAT_ACTUAL_DATES V_CONSTAT_ACTUAL_DATES
ON
V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_ACTUAL_DATES.JOB_NUMBER)
INNER JOIN
homefront.dbo.V_CONSTAT_BASE_DATES V_CONSTAT_BASE_DATES
ON
(V_CONSTAT_ACTUAL_DATES.JOB_NUMBER=V_CONSTAT_BASE_DATES.JOB_NUMBER) AND (V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_BASE_DATES.JOB_NUMBER))
INNER JOIN
homefront.dbo.V_CONSTAT_SCH_DATES V_CONSTAT_SCH_DATES
ON
((V_CONSTAT_BASE_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER) AND (V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER))
AND (V_CONSTAT_ACTUAL_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER)



WHERE V_CONSTAT_ACTUAL_DATES.AREA_DESC = 'Ancaster Augusta Ph 4(A) Condos' AND V_CONSTAT_ACTUAL_DATES.DATE_TO_END>=GETDATE()
ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END
FOR XML PATH(''), ROOT('Root')

Answer

It is common for analytic functions to be used through a derived table so that the column is produced and then accessed later by a subsequent clauses via the column alias. It is particularly common when needing to use row_number() results in a where clause. e.g.

select * from (select *
                  , row_number(partition by X order by Y) as rn
               from table1
               ) as d
where d.rn = 1

Here I believe the same logic applies, you want to calculate a sortorder column THEN place the data into an XML result. My guess is you want to partition by job number.

FROM (
  SELECT
        *
      , ROW_NUMBER() OVER (PARTITION BY V_CONSTAT_ACTUAL_DATES.JOB_NUMBER
                            ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS 'SortOrder'
  FROM homefront.dbo.V_CONSTAT_PROJ_DATES V_CONSTAT_PROJ_DATES
        INNER JOIN homefront.dbo.V_CONSTAT_ACTUAL_DATES V_CONSTAT_ACTUAL_DATES ON V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_ACTUAL_DATES.JOB_NUMBER
        INNER JOIN homefront.dbo.V_CONSTAT_BASE_DATES V_CONSTAT_BASE_DATES ON V_CONSTAT_ACTUAL_DATES.JOB_NUMBER = V_CONSTAT_BASE_DATES.JOB_NUMBER
                    AND V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_BASE_DATES.JOB_NUMBER
        INNER JOIN homefront.dbo.V_CONSTAT_SCH_DATES V_CONSTAT_SCH_DATES ON V_CONSTAT_BASE_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
                    AND V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
                    AND V_CONSTAT_ACTUAL_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
  WHERE V_CONSTAT_ACTUAL_DATES.AREA_DESC = 'Ancaster Augusta Ph 4(A) Condos'
        AND V_CONSTAT_ACTUAL_DATES.DATE_TO_END >= GETDATE()
  ) AS d

and as a full query:

SELECT (
        SELECT
              CAST('<' + V_CONSTAT_ACTUAL_DATES.JOB_NUMBER + '>' +
              CAST((
                    SELECT (
                                 SELECT
                                       CONVERT(date, d.DATE_TO_END) AS 'closingDate'
                                 FOR xml PATH (''), TYPE
                           )
                         , (
                                 SELECT
                                       DATEDIFF(dd, d.ID67, V_CONSTAT_ACTUAL_DATES.DATE_TO_END) - 1 AS 'DaysOfConstruction'
                                 FOR xml PATH (''), TYPE
                           )
                         , (
                                 SELECT
                                       DATEDIFF(dd, GETDATE(), d.DATE_TO_END) AS 'DaysToClosing'
                                 FOR xml PATH (''), TYPE
                           )
                         , (
                                 SELECT
                                       CASE
                                             WHEN COALESCE(d.IDNOTES2, '') = '' THEN ' '
                                             ELSE d.IDNOTES2
                                       END AS 'notes'
                                 FOR xml PATH (''), TYPE
                           )
                         , (
                                 SELECT
                                       DATEDIFF(dd, d.ID187, d.ID187) AS 'ScheduleVariance'
                                 FOR xml PATH (''), TYPE
                           )
                         , (
                                 SELECT
                                       SortOrder
                                 FROM (
                                       SELECT
                                             d.SortOrder
                                 ) AS SubQuery
                                 FOR xml PATH (''), TYPE
                           )


                    FOR xml PATH ('')
              )
              AS varchar(max)

              )
              + '</' + V_CONSTAT_ACTUAL_DATES.JOB_NUMBER + '>'
              AS xml)
  )
FROM (
  SELECT
        *
      , ROW_NUMBER() OVER (PARTITION BY V_CONSTAT_ACTUAL_DATES.JOB_NUMBER
                            ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS "SortOrder"
  FROM homefront.dbo.V_CONSTAT_PROJ_DATES V_CONSTAT_PROJ_DATES
        INNER JOIN homefront.dbo.V_CONSTAT_ACTUAL_DATES V_CONSTAT_ACTUAL_DATES ON V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_ACTUAL_DATES.JOB_NUMBER
        INNER JOIN homefront.dbo.V_CONSTAT_BASE_DATES V_CONSTAT_BASE_DATES ON V_CONSTAT_ACTUAL_DATES.JOB_NUMBER = V_CONSTAT_BASE_DATES.JOB_NUMBER
                    AND V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_BASE_DATES.JOB_NUMBER
        INNER JOIN homefront.dbo.V_CONSTAT_SCH_DATES V_CONSTAT_SCH_DATES ON V_CONSTAT_BASE_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
                    AND V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
                    AND V_CONSTAT_ACTUAL_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
  WHERE V_CONSTAT_ACTUAL_DATES.AREA_DESC = 'Ancaster Augusta Ph 4(A) Condos'
        AND V_CONSTAT_ACTUAL_DATES.DATE_TO_END >= GETDATE()
  ) AS d
ORDER BY
      V_CONSTAT_ACTUAL_DATES.DATE_TO_END
FOR xml PATH (''), ROOT ('Root')