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's gravatar image

gg99
227293038
accept rate: 10%

edited 12 Dec '17, 09:11

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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:

×46
×6

question asked: 11 Dec '17, 11:00

question was seen: 1,187 times

last updated: 12 Dec '17, 09:11