I have a table of prices, 2 types. metal 1 and metal 2.
I have succeeded in getting the max, min price for each metal groups by day.
How can i also select the start (first) and end (last) of every day too?
I am nearly there, but struggling on getting these two final prices...
My SQL fiddle with example data: http://sqlfiddle.com/#!9/ca4867/1
My query so far:
select
highp.metal_price_datetime_IST AS high_price_metal_price_datetime_IST
, highp.metal_price as highest_price
, lowp.report_term
, lowp.metal_id
, lowp.metal_price as lowest_price
, lowp.metal_price_datetime_IST AS low_price_metal_price_datetime_IST
from (select @report_term:=concat(day(metal_price_datetime_IST), ' ', monthname(metal_price_datetime_IST), ' ', year(metal_price_datetime_IST)) as report_term
, metal_price_datetime_IST
, metal_price
, metal_id
, case when @report_term=@old_report_term then @rn1:=@rn1+1 else @rn1:=1 end as rn
, @old_report_term:=@report_term
from metal_prices
cross join (select @rn1:=0, @old_report_term:='') inituservar1
where metal_price_datetime_IST BETWEEN '2018-02-01' AND LAST_DAY('2018-02-01')
order by metal_id, report_term, metal_price asc) lowp
inner join (select @report_term2:=concat(day(metal_price_datetime_IST), ' ', monthname(metal_price_datetime_IST), ' ', year(metal_price_datetime_IST)) as report_term
, metal_price_datetime_IST
, metal_price
, metal_id
, case when @report_term2=@old_report_term2 then @rn2:=@rn2+1 else @rn2:=1 end as rn
, @old_report_term2:=@report_term2
from metal_prices
cross join (select @rn2:=0, @old_report_term2:='') inituservar1
where metal_price_datetime_IST BETWEEN '2018-02-01' AND LAST_DAY('2018-02-01')
order by metal_id, report_term, metal_price desc) highp
on lowp.rn=highp.rn
and lowp.metal_id = highp.metal_id
and lowp.report_term = highp.report_term
and lowp.rn = 1
and (lowp.metal_id = 1 or lowp.metal_id = 2)
order by lowp.metal_price_datetime_IST DESC
The query you have in your fiddle seems too complex for what needs to be done. I have refactored and rewritten the query. Basically, the query is split in two parts. First one maxminprice determines the max and min price for each day for each metal. Fairly straight forward. The second part firstlastprice is a bit more complex. It finds out the max and min time stamps for each metal for each day. Then joins back to the main table to get the values for those time stamps. The case statement there is to merge the results for max and min (first and last) time so we don't have to do the query twice.
SELECT maxminprice.metal_id,
maxminprice.metal_price_datetime,
maxminprice.max_price,
maxminprice.min_price,
firstlastprice.first_price,
firstlastprice.last_price
FROM (SELECT metal_id,
DATE(metal_price_datetime) metal_price_datetime,
MAX(metal_price) max_price,
MIN(metal_price) min_price
FROM metal_prices
GROUP BY metal_id,
DATE(metal_price_datetime)
ORDER BY metal_id,
DATE(metal_price_datetime)) maxminprice
INNER JOIN (SELECT mp.metal_id,
day_range.metal_price_datetimefl,
SUM(CASE
WHEN TIME(mp.metal_price_datetime) = first_time
THEN
mp.metal_price
ELSE NULL
END) first_price,
SUM(CASE
WHEN TIME(mp.metal_price_datetime) = last_time
THEN
mp.metal_price
ELSE NULL
END) last_price
FROM metal_prices mp
INNER JOIN (SELECT metal_id,
DATE(metal_price_datetime)
metal_price_datetimefl,
MAX(TIME(metal_price_datetime))
last_time,
MIN(TIME(metal_price_datetime))
first_time
FROM metal_prices
GROUP BY metal_id,
DATE(metal_price_datetime))
day_range
ON mp.metal_id = day_range.metal_id
AND DATE(mp.metal_price_datetime) =
day_range.metal_price_datetimefl
AND TIME(mp.metal_price_datetime) IN
( last_time, first_time )
GROUP BY mp.metal_id,
day_range.metal_price_datetimefl) firstlastprice
ON maxminprice.metal_id = firstlastprice.metal_id
AND maxminprice.metal_price_datetime =
firstlastprice.metal_price_datetimefl