How to identify the most recent Oct 1st date or the Jan 1st based on the date condition given as a dymanic value

Database

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
2 REPLIES
Senior Apprentice

Re: How to identify the most recent Oct 1st date or the Jan 1st based on the date condition given as a dymanic value

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

Re: How to identify the most recent Oct 1st date or the Jan 1st based on the date condition given as a dymanic value

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 .