5

I am stuck in a requirement. It might be simple but i am not getting through.

I have one audit table Audit_Info which captures the audit information of all tables. A table could be run multiple times on the same business date. My requirement is to get the latest business date record for each month upto last 5 months. It may happen that for one particular month the table was not run.

Table is like

table_name business_date src_rec tgt_rec del_rec load_timestamp
abc          25/10/2015   10      10      0       23/01/2016 03:06:56
abc          25/10/2015   10      10      0       23/01/2016 05:23:34
abc          07/09/2015   10      10      0       23/10/2015 05:37:30
abc          05/08/2015   10      10      0       23/09/2015 05:23:34
abc          15/06/2015   10      10      0       23/07/2015 05:23:34
abc          25/04/2015   10      10      0       23/05/2015 05:23:34

similary there are other tables in this. I need it for 5 tables.

Thanks for your help.

Regards, Amit Please see the highlighted

user3901666
  • 399
  • 11
  • 29

3 Answers3

3

Based on your expected result this should be close:

select * from tab
where  -- last five months
   business_date >= add_months(trunc(current_date),-5)
qualify
   row_number()  
   over (partition by trunc(business_date)  -- every month
         order by business_date desc, load_timestamp desc) -- latest date
dnoeth
  • 59,503
  • 4
  • 39
  • 56
1

Hmmm, if I understand correctly you can use row_number() with some date arithmetic:

select ai.*
from (select ai.*,
             row_number() over (partition by table_name, extract(year from business_date), extract(month from business_date)
                                order by business_date desc
                               ) as seqnum
      from audit_info ai
      where timestamp >= current timestamp - interval '5' month
     ) ai
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • OP requirement is to the latest **business_date** (not load_timestamp). Also... `where timestamp >= current timestamp...` : `timestamp` is not a column and `current timestamp` (with the space) is not correct. – mauro Feb 23 '16 at 04:35
  • Thanks Gordon, Sorry, if my desc was misleading. Please find the attached – user3901666 Feb 23 '16 at 07:18
  • Thanks everybody for your response Thanks Dieter ..got expected result..:) – user3901666 Mar 03 '16 at 10:46
1

If I understand correctly, you want the greatest date per month for the 5 most recent months that you have data for. If so, group by year and month and use the max function to select the greatest date per month:

select top 5 
    max(business_date), extract(year from business_date) , extract(month from business_date)
from mytable
group by extract(year from business_date), extract(month from business_date)
order by extract(year from business_date) desc, extract(month from business_date) desc
FuzzyTree
  • 32,014
  • 3
  • 54
  • 85