given a date like '2017-12-10' I know how to query for previous month's min amount with the corresponding date: Begin declare a_dt date declare dt1 date declare dt2 date 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') I know also I can loop with 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# please note I will eventually like to list also the max value I also tried the analytic function with over ( partition by..) but I only get one single min value throughout the last 5 months 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 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. however I need to list that along with the min_month and I have toruble getting the corresponding date your help is apprecated
asked
gg99 |