All Forums Database
Beanstalk4 2 posts Joined 02/12
23 Jul 2015
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
 
 
 
 
 
 

gskaushik 56 posts Joined 09/10
24 Jul 2015

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

 

 
 
 
 
 
 
 

Regards
Subramanian kaushik Gurumoorthy

dnoeth 4628 posts Joined 11/04
25 Jul 2015

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

 

Dieter

You must sign in to leave a comment.