I have rows like below:
id s_Jan s_Feb s_Mar s_Apr
1 100 102 108 99
2 80 105 30 70
I need to get result as below
id s_max s_min
1 108 99
2 105 33
I only list 4 columns, actully the data have 12 months, I don't think it is a good idea to write a case when sql statement like
case when (s_Jan <s_feb and s_Jan <s_Mar and s_Jan <s_Apr) then s_Jan
It will make me crazy. :-(
Any feedback is appreciate.
I think you can transpose the columns into rows and then use simply MIN and MAX Window functions.
Thanks Khurram for your feedback
This is one potential method, but maybe there are some way better. :-)
You can take union all of all the fields so that they are in a row and then take min and max.
Note : Just an example ::::::::::::::select sjan from yy where id=1 union all..select sfeb from yy where id=1... to make it to column level. and you can generalize it as much as you can .
If you have many queries like that, you might even want to consider changing your table structure. I.e., transposing columns into rows, which is what both Khurram and Raja are suggesting to do for the query. It would provide you with more flexibility to use aggregate functions.
You didn't specify your TD release.
In TD14 there's a GREATEST/LEAST, but it's only defined up to 10 columns, so you need to nest it:
Otherwise the CASE will drive you crazy, but it's the most efficient way. At least it can be simplified:
WHEN col1 <= col2 AND col1 <= col3 AND col1 <= col4 THEN col1
WHEN col2 <= col3 AND col2 <= col4 THEN col2
WHEN col3 <= col4 THEN col3
CREATE VOLATILE TABLE dt AS (
SEL CURRENT_DATE AS dt1, CURRENT_DATE-INTERVAL '30' DAY AS dt2, CURRENT_DATE-INTERVAL '60' DAY AS dt3
)WITH DATA PRIMARY INDEX(dt1, dt2, dt3) ON COMMIT PRESERVE ROWS;
SEL LEAST(dt1, dt2, dt3) FROM dt;
Does this function work with dates? I've tried on several table and get an error for inalid number of parameters. Here's an example: