Lag or first_value doesn't work when year is changed

Question

    with
    --my input--
    x(id1,med_id,dt,dt_tm,casemgr_id,casemgr_clntid,status) as (
    select 123456,98410,date'2024-04-19',timestamp'2024-04-19 09:00:00',12345,67891,-2
    union all
    select 194567,98410,date'2024-04-19',timestamp'2024-04-19 11:00:00',12345,67891,-2
    union all
    select 789101,98410,date'2024-04-24',timestamp'2024-04-24 09:00:00',12345,67891,-2
    union all
    select 194587,98410,date'2024-04-25',timestamp'2024-04-25 09:00:00',12345,67891,-2
    union all
    select 234561,98410,date'2024-04-26',timestamp'2024-04-26 09:00:00',12345,67891,-2
    union all
    select 456789,91956,date'2024-04-19',timestamp'2024-04-19 08:00:00',99012,87567,-2
    union all
    select 998415,91956,date'2024-12-20',timestamp'2024-12-20 07:00:00',99012,87567,-2
union all
  select 01786,18745,date'2023-11-16',timestamp'2023-11-16 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2023-11-21',timestamp'2023-11-21 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2023-12-01',timestamp'2023-12-01 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2023-12-04',timestamp'2023-12-04 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2024-02-02',timestamp'2024-02-02 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2024-07-05',timestamp'2024-07-05 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2024-07-21',timestamp'2024-07-21 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2024-07-21',timestamp'2024-07-23 07:00:00',12434,87438,-2
    );

Input above. What I am trying to achieve is that for a combination of med_id, casemgr_clntid and dt, the first record sorted by date should be valid, and the subsequent records should be invalid if it falls within 180 days. Then the code should look for the first record after the 180 day timeframe, that record should be valid, and this record should be the starting point and the subsequent rows should be invalid. If greater than 180 days, it should be valid. if there are multiple records on the same day, like in the first two instances, the earlier record should be valid since it's earlier timestamp and the other should be invalid since it's later in the day, In other words, the first record at 9am should be valid while the second record should be invalid. I have also put the desired output at the end. So for example in the last three rows, where the record on 5th july is valid since it's the first record after the 180 day time window, but subsequently it should be invalid (in other words, it has restarted the time window). So the records on 21st July and 23rd july should take the record on 5th july as a starting point

-- sql qry which was suggested, tweaked to explictly put the date format...
SELECT
  id1
, med_id
, dt
,dt_tm
, casemgr_id
, casemgr_clntid
, CASE WHEN (
           to_char(dt,'yyyy-mm-dd)' -
         , lag(to_char(dt,'yyyy-mm-dd')) OVER(PARTITION BY med_id, casemgr_clntid ORDER BY dt_tm)
         )  is null
      OR (
           to_char(dt,'yyyy-mm-dd)' -
         , lag(to_char(dt,'yyyy-mm-dd')) OVER(PARTITION BY med_id, casemgr_clntid ORDER BY dt_tm)

         )  >180
    THEN 'valid'
    ELSE 'invalid'
  END AS status
FROM x
ORDER BY med_id, casemgr_clntid, dt_tm 
;

I also tried subtracting by dt_tm, but the dt_tm-first_value(dt_tm) doesn't work.

Error: invalid operation

    with
        --my desired output--
        x(id1,med_id,dt,dt_tm,casemgr_id,casemgr_clntid,status) as (
        select 123456,98410,date'2024-04-19',timestamp'2024-04-19 09:00:00',12345,67891, 'valid'
        union all
        select 194567,98410,date'2024-04-19',timestamp'2024-04-19 11:00:00',12345,67891,'invalid'
        union all
        select 789101,98410,date'2024-04-24',timestamp'2024-04-24 09:00:00',12345,67891,'invalid'
        union all
        select 194587,98410,date'2024-04-25',timestamp'2024-04-25 09:00:00',12345,67891,'invalid'
        union all
        select 234561,98410,date'2024-04-26',timestamp'2024-04-26 09:00:00',12345,67891,'invalid'
        union all
        select 456789,91956,date'2024-04-19',timestamp'2024-04-19 08:00:00',99012,87567,'valid'
        union all
        select 998415,91956,date'2024-12-20',timestamp'2024-12-20 07:00:00',99012,87567,'valid'
union all
  select 01786,18745,date'2023-11-16',timestamp'2023-11-16 07:00:00',12434,87438,valid
union all
  select 01786,18745,date'2023-11-21',timestamp'2023-11-21 07:00:00',12434,87438,invalid
union all
  select 01786,18745,date'2023-12-01',timestamp'2023-12-01 07:00:00',12434,87438,invalid
union all
  select 01786,18745,date'2023-12-04',timestamp'2023-12-04 07:00:00',12434,87438,invalid
union all
  select 01786,18745,date'2024-02-02',timestamp'2024-02-02 07:00:00',12434,87438,invalid
union all
  select 01786,18745,date'2024-07-05',timestamp'2024-07-05 07:00:00',12434,87438,valid
union all
  select 01786,18745,date'2024-07-21',timestamp'2024-07-21 07:00:00',12434,87438,invalid
union all
  select 01786,18745,date'2024-07-23',timestamp'2024-07-23 07:00:00',12434,87438,invalid

        );

@DaleK I added the desired output as well as an explanation of what I am trying to achieve — Chug
– Chug, Commented Dec 19, 2024 at 1:58
Not an answer, but FYI the "invalid operation" error is because you need to specify the type of interval for the result, e,g. dt_tm-first_value(dt_tm) DAY(4) — Fred
– Fred, Commented Dec 19, 2024 at 16:17
@Andrew Initially I just used the queries suggested, but it failed when the year changes from 2023 to 2024. I tried changing the datatypes to yyyy-mm-dd and it doesnt work. The row with 2/2/2024 should be invalid because it's within 6 months, but it shows valid for me. I am using teradatasqlassistant and the date format in my database is mm/dd/yyyy. That's why I tried to change it to yyyy-mm-dd — Chug
– Chug, Commented Dec 20, 2024 at 21:32

Thorsten Kettner · Accepted Answer · 2024-12-19 06:30:29Z

0

If I am not mistaken, the dt column is redundant, as the date is included in dt_tm. And the whole thing is not about the first dt per med_id and casemgr_clntid anyway, but about their first (i.e. minimum) dt_tm.

SELECT
  id1,
  med_id,
  dt,
  dt_tm,
  casemgr_id,
  casemgr_clntid,
  CASE WHEN dt_tm = MIN(dt_tm) OVER(PARTITION BY med_id, casemgr_clntid)
         OR dt_tm >= CAST(MIN(dt_tm) OVER(PARTITION BY med_id, casemgr_clntid) AS DATE)
                     + INTERVAL '181' DAY
    THEN 'valid'
    ELSE 'invalid'
  END AS status
FROM x
ORDER BY med_id, casemgr_clntid, dt_tm;

answered Dec 19, 2024 at 6:30

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Chug Dec 20, 2024 at 19:19

The code that you suggested doesn't work in the scenario where there is a change of year. I have given examples. When the year changes to 2024, the row with the date 2/2/2024 should be invalid, but the codeshows valid. The code works perfectly in the scenario where the dates are on the same day and the timestamp is sorted

Thorsten Kettner Dec 20, 2024 at 21:13

The year doesn't matter at all. It's a date plus 180 days. Here is a fiddle showing that the query works properly (2/2/2024 is invalid as it should be): dbfiddle.uk/cz59FEtJ

Chug Dec 20, 2024 at 21:29

You are right, 2/2/2024 should be invalid, whereas I am getting valid by running your query. Not sure if it is because I am using teradata sqlassistant? The date formats in my database is mm/dd/yyyy. Is that the reason why?

Chug Dec 20, 2024 at 21:40

I get the same results even with using the LAG that was suggested. 2/2/2024 is coming as valid using TDSQLA, whereas as you indicated it should be invalid because it's within the 180 days

Thorsten Kettner Dec 20, 2024 at 23:22

Dates have no format. It is the tool you are using that displays the dates in some particular format. Same for timestamps.

|

nbk · Accepted Answer · 2024-12-21 01:38:30Z

You need LAG and the check for NULL which marks the first value, that is valid and then you need to check also if 180 day is already past which you check with > 180

    with
    --my input--
    x(id1,med_id,dt,dt_tm,casemgr_id,casemgr_clntid,status) as (
    select 123456,98410,date'2024-04-19',timestamp'2024-04-19 09:00:00',12345,67891,-2
    union all
    select 194567,98410,date'2024-04-19',timestamp'2024-04-19 11:00:00',12345,67891,-2
    union all
    select 789101,98410,date'2024-04-24',timestamp'2024-04-24 09:00:00',12345,67891,-2
    union all
    select 194587,98410,date'2024-04-25',timestamp'2024-04-25 09:00:00',12345,67891,-2
    union all
    select 234561,98410,date'2024-04-26',timestamp'2024-04-26 09:00:00',12345,67891,-2
    union all
    select 456789,91956,date'2024-04-19',timestamp'2024-04-19 08:00:00',99012,87567,-2
    union all
    select 998415,91956,date'2024-12-20',timestamp'2024-12-20 07:00:00',99012,87567,-2
          union all
    select 998425,91956,date'2025-05-21',timestamp'2025-05-21 07:00:00',99012,87567,-2
union all
  select 01786,18745,date'2023-11-16',timestamp'2023-11-16 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2023-11-21',timestamp'2023-11-21 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2023-12-01',timestamp'2023-12-01 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2023-12-04',timestamp'2023-12-04 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2024-02-02',timestamp'2024-02-02 07:00:00',12434,87438,-2
union all
  select 01786,18745,date'2024-07-05',timestamp'2024-07-05 07:00:00',12434,87438,-2
    ),
      CTE as (
SELECT
  id1
, med_id
, dt
,dt_tm
, casemgr_id
, casemgr_clntid
      ,           dt -
          LAG(dt) OVER(PARTITION BY med_id, casemgr_clntid ORDER BY dt_tm) dayspassed

FROM x),
      CTE2 AS(
      SELECT
        id1
, med_id
, dt
,dt_tm
, casemgr_id
, casemgr_clntid
      ,dayspassed
      ,SUM(dayspassed) OVER (PARTITION BY med_id, casemgr_clntid ORDER BY dt_tm)/180 n
      FROM CTE)
      SELECT
              id1
, med_id
, dt
,dt_tm
, casemgr_id
      
, casemgr_clntid
      ,dayspassed
      ,CASE WHEN (n IS NULL)
      OR
      (n > CASE WHEN LAG(n) OVER (PARTITION BY med_id, casemgr_clntid ORDER BY dt_tm) IS NULL 
      THEN 0 ELSE LAG(n) OVER (PARTITION BY med_id, casemgr_clntid ORDER BY dt_tm) END)
      THEN 'Valid'
      ELSE 'invalid'
      END status
      FROM CTE2
ORDER BY med_id, casemgr_clntid, dt_tm 
;

id1	med_id	dt	dt_tm	casemgr_id	casemgr_clntid	dayspassed	status
1786	18745	2023-11-16	2023-11-16 07:00:00	12434	87438	null	Valid
1786	18745	2023-11-21	2023-11-21 07:00:00	12434	87438	5	invalid
1786	18745	2023-12-01	2023-12-01 07:00:00	12434	87438	10	invalid
1786	18745	2023-12-04	2023-12-04 07:00:00	12434	87438	3	invalid
1786	18745	2024-02-02	2024-02-02 07:00:00	12434	87438	60	invalid
1786	18745	2024-07-05	2024-07-05 07:00:00	12434	87438	154	Valid
456789	91956	2024-04-19	2024-04-19 08:00:00	99012	87567	null	Valid
998415	91956	2024-12-20	2024-12-20 07:00:00	99012	87567	245	Valid
998425	91956	2025-05-21	2025-05-21 07:00:00	99012	87567	152	Valid
123456	98410	2024-04-19	2024-04-19 09:00:00	12345	67891	null	Valid
194567	98410	2024-04-19	2024-04-19 11:00:00	12345	67891	0	invalid
789101	98410	2024-04-24	2024-04-24 09:00:00	12345	67891	5	invalid
194587	98410	2024-04-25	2024-04-25 09:00:00	12345	67891	1	invalid
234561	98410	2024-04-26	2024-04-26 09:00:00	12345	67891	1	invalid

SELECT 14

The code that you suggested doesn't work in the scenario where there is a change of year. I have given examples. When the year changes to 2024, the row with the date 2/2/2024 should be invalid, but the codeshows valid. The code works perfectly in the scenario where the dates are on the same day and the timestamp is sorted
that has nothing to do with the dates, you changed the rules, a perfect sql solution is very difficult to ger another programming language like php python c# will be better suited for this, a solution that is better suited for you , is abouve but ut would only catch days since the first treatment and will not take in consideration the other valid dates see example here dbfiddle.uk/UlqJza8U with casemgr_id 99012

Collectives™ on Stack Overflow

Lag or first_value doesn't work when year is changed

2 Answers 2

11 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related