Need this tricky logic .. Urgent help

Database

Need this tricky logic .. Urgent help

Hi.. Here is a tricky scenario am trying to solve. Please help me out.

Table A1: 3 columns. And this table keeps updating once in 2 days.

id,name,fav_color,date

2051,joe,white,07/21

2052,John,green,07/21

After 2 days records are updated and table A1 looks like this.

id,name,fav_color,date

2051,joe,blue,07/23

2052,Rick,green,07/23

Table A2: this is a history table that captures all the changes being done to table A1.

id,updated_column,update_dt,old_value,new_value

2051,fav_color,07/23,white,blue

2052,Name,07/23,John,Rick 

Now business wants to see a monthly snapshot of table A1 by the end of the month.

basically I want a monthly snapshot like this.

id,name,fav_color,date

2051,joe,white,07/21

2051,joe,blue,07/23

2052,John,green,07/21

2052,Rick,green,07/23

please tell me how to achieve this.

I have very less time to figure this out, so am posting here :). Thank you all

2 REPLIES
Enthusiast

Re: Need this tricky logic .. Urgent help

Hi ,

We can use UNION ALL , as below

select

id,

name,

fav_color,

date

From Table_A1

union all

select

id,

updated_column,

old_value,

update_dt

from table_a2

Junior Contributor

Re: Need this tricky logic .. Urgent help

What's your Teradata release?

In TD14.10 there's LAST_VALUE:

SELECT
id,
LAST_VALUE(name ignore NULLS)
OVER (PARTITION BY id
ORDER BY datecol DESC) AS name,
LAST_VALUE(fav_color ignore NULLS)
OVER (PARTITION BY id
ORDER BY datecol DESC) AS fav_color,
MAX(datecol) -- date of change, NULL for the first version
OVER (PARTITION BY id
ORDER BY datecol DESC
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS detail
FROM
(
SELECT id, NAME, fav_color, DATE '9999-12-31' AS datecol -- current version
FROM a1

UNION ALL

SELECT -- previous versions
id,
MAX(CASE WHEN updated_column = 'name' THEN old_value END) AS name,
MAX(CASE WHEN updated_column = 'fav_color' THEN old_value END) AS fav_color,
update_dt
FROM a2
GROUP BY id, update_dt
) AS dt