0

I have a CTE that marks (as bits) overlapped records based on certain criteria. 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 queries.

When I directly call the bit variable I don't have problems, but where I do a calculation with t2.ini (which is one of variables in the subquery) I get the error. I've been stuck for days and I can't find a better approach for a better solution.

In this table, for this user we have two overlapped records with id 2 and 3

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

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                   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

But I need to also make the dates sequential so in the table it would look like INI→FIN→INI→FIN →... without overlapped dates. To accomplish 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) --LAST ENDDATE RECORD = (OVERLAPPED BEGININGDATE RECORD -1)

This is the current code that gives me an error. I received a suggested to do an UPDATE with JOIN, but with IDOR column that's temporarily generated it isn't possible to work it around:

CREATE TABLE tstOver 
    ([valido] int, [id] int, [idhr] int, [ini] datetime, fin datetime, ult_act datetime)
;

INSERT INTO tstOver
VALUES
    (1, 1, 666, '2019-12-01','2019-12-05','2018-01-10'),
    (1, 2, 666, '2019-12-03','2019-12-07','2018-01-12'),
    (1, 3, 666, '2019-12-06','2019-12-10','2018-01-14'),
    (1, 4, 666, '2019-12-15','2019-12-20','2018-01-16')
;

;WITH CTE AS
(
    --GROUP BY IDHR
    SELECT ROW_NUMBER() OVER (PARTITION BY IDHR ORDER BY ULT_ACT ASC) AS IDOR,
    T.VALIDO,
    T.ID,
    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.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


DROP TABLE tstOver

Any help is appreciated.

  • Does the left semi join (WHERE EXISTS) return only one row? – McNets Feb 10 at 16:57
  • @McNets It returns the row that gets overlapped, in the example I provided It will return 2 rows because there is 2 overlapped records. – Kurox Feb 10 at 17:15
  • Wish you could clearly show the end output .Overlapping is clear,output is not clear – KumarHarsh Feb 11 at 3:22
0

Your appear to have a couple of problems 1) you are referencing the T2.INI column in the UPDATE statement, but T2 only exists within the EXISTS statements. 2) The CONVERT statements on the date are messing up the UPDATE.

While the following may not be perfectly identical in functionality, it does actually run

;WITH CTE AS(
--GROUP BY IDHR
SELECT row_number() OVER (PARTITION BY T.IDHR ORDER BY T.ULT_ACT ASC) AS IDOR,
T.VALIDO,
T.ID,
T.INI AS INI,
T.FIN AS FIN,
T.ULT_ACT AS ULT_ACT,
T2.fin as T2FIN
FROM tstOver T
inner join tstOver T2
    on T.ID <> T2.ID
    and (T2.INI BETWEEN T.INI AND T.FIN) OR
    T2.INI = T.FIN
    --WHERE EXISTS
    --    (
    --        --CRITERIA FOR OVERLAPS
    --        SELECT 1 FROM tstOver T2
    --        WHERE 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 = (CTE.T2FIN - 1) --4406 BECAUSE T2.INI IT'S DERIVED FROM THE SUBQUERY
  where IDOR > 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.