# need to get for each of the last 5 months' min and max amount with the corresponding date in each row

 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 11 Dec '17, 11:00 gg99 193●21●22●29 accept rate: 0%
Be the first one to answer this question!
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×25
×5

question asked: 11 Dec '17, 11:00

question was seen: 88 times

last updated: 12 Dec '17, 09:11