Answers to: need to get for each of the last 5 months' min and max amount with the corresponding date in each rowhttps://sqlanywhere-forum.sap.com/questions/31323/need-to-get-for-each-of-the-last-5-months-min-and-max-amount-with-the-corresponding-date-in-each-row<p>given a date like '2017-12-10' I know how to query for previous month's min amount with the corresponding date:</p>
<div><pre><span></span>Begin
</pre></div>
<p>declare a_dt date
declare dt1 date
declare dt2 date</p>
<p>select
select amt, valuation_dt,
'min in the mth' 'note' from dba.summary_by_fin_grp
where fi_group_cd='RBCFG'
and valuation_dt between '2017-11-01' and '2017-11-30 23:59:59.999'
and net_ValueWOBankAcct =
(select min(net_ValueWOBankAcct) from dba.summary_by_fin_grp
where fi_group_cd='RBCFG'
and valuation_dt between '2017-11-01' and '2017-11-30 23:59:59.999')</p>
<p>I know also I can loop with</p>
<p>while expr
begin
...
end
Now how do go about returning min value with the date for each month in n last previous months without hard-coding with aid of c#</p>
<p>please note I will eventually like to list also the max value</p>
<p>I also tried the analytic function with over ( partition by..) but I only get one single min value throughout the last 5 months</p>
<div><pre><span></span> SELECT
net_ValueWOBankAcct as amt
, valuation_dt
, min_in_month
, YearMth
FROM (
SELECT
net_ValueWOBankAcct as amt
, valuation_dt
, net_ValueWOBankAcct
, year(valuation_dt)+ month(valuation_dt) as yearMth
, MIN(net_ValueWOBankAcct) OVER (PARTITION BY YearMth) AS min_in_month
FROM dba.pf_dailyvaluation_summary_by_fin_grp
WHERE fi_group_cd = 'RBCFG'
AND valuation_dt >= '2017-11-01' AND valuation_dt < '2017-12-01'
) d
WHERE net_ValueWOBankAcct = min_in_month
</pre></div>
<p>that seem to work. I guess I can repeat that for max__in_month in a union along with some note that is a min for the month.</p>
<p>however I need to list that along with the min_month and I have toruble getting the corresponding date</p>
<p>your help is apprecated</p>enWed, 30 Nov 2022 02:00:12 -0000