Reputation: 11961
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')
Upvotes: 0
Views: 3165
Reputation: 35623
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')
Upvotes: 1
Reputation: 126
You can use subquery for your needs. Try something like this:
SELECT SortOrder FROM
(SELECT ROW_NUMBER() OVER
(ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END)
AS 'SortOrder') AS SubQuery
FOR XML PATH(''), Type
Edit: After reviewing full query I can recommend you another solution.
Actualy you will have all query logic inside your subselect and ROW_NUMBER() function will work perfectly. The outside part will cover query in XML.
P.S. Little bit more explained:
SELECT (
SELECT CAST('<'+JOB_NUMBER + '>' +
CAST((SELECT
(SELECT closingDate FOR XML PATH(''), Type),
(SELECT DaysOfConstruction FOR XML PATH(''), Type),
(SELECT DaysToClosing FOR XML PATH(''), Type),
(SELECT notes FOR XML PATH(''), Type),
(SELECT ScheduleVariance FOR XML PATH(''), Type),
(SELECT SortOrder FOR XML PATH(''), Type)
FOR XML PATH('')) AS VARCHAR(MAX))
+ '</'+ JOB_NUMBER + '>' as xml))
FROM
(SELECT
V_CONSTAT_ACTUAL_DATES.JOB_NUMBER,
CONVERT(date, V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS closingDate,
DATEDIFF(dd, V_CONSTAT_BASE_DATES.ID67,V_CONSTAT_ACTUAL_DATES.DATE_TO_END) - 1 AS DaysOfConstruction,
DATEDIFF(dd, GETDATE(),V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS DaysToClosing,
(CASE WHEN COALESCE(V_CONSTAT_ACTUAL_DATES.IDNOTES2, '') = '' THEN ' ' ELSE V_CONSTAT_ACTUAL_DATES.IDNOTES2 END) AS notes,
DATEDIFF(dd, V_CONSTAT_BASE_DATES.ID187,V_CONSTAT_PROJ_DATES.ID187) AS ScheduleVariance,
ROW_NUMBER() OVER (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 SubQuery
ORDER BY closingDate
FOR XML PATH(''), ROOT('Root')
Upvotes: 1
Reputation: 221380
You're putting ROW_NUMBER()
inside of a scalar subquery with exactly one row:
(SELECT ROW_NUMBER() OVER
(ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END)
AS 'SortOrder')
You're probably thinking that the ORDER BY
clause (which references a column from the outer query) makes any difference to the calculation of ROW_NUMBER()
here. It doesn't. This subquery returns one row and the only reasonable ROW_NUMBER
for that row is always 1
.
The solution is to delay conversion of your results to XML for as long as possible and just select your data ordinarily, with the ROW_NUMBER()
calculation at the top level SELECT
.
Upvotes: 5