need to find the minimum and maximum value from each row using dynamic query

[from curr] will be given as input

DECLARE @t TABLE(a INT,b INT,c INT);
INSERT @t VALUES(1,2,3),(9,8,7),(4,6,5);
SELECT *
,      (   SELECT  MAX(val) 
           FROM    (VALUES (a)
                       ,   (b)
                       ,   (c)
                   ) AS value(val)
       ) AS MaxVal 
,      (   SELECT  MIN(val) 
           FROM    (VALUES (a)
                       ,   (b)
                       ,   (c)
                   ) AS value(val)
       ) AS MinVal 
FROM @t;

Hi Tuky,

Is this the input table where you should have the min and max value from?

Normally you will have a column for date and values, do you also have that table? For example From Curr column, date, values.

Then I would say:

Select From Curr, date, MIN(Values) AS MIN_VAL, MAX(VALUES) AS MAX_VAL FROM #TEMP

Regards,

Reshma

Try the below:

Declare @Test_Sample Table (FromCurr Varchar(100), July2014 Money, June2014 Money, May2014 Money);
Insert Into @Test_Sample
Values ('AED','3673','36728','3673')
, ('ARS','81853','81321','80799')


Declare @SearchValue Varchar(100) = 'ARS'

Select *
from @Test_Sample A
   Cross Apply
  ( Select Max(AllValues) As MaxValue,Min(AllValues) as MinValue
   From (Values (July2014), (June2014), (May2014)) B(AllValues)
  ) C

  Where A.FromCurr = @SearchValue

Hellow Reshma

Yes This is input table from my db. Here i need to  find the minimum value row wise that means at the from curr column the first attribute is "AED" . We need to find the minimum value for aed from all the columns.

Hi Tuky,

Maybe this wil help you find the min and max value. I have created an example on your above table.

CREATE TABLE #TEMP
(
FROM_CURR VARCHAR(5),
END_JULY14 INT,
END_JUNE14 INT,
END_MAY14 INT
);

INSERT INTO #TEMP
VALUES
('AED',1000,2000,500),
('ARS',500,1000,3000),
('AUD',3000,1000,2000);

--SELECT * FROM #TEMP

SELECT FROM_CURR, MIN(VALUE) AS MIN_VAL, MAX(VALUE) AS MAX_VAL
FROM #TEMP
UNPIVOT(VALUE FOR DATE_VAL IN(END_JULY14,END_JUNE14,END_MAY14)) AS U
GROUP BY FROM_CURR;

Regards,

Reshma

ThanQ so much Reshma :)

As others have posted you need an unpivoting query. But what Reshma is saying is that you should have a table where currency and month is the key, in which case the query is trivial.

A table like the one you posted may be good for presentational purposes, but it breaks the mindset from which a relational database is designed. To wit, a table is supposed to model unique entity, and the columns are supposed to model unique attributes of that entity. If you work against that mindset, you will also find that many operations will be difficult and painful to implement.

You are welcome!

If this is a satisfied answer can you, mark this as an answer?

Thank you!

Regards,

Reshma

 i did unpivot but the problem is lying with that it is when i do unpivot and find the minimum value and attach the period date according to aggregate function if the period is attached it couldn't find one minimum value  rather than it takes all the values group by period.

and here we need to give For curr as input 

from a presentation i need to load it in db for some qc purpose. it should not be considered as a typical database i guess :)

Hi Tuky,

To make sure, is your question answered? Or do you need more help?

Regards,

Reshma