All Forums Database
Raghunath7789 4 posts Joined 07/14
03 Jul 2014
How to identify the most recent Oct 1st date or the Jan 1st based on the date condition given as a dymanic value

need to diplay as this field as “Annual Salary as of EEO Planning Period Start Date”: 

This field should display Annual salary as on specific dates based on when the report has been run:

o If “As of Date” date is any date after “1.1.2015”, it should display Annual Salary as of Jan 1st of that date’s year. 

For example, if the report is run for “11.30.2015” the report should display Annual Salary as on “1.1.2015”

o If “As of Date” date is any date before “1.1.2015”, it should display Annual Salary as of the most recent Oct 1st date.

 Example:  if the report is run for 15th Nov 2013, salary as on 1st October 2013 should be displayed. If the report has been run for 20th August 2013, 

 Annual salary as on 1st October, 2012 should be displayed.

 

Need some Sudo Queries or need some guidness ho we can able to achive it .

 

Sample record set 

Employee ID  Annual Salary  Effective Date

00000472922   5,178,921.00     01-Jun-2007 
00000472922   5,178,921.00     01-Jan-2008
CV27WH24        964,753.56     01-Jan-2012
CV27WH24        964,753.56     06-Mar-2012
CV27WH24        964,753.56     01-Apr-2012
CV27WH24        964,753.56     23-May-2012
CV27WH24        964,753.56     04-Aug-2012
CV27WH24        964,753.56     01-Oct-2012
CV27WH24        964,753.56     01-Apr-2013
CV27WH24        964,753.56     29-Sep-2013
CV27WH24        964,753.56     01-Oct-2013
13245            97,284.24     01-Feb-2004
13245            97,284.24     01-Mar-2004
13245           100,061.28     01-Jul-2004
13245           100,061.28     01-Aug-2004
13245           100,061.28     01-Jan-2005
13245           100,061.28     01-Mar-2005 
13245           102,255.36     01-Apr-2005
13245           102,255.36     01-May-2005

 

dnoeth 4628 posts Joined 11/04
03 Jul 2014

You can calculate the most recent 01-Jan or 01-Oct like this:

   cast(case
           when yourdate >= date '2015-01-01' 
              then (extract(year from yourdate) -1900) * 10000 +  101
           when extract(month from yourdate) < 10
              then (extract(year from yourdate) -1901) * 10000 + 1001
           else    (extract(year from yourdate) -1900) * 10000 + 1001
        end as date) 

It's using old-style syntax, but it's easier than the same in ANSI SQL. If you're on TD13.10 you can hide the calculation in a SQL UDF (or add it as a new colum to your existing calendar table).
But what if there's no row for 01-Oct or 01-Jan, do you want to get the lastest value before that date and the last few months before?
Then it's something like 

WHERE effective_date BETWEEN ADD_MONTHS(this calculation, -3) AND this calculation
QUALIFY ROW_NUMBER()
        OVER (PARTITION BY employee_id
              ORDER BY effective_date DESC) = 1

 

Dieter

Raghunath7789 4 posts Joined 07/14
03 Jul 2014

Thakns Dieter finally i got some rough idea about that
in need some thing like below
 
case when ({d '2015-06-25'} >= date '2015-01-01'
then
BHRDJ02_EMP_JOB_VW.HRDJ02_SRC_EFF_Y = ADD_MONTHS({d '2014-06-25'},-(EXTRACT(MONTH FROM {d '2014-06-25'})-1))+(1-EXTRACT(DAY FROM {d '2014-06-25'}))
-- need to show the 1st date of jan
when extract(month from ({d '2014-06-25'} )) < 10 )
BHRDJ02_EMP_JOB_VW.HRDJ02_SRC_EFF_Y = -- Need to show the previous year oct 1st date
when extract(month from ({d '2014-06-25'} )) > 10 )
BHRDJ02_EMP_JOB_VW.HRDJ02_SRC_EFF_Y =-- if the selected date is greated than oct date means Need to show the current year oct 1st date
when extract(month from ({d '2014-06-25'} )) = 10 )
BHRDJ02_EMP_JOB_VW.HRDJ02_SRC_EFF_Y = -- if the selected date is on the oct date means Need to show the current month oct 1st date
the Commented line given part i need to check .please guide me on this .
 

You must sign in to leave a comment.