need help to write sql query

Analytics
Highlighted
ko
Fan

need help to write sql query

we need to extract the records where TPlvalue changed for that claimno based on loaddatekey

 

Scenaio:

CalimNo               Tvalue   loaddatekey

1                              a                              11042004---pick this one as it is first record

1                              a                              11062004

1                              null                         11202004 ---pick this as tpl value changed

1                              null                         11222004

1                              null                         12252004

1                              a                              12252006---pick this as tpl value changed

1                              a                              12252007

1                              null                         12252007---pick this as tpl value changed

2                              null                         11042004---pick this one as it is first record

2                              null                         11062004

2                              null                         11202004

2                              null                         11222004

2                              null                         12252004

2                              a                              12252006---pick this as tpl value changed

2                              a                              12252007

2                              null                         12252007---pick this as tpl value changed


Accepted Solutions
Junior Contributor

Re: need help to write sql query

Those NULLs complicate the logic a bit:

SELECT ...
   Row_Number () 
   Over (PARTITION BY CalimNo
         ORDER BY loaddatekey) AS rn,
   Min(Tvalue)
   Over (PARTITION BY CalimNo
         ORDER BY loaddatekey
         ROWS BETWEEN 1 Preceding  AND 1 Preceding) AS prev_val
FROM mytable
QUALIFY rn = 1
  OR Tvalue <> prev_val 
  OR (Tvalue IS NULL AND prev_val IS NOT NULL)
  OR (Tvalue IS NOT NULL AND prev_val IS NULL)

Or you might use COALESCE if you know a value which will never exist:

SELECT ...
   Min(Coalesce(Tvalue, 'ööö'))
   Over (PARTITION BY CalimNo
         ORDER BY loaddatekey
         ROWS BETWEEN 1 Preceding  AND 1 Preceding) AS prev_val
FROM vt
QUALIFY 
   prev_val <> Coalesce(Tvalue, 'ööö')
OR prev_val IS  NULL

 

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: need help to write sql query

There's likely a more elegant way to do this, but you can get the previous row's value with an ordered analytic function and then use the qualify clause to only keep rows where there was a change.

 

create table claims (
	id int
	,claim_id int
	,val char(1)
	,dt date
) primary index(id);

insert into claims (1,1,'a','2018-01-01');--first
insert into claims (2,1,'a','2018-01-02');
insert into claims (3,1,'b','2018-01-03');--change
insert into claims (4,1,'b','2018-01-04');
insert into claims (5,1,'z','2018-01-05');--change

select * from claims order by dt;
--id	claim_id	val	dt
--1	1		a 	2018-01-01
--2	1		a 	2018-01-02
--3	1		b 	2018-01-03
--4	1		b 	2018-01-04
--5	1		z 	2018-01-05


select 
	id, claim_id, val, dt
	/*Get the previous val*/
	, max(val) over(
		partition by claim_id --group claims
		order by dt --order by the date
		rows between 1 preceding and 1 preceding --look at only the prev row
	) as prev_val
from claims
qualify prev_val is null --first date for that claim
	or val <> prev_val --change in val

--id	claim_id	val	dt			prev_val
--1	1		a 	2018-01-01	null
--3	1		b 	2018-01-03	a 
--5	1		z 	2018-01-05	b 
Junior Contributor

Re: need help to write sql query

Those NULLs complicate the logic a bit:

SELECT ...
   Row_Number () 
   Over (PARTITION BY CalimNo
         ORDER BY loaddatekey) AS rn,
   Min(Tvalue)
   Over (PARTITION BY CalimNo
         ORDER BY loaddatekey
         ROWS BETWEEN 1 Preceding  AND 1 Preceding) AS prev_val
FROM mytable
QUALIFY rn = 1
  OR Tvalue <> prev_val 
  OR (Tvalue IS NULL AND prev_val IS NOT NULL)
  OR (Tvalue IS NOT NULL AND prev_val IS NULL)

Or you might use COALESCE if you know a value which will never exist:

SELECT ...
   Min(Coalesce(Tvalue, 'ööö'))
   Over (PARTITION BY CalimNo
         ORDER BY loaddatekey
         ROWS BETWEEN 1 Preceding  AND 1 Preceding) AS prev_val
FROM vt
QUALIFY 
   prev_val <> Coalesce(Tvalue, 'ööö')
OR prev_val IS  NULL

 

ko
Fan

Re: need help to write sql query

Capture.PNGsource

 

Thanks for your reply

The TPl value contains only A and nulls.

 

OUTPUT:

 

tgt.PNGHighlighted recotds should be the output