使用CTE更正重叠日期 [英] Correcting overlapped dates using CTE
问题描述
我有一个CTE，根据某些标准标记（作为位）重叠的记录，事情是过滤后我需要更新这些记录以"更正"。他们并使它们顺序但是我在更新语句上得到4104，因为我是
调用存储在CTE查询之外的变量。
I have a CTE that marks (as bits) overlapped records based on certain criteria, the thing is that after filtering I need to update these records to "correct" them and make them sequential but I'm getting a 4104 on the update statement because i'm calling variables that are stored outside the CTE's query's.
当我直接调用时位变量我没有问题，但我用t2.ini进行计算（这是子查询中的变量之一）我得到错误。我已经被困了好几天了，我找不到更好的方法来获得更好的解决方案。
When I call directly the bit variable I don't have problems but where I do a calculation with t2.ini (wich is one of variables in the subquery) I get the error. I've been stuck for days and I don't find a better approach for a better solution.
在这个表中，对于这个用户，我们有两个重叠的记录，id为2和3
In this table, for this user we have two overlapped records with id 2 and 3
valido id IDHR       INI                             
         鳍                                      ult_act
------ ----------- ---------- -------------- --------- ----------------------- ------------------ -----
1 1           666        2019-12-01 00：00：00.000 2019-12-05 00：00：00.000 2018-01-10 00：00：00.000
1 2           666        2019-12-03 00：00：00.000 2019-12-07 00：00：00.000 2018-01-12 00：00：00.000
1 3           666        2019-12-06 00：00：00.000 2019-12-10 00：00：00.000 2018-01-14 00：00：00.000
1 4           666        2019-12-15 00：00：00.000 2019-12-20 00：00：00.000 2018-01-16 00：00：00.000
valido id idhr ini
fin ult_act
------ ----------- ---------- ----------------------- ----------------------- -----------------------
1 1 666 2019-12-01 00:00:00.000 2019-12-05 00:00:00.000 2018-01-10 00:00:00.000
1 2 666 2019-12-03 00:00:00.000 2019-12-07 00:00:00.000 2018-01-12 00:00:00.000
1 3 666 2019-12-06 00:00:00.000 2019-12-10 00:00:00.000 2018-01-14 00:00:00.000
1 4 666 2019-12-15 00:00:00.000 2019-12-20 00:00:00.000 2018-01-16 00:00:00.000
使用我目前的代码，我能够将重叠记录设置为0并保留最新添加的内容（ult_act是添加此记录时的时间戳）为1.
With my current code I'm able to set overlapped records as 0 and keep the latest added (ult_act is a timestamp for when this record was added) as 1.
valido id ;   IDHR          INI                        
           鳍                                      ult_act
------ ----------- ---------- -------------- --------- ----------------------- ------------------ -----
1 1           666        2019-12-01 00：00：00.000 2019-12-05 00：00：00.000 2018-01-10 00：00：00.000
0 2           666        2019-12-03 00：00：00.000 2019-12-07 00：00：00.000 2018-01-12 00：00：00.000
1 3           666        2019-12-06 00：00：00.000 2019-12-10 00：00：00.000 2018-01-14 00：00：00.000
1 4           666        2019-12-15 00：00：00.000 2019-12-20 00：00：00.000 2018-01-16 00：00：00.000
valido id idhr ini
fin ult_act
------ ----------- ---------- ----------------------- ----------------------- -----------------------
1 1 666 2019-12-01 00:00:00.000 2019-12-05 00:00:00.000 2018-01-10 00:00:00.000
0 2 666 2019-12-03 00:00:00.000 2019-12-07 00:00:00.000 2018-01-12 00:00:00.000
1 3 666 2019-12-06 00:00:00.000 2019-12-10 00:00:00.000 2018-01-14 00:00:00.000
1 4 666 2019-12-15 00:00:00.000 2019-12-20 00:00:00.000 2018-01-16 00:00:00.000
但我还需要按顺序排列日期所以在表中它看起来像INI→FIN→INI→FIN→...没有重叠的日期，因为我想抓住重叠的行，INI值减去一（天）并覆盖它的FIN值
最后一条记录或类似的东西：
But I need to also make the dates sequential so in the table it would look like INI→FIN→INI→FIN →... without overlapped dates, for that I thought of grabbing the overlapped row and the INI value substract one (day) and overwrite it on the FIN value of the last record or something like this:
FIN = (T2.INI - 1)
这是我当前的代码，我收到错误：
This is my current code where I get the error:
;WITH CTE AS
(
--GROUP BY IDHR
SELECT ROW_NUMBER() OVER (PARTITION BY IDHR ORDER BY ULT_ACT ASC) AS IDOR,
T.VALIDO,
T.ID,
T.RUT,
CONVERT(VARCHAR,T.INI,112) AS INI,
CONVERT(VARCHAR,T.FIN,112) AS FIN,
CONVERT(VARCHAR,T.ULT_ACT,112) AS ULT_ACT
FROM tstOver T
WHERE EXISTS
(
--CRITERIA FOR OVERLAPS
SELECT 1 FROM tstOver T2
WHERE T.RUT = T2.RUT AND
T.ID <> T2.ID AND
(T2.INI BETWEEN T.INI AND T.FIN) OR
T2.INI = T.FIN
)
)
--SETTING VALIDO TO 0 WHEN THEY OVERLAP AND MAKING RECORDS SEQUENTIAL DATE-WISE
UPDATE CTE SET VALIDO = 0, FIN = (T2.INI - 1) --4406 BECAUSE T2.INI IT'S DERIVED FROM THE SUBQUERY
WHERE IDOR > 1
SELECT * FROM TSTOVER
GO
任何帮助都值得赞赏。
Any help it's appreciated.
推荐答案
您甚至无法在CTE中的查询中访问T2.INI，因为它只在子查询中可见。在我看来，这不一定是标量值，但可能有多个匹配的行。但是我没有深入解决实际问题。
You cannot even access T2.INI in the query in the CTE, since it is only visible inside the subquery. And it seems to me that this is not necessarily a scalar value, but there could be multiple matching rows. But I did not penetrate the actual problem.
我建议您为表发布CREATE TABLE语句，然后将数据作为insert语句进行采样，以便我们可以复制到查询窗口中处理问题。当然，我们还需要知道预期结果并解释为什么
你想要那个结果。最后，不要忘记告诉我们您要定位的SQL Server版本。
I would suggest that you post the CREATE TABLE statement for your table, and you sample data as insert statements so that we can copy into a query window to work with the problem. Of course, we also need to know the expected result and an explanation why you want that result. Finally, don't forget to tell us which version of SQL Server you are targeting.
这篇关于使用CTE更正重叠日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
