Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2016
    Posts
    2
    Provided Answers: 1

    Answered: Selecting last 3 months' data

    Code:
    Hello All, I am new to DB and this forum.
    
    I have  a table called Employee
    
    br_id  |  emp_id  |  year  |  month   | assignment   | status   |  fund  |  proj_cost
    
    1     77551     2016     2     13	               1     1341      1104,34
    1     77569     2014     9     25		              2333      1760,57
    1     77577     2016     9     17		              2861	   3141,91
    1     77569	    2014     8     29		              4459	   7264,25
    1     77577	    2016     8     11		              2719	   4744,1
    1     77551	    2016     1     16		              1582	   1819,67
    1     77577	    2016     2     18		              2600	   3852,15
    1     77551	    2015     12     29	                2    2989	   3154,63
    
    
    I am looking for last 3 months data for each employee Id.
    
    For example, 
    1) For 77551, last valid period is year 2016, month 2. So for this Emp_id, it should be data from last year december to present year february
    
    1	    77551	 2016	2	     13	       1	1341    1104,34
    1	    77551	 2016	1	     16		        1582	   1819,67
    1	    77551	 2015	12	     29	       2	2989	   3154,63
    
    
    2) If there is a gap between 3 successive months then fetch only last 2 rows.
    
    For 77577, it should be data for month 8 and 9 , year 2016 only.
    
    3) Need to fetch data based on last 3 months. So for 77569 also it should fetch data for the year 2014.
    
    Final outout should look like :-
    
    
    br_id  |  emp_id | year | month | assignment | status | fund | proj_cost
    
    1	    77551	 2016	2	     13	       1	1341    1104,34
    1	    77551	 2016	1	     16		        1582	   1819,67
    1	    77551	 2015	12	     29	       2	2989	   3154,63
    1	    77577	 2016	9	     17		        2861	   3141,91
    1	    77577	 2016	8	     11		        2719	   4744,1
    1	    77569	 2014	9	     25		        2333	   1760,57
    1	    77569	 2014	8	     29		        4459	   7264,25
    
    
    Thank you in advance :)
    Last edited by Pentium99; 09-16-16 at 06:53.

  2. Best Answer
    Posted by Pentium99

    "Sorry Guys,

    It was urgently required so posted without trying much.
    It took one hour to write : (

    Below is the query:-

    With Mycte
    as
    (Select br_id, emp_id, To_Date (To_Number(year) || To_Number (month), 'yyyymm') Period, assignment, status, fund, proj_cost
    From employee
    Group By br_id, emp_id, year, month, assignment, status, fund, proj_cost),
    Mycte2
    As
    (Select br_id, emp_id, Max(Period) Period From Mycte Group By br_id, emp_id)
    Select A.br_id, A.emp_id, a.Period, extract (year from a.Period) year, extract (month from a.Period) month, A.assignment, A.status, A.fund, A.proj_cost From Mycte A, Mycte2 B
    Where
    a.period >= add_months (b.period, -2) and
    A.br_id = B.br_id And
    A.emp_id = B.emp_id
    order by a.emp_id desc;

    Appreciate the people who viewed and tried solving "


  3. #2
    Join Date
    Sep 2016
    Posts
    2
    Provided Answers: 1

    Below is the query

    Sorry Guys,

    It was urgently required so posted without trying much.
    It took one hour to write : (

    Below is the query:-

    With Mycte
    as
    (Select br_id, emp_id, To_Date (To_Number(year) || To_Number (month), 'yyyymm') Period, assignment, status, fund, proj_cost
    From employee
    Group By br_id, emp_id, year, month, assignment, status, fund, proj_cost),
    Mycte2
    As
    (Select br_id, emp_id, Max(Period) Period From Mycte Group By br_id, emp_id)
    Select A.br_id, A.emp_id, a.Period, extract (year from a.Period) year, extract (month from a.Period) month, A.assignment, A.status, A.fund, A.proj_cost From Mycte A, Mycte2 B
    Where
    a.period >= add_months (b.period, -2) and
    A.br_id = B.br_id And
    A.emp_id = B.emp_id
    order by a.emp_id desc;

    Appreciate the people who viewed and tried solving

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •