Minimax on forex rates table.

This will give you the min and Max values either it has a single value for a time or more values..

SELECT fxr_date, fxr_time, Min(fxr_open) Min_open, Max(fxr_open) Max_open, Min(fxr_high) min_high, max(fxr_high) max_high
, min(fxr_low) min_low, max(fxr_low) max_low, min(fxr_close) min_close, max(fxr_close) max_close
FROM USDSGD
GROUP BY fxr_date, fxr_time

Select allOpen in new window

May I ask a clarification, is two records have the same rate, say same maximum rate, is it returning the first one found or the last one ?
Thank you very much

>> May I ask a clarification, is two records have the same rate, say same maximum rate, is it returning the first one found or the last one ?

Just to clarify, that the query I've provided will group the records by Date and Time values.
If you want the grouping to be done based upon rate then the query should be modified accordingly. Kindly let me know based upon which column you would need to consolidate the rows so that I can guide you accordingly.

First, thank you very much for your help! Now, on the solution: the SQL statement returns many records but I would expect maximum 8 (one for each min/max and if in a day the min and max was reached, then one record is ok().
The group by date time is correct.
To give an example:

(date, time, open, high, low, close)
23.07.2018 13:00 1.3, 1.7, 1.3, 1.5
24.07.2018 14:45 1.65, 1.9, 1.45, 1.6
25.07.2018 08:32 1.8, 2.6, 1.25, 1.5
26.07.2018 15:43 1.3, 1.91, 1.52, 1.77
27.07.2018 13:35 1.47, 2.3, 2.0, 2.1
30.07.2018 11:45 2.0, 2.4, 2.1, 2.0

I wanted the below
(date, time, min_open, max_open, min_high, max_high, min_low, max_low, min_close, max_close)
23.07.2018 13:00 1.3, NULL, NULL, NULL, ...   <-- min open (same value was in 26.07 but I am looking for the 'first' qualified value)
30.07.2018 11:45 NULL, 2.0, NULL, NULL, ...   <-- max open
23.07.2018 13:00 NULL, NULL, 1.7, NULL, ...   <-- min high
25.07.2018 08:32 NULL, NULL, NULL, 2.6, NULL, ...   <-- max high
25.07.2018 08:32 NULL, NULL, NULL, NULL, 1.25, NULL... <-- min low
30.07.2018 11:45 NULL, NULL, NULL, NULL, NULL, 2.1, NULL...  <-- max low
etc for the min-max close.

Hence, should be a 8x8 matrix (8 records for 8 values, excluding date and time)


Thank you very much for your effort (and patience!) :-), hope makes it clearer

This one can help you get the min and max records but not as 8*8 matrix.. kindly let me know whether the below result set can help you or not

CREATE TABLE [USDSGD](
      [fxr_date] [date] NOT NULL,
      [fxr_time] [time] NOT NULL,
      [fxr_open] [decimal](16, 8) NOT NULL,
      [fxr_high] [decimal](16, 8) NOT NULL,
      [fxr_low] [decimal](16, 8) NOT NULL,
      [fxr_close] [decimal](16, 8) NOT NULL,
 CONSTRAINT [PK_USDSGD] PRIMARY KEY CLUSTERED
(
      [fxr_date] ASC,
      [fxr_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into [USDSGD] values 
('2018-07-23', '13:00', 1.3, 1.7, 1.3, 1.5),
('2018-07-24', '14:45', 1.65, 1.9, 1.45, 1.6),
('2018-07-25', '08:32', 1.8, 2.6, 1.25, 1.5),
('2018-07-26', '15:43', 1.3, 1.91, 1.52, 1.77),
('2018-07-27', '13:35', 1.47, 2.3, 2.0, 2.1),
('2018-07-30', '11:45', 2.0, 2.4, 2.1, 2.0)

SELECT fxr_date, fxr_time, Min(fxr_open) Min_open, Max(fxr_open) Max_open, Min(fxr_high) min_high, max(fxr_high) max_high
, min(fxr_low) min_low, max(fxr_low) max_low, min(fxr_close) min_close, max(fxr_close) max_close
FROM USDSGD
GROUP BY fxr_date, fxr_time

; with cte as (
SELECT fxr_date, fxr_time, fxr_open, fxr_high, fxr_low, fxr_close
, ROW_NUMBER() over (order by fxr_open,fxr_date, fxr_time) rnum_open
, ROW_NUMBER() over (order by fxr_high,fxr_date, fxr_time) rnum_high
, ROW_NUMBER() over (order by fxr_low,fxr_date, fxr_time) rnum_low
, ROW_NUMBER() over (order by fxr_close,fxr_date, fxr_time) rnum_close
, ROW_NUMBER() over (order by fxr_open desc,fxr_date, fxr_time) rnum_open_d
, ROW_NUMBER() over (order by fxr_high desc,fxr_date, fxr_time) rnum_high_d
, ROW_NUMBER() over (order by fxr_low desc,fxr_date, fxr_time) rnum_low_d
, ROW_NUMBER() over (order by fxr_close desc,fxr_date, fxr_time) rnum_close_d
FROM USDSGD
)
SELECT fxr_date, fxr_time
, case when rnum_open = 1 THEN fxr_open END fxr_open_min
, case when rnum_open_d = 1 THEN fxr_open END fxr_open_max
, case when rnum_high = 1 THEN fxr_high END fxr_high_min
, case when rnum_high_d = 1 THEN fxr_high END fxr_high_max
, case when rnum_low = 1 THEN fxr_low END fxr_low_min
, case when rnum_low_d = 1 THEN fxr_low END fxr_low_max
, case when rnum_close = 1 THEN fxr_close END fxr_close_min
, case when rnum_close_d = 1 THEN fxr_close END fxr_close_max
from cte 

Select allOpen in new window

I think it is in the right direction; however, it returns (when ran against the original data set) too many records with all columns NULL except date and time. Attached an image that might help see the issue. (the second data set - seems returns two data sets)

Note: very correctly returned 1 record where the fxr_open and the fxr_high where both max. This is ok. I know I might have 8 records maximum or 2 records minimum (suppose a fictional scenario where one day, all rates were at their maximum, and another day all were at their minimum)

Thank you for your effort and knowledge; you think can be done without returning records with all NULL?? (and just one data set?)
Seems you are closer than I ever got to be..
Tue-24-07-2018-21-44-54.png