Compare columns and find the difference from a table dynamically

Question

I have a table with 3 columns,first column is some parameter and remaining 3 are last 1 week count of those parameter and has content similar like below. first row is table columns from oracle. I have to calculate the difference 2 dates.

Parameter    20190319   20190315    20190313
============================================
A    682            614         600         
B    194            194         190     
C    62             62          0

Output should be like below,

Parameter    20190319   (20190319-20190315) 20190315    (20190315-20190313) 20190313
========================================================
 A   682            68      614         14      600         
 B   194            0       194         4       190     
 C   62             0        62         62      0

Here the tricky part is dates are not in sequence and it can be up to 7dates, we have to calculate dynamically from column names. Would be great if can be done in oracle. Thank you!!

When you say "Oracle", you mean some database engine, right? You should be able to solve this in your query. — Kusalananda, Mar 25 '19 at 12:22
@Bodo, the table contains data from different dates, I'm not sure what else I can provide on this. — Senthil Prabu S, Mar 25 '19 at 12:29
@Kusalananda, i couldnt solve using queries, thats why i have posted here. Thanks for your time — Senthil Prabu S, Mar 25 '19 at 12:31
@SenthilPrabuS The question would be suitable for e.g. StackOverflow if the SQL schema and the current SQL query was included. — Kusalananda, Mar 25 '19 at 12:36

score 0 · Accepted Answer · answered Mar 26 '19 at 19:34

0

Something like this?

#!/usr/bin/perl

use strict;
use warnings;

while (<DATA>) {
        chomp;
        my @line = split;
        my $diff1 = $line[1] - $line[2];
        my $diff2 = $line[2] - $line[3];
        print "$line[0]\t$line[1]\t$diff1\t$line[2]\t$diff2\t$line[3]\n";
}

__DATA__
A    682            614         600         
B    194            194         190     
C    62             62          0

Output

$ perl t.pl 
A   682 68  614 14  600
B   194 0   194 4   190
C   62  0   62  62  0

The output of line C in your question doesn't look correct. How did you calculate that?

answered Mar 26 '19 at 19:34

Ashish Kumar

811
4
8

Thanks Ashish!! but i'm not sure about the how many dates will be present to find the difference, upto 10 days maybe present. is it possible to modify accordingly? Also here __DATA__ is a file, right? – Senthil Prabu S Mar 27 '19 at 12:42
You can find more information about at https://stackoverflow.com/questions/13463509/the-data-syntax-in-perl Copy all your dates below __DATA__ line and try running the code, share how this works and what's expected if it doesn't work per your requirements. – Ashish Kumar Apr 01 '19 at 20:56

score 0 · Answer 2 · answered Apr 03 '19 at 07:46

Finally, I could able to write the solution, Thanks all for your support! Specially Ashish:)

=========================================================================== DECLARE

x varchar2(2000):= NULL; y varchar2(4000):= NULL;

begin

for i in(select column_id,column_name,lead(column_name,1) OVER (ORDER BY column_id) next_column 
    from all_tab_cols where table_name='TABLE_NAME' and column_name not in ('Parameter'))
loop

    if i.next_column != 'NULL' then
        x := x||'NVL("'||i.column_name||'",0) as "'||i.column_name||'",NVL("'||i.column_name||'", 0)-NVL("'||i.next_column||'", 0) as "'||i.column_name||'~",';
    else
        x := x||'NVL("'||i.column_name||'",0) as "'||i.column_name||'"';
    end if;    
 end loop;

y :=  'create  table TABLE_NAME_NEW as select Parameter,'|| x || ' from TABLE_NAME
order by  Parameter';
--dbms_output.put_line('y :'||y);  
execute immediate y;

END;

Compare columns and find the difference from a table dynamically

2 Answers2

/