asked on
months person is active
Employee 101 worked two jobs RCD (0,6) shows he worked two jobs
First job RCD =0 and EMPL_STATUS=T he heft 15-08-10
Second Job RCD=6 and EMPL_STATUS=T he left 15-12-12
Records look like this:
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS
2007-12-12 101 0 ADL 2007-12-12 null A
2014-12-12 101 6 ADL 2014-12-12 null A
2015-07-24 101 6 REO 2014-12-12 null A
2015-08-12 101 0 ADL 2007-12-12 15-08-10 T
2015-12-24 101 6 TER 2014-12-12 15-12-12 T
Result in two steps
1) Update Active null end_dt termination date
Records look like this:
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS
2007-12-12 101 0 ADL 2007-12-12 15-08-10 A--update end_date
2014-12-12 101 6 ADL 2014-12-12 15-12-12 A --update end_date
2015-07-24 101 6 REO 2014-12-12 null A
2015-08-12 101 0 ADL 2007-12-12 15-08-10 T
2015-12-24 101 6 TER 2014-12-12 15-12-12 T
2) Want to see how many nonths employee are active for Q3(Starts from October till December)
In this case employee with RCD 6 is active for Q3 for 12 months.
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS months_active
2014-12-12 101 6 ADL 2014-12-12 15-12-12 A 12
First job RCD =0 and EMPL_STATUS=T he heft 15-08-10
Second Job RCD=6 and EMPL_STATUS=T he left 15-12-12
Records look like this:
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS
2007-12-12 101 0 ADL 2007-12-12 null A
2014-12-12 101 6 ADL 2014-12-12 null A
2015-07-24 101 6 REO 2014-12-12 null A
2015-08-12 101 0 ADL 2007-12-12 15-08-10 T
2015-12-24 101 6 TER 2014-12-12 15-12-12 T
Result in two steps
1) Update Active null end_dt termination date
Records look like this:
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS
2007-12-12 101 0 ADL 2007-12-12 15-08-10 A--update end_date
2014-12-12 101 6 ADL 2014-12-12 15-12-12 A --update end_date
2015-07-24 101 6 REO 2014-12-12 null A
2015-08-12 101 0 ADL 2007-12-12 15-08-10 T
2015-12-24 101 6 TER 2014-12-12 15-12-12 T
2) Want to see how many nonths employee are active for Q3(Starts from October till December)
In this case employee with RCD 6 is active for Q3 for 12 months.
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS months_active
2014-12-12 101 6 ADL 2014-12-12 15-12-12 A 12
Oracle Database
Last Comment
Sean Stuber
First question - what are the date formats (I assume the fields are varchar2)?
Second question - if RCD 6 started 2014-12-12 (I assume to be December 12, 2014) and ended 15-12-12 (assumed to be December 12, 2015). why wouldn't the months active in Q3 be 4, since he/she would have been active December, 2014, October, 2015, November, 2015, and December, 2015?
Second question - if RCD 6 started 2014-12-12 (I assume to be December 12, 2014) and ended 15-12-12 (assumed to be December 12, 2015). why wouldn't the months active in Q3 be 4, since he/she would have been active December, 2014, October, 2015, November, 2015, and December, 2015?
(Not a direct answer) I have an article out there T-SQL: Identify Bad Dates in a Time Series which is SQL Server T-SQL with a bunch of Common Table Expressions (CTE's) that analyze a time series for custom logic such as gaps, overlaps, and duplicates. This code should be very transferrable to Oracle, and you can modify the custom logic to suit your needs.
ASKER
I should have asked part2 not part1 so lets start from there
My question is how can i find fiscal_quater range .
Source:
Records look like this:
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS
2007-12-12 101 0 ADL 2007-12-12 2015-08-10 A
2014-12-12 101 6 PAY 2014-12-12 2015-12-12 A
2015-07-24 101 6 REO 2014-12-12 null A
2015-08-12 101 0 ADL 2007-12-12 2015-08-10 T
2015-12-24 101 6 TER 2014-12-12 2015-12-12 T
Target:
Want to see how many months employee are active for Q3 ( Q3-Starts from October till December)
In this case employee with RCD 6 is active(Action ADL tells employee is active) for Q3 for 12 months.
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS months_active
2014-12-12 101 6 ADL 2014-12-12 15-12-12 A 12
If i want to see Q2 active i should get results like below.
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS months_active
2007-12-12 101 0 ADL 2007-12-12 2015-08-10 A 9
2014-12-12 101 6 ADL 2014-12-12 15-12-12 A 12
My question is how can i find fiscal_quater range .
Source:
Records look like this:
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS
2007-12-12 101 0 ADL 2007-12-12 2015-08-10 A
2014-12-12 101 6 PAY 2014-12-12 2015-12-12 A
2015-07-24 101 6 REO 2014-12-12 null A
2015-08-12 101 0 ADL 2007-12-12 2015-08-10 T
2015-12-24 101 6 TER 2014-12-12 2015-12-12 T
Target:
Want to see how many months employee are active for Q3 ( Q3-Starts from October till December)
In this case employee with RCD 6 is active(Action ADL tells employee is active) for Q3 for 12 months.
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS months_active
2014-12-12 101 6 ADL 2014-12-12 15-12-12 A 12
If i want to see Q2 active i should get results like below.
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS months_active
2007-12-12 101 0 ADL 2007-12-12 2015-08-10 A 9
2014-12-12 101 6 ADL 2014-12-12 15-12-12 A 12
When you say Q3 do you mean "any" Q3?
That is, a record from November of 1982 would count because that's active in Q3? Or a record for December 2037?
Or, do you mean Q3 as in the most recent period Oct-Dec based on the current date?
So, since today is Feb 3, 2016, then Q3 means Oct-Dec of 2015?
Also, in your Q2 example.
The first row shows active for 9 months. Where does that number come from? The record looks like it was active for almost 8 years.
The second row shows 12 months active. But in Q2 that person would have only been there for 9 months.
Is that a mistake in your example, or are you not looking for time "as of" a particular quarter?
That is, a record from November of 1982 would count because that's active in Q3? Or a record for December 2037?
Or, do you mean Q3 as in the most recent period Oct-Dec based on the current date?
So, since today is Feb 3, 2016, then Q3 means Oct-Dec of 2015?
Also, in your Q2 example.
The first row shows active for 9 months. Where does that number come from? The record looks like it was active for almost 8 years.
The second row shows 12 months active. But in Q2 that person would have only been there for 9 months.
Is that a mistake in your example, or are you not looking for time "as of" a particular quarter?
I think a little more clarification is needed. Are you trying to say that, if an employee's start_dt or end_dt encompasses some time in Q3, compute the number of active months from the month of the end_dt back to the month of the start_dt ? So RCD 0 would be from December 2014 to August 2015 and RCD 6 would be from December 2014 to December 2015. The problem I see is that the difference between the two would be 8 and 12 and the count of the months the employee was active would be 9 and 13?
ASKER
1)Months active is months between start_dt and end_dt
2)Right now i am just asking 2015 Q3 but this is just example so how we get Quater is
if we see for data range falling between start date and end date between 2015 Q3 pick that record
else ignore that
Example in below case date range falls between 2014-12-12 to 2015-12-12 so if i say give me anything
which falls in quarter 2015 Q3 i should pick up this record.
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS months_active
2014-12-12 101 6 ADL 2014-12-12 15-12-12 A 12
2)Right now i am just asking 2015 Q3 but this is just example so how we get Quater is
if we see for data range falling between start date and end date between 2015 Q3 pick that record
else ignore that
Example in below case date range falls between 2014-12-12 to 2015-12-12 so if i say give me anything
which falls in quarter 2015 Q3 i should pick up this record.
EFFDT EMPLID RCD ACTION START_DT END_DT EMPL_STATUS months_active
2014-12-12 101 6 ADL 2014-12-12 15-12-12 A 12
you keep changing date formats
15-12-12
2015-12-12
which do you want? or do you not want formatted text but instead want date values returned?
15-12-12
2015-12-12
which do you want? or do you not want formatted text but instead want date values returned?
assuming you want dates try this...
SELECT t.*, MONTHS_BETWEEN(end_dt, start_dt) months_active
FROM yourtable t
WHERE action = 'ADL'
AND end_dt >= TO_DATE('2015-10-01', 'yyyy-mm-dd') -- Q3 start
AND start_dt <= TO_DATE('2015-12-31', 'yyyy-mm-dd') -- Q3 end
comparing the end to the start and the start to the end is intentional, not a typo
SELECT t.*, MONTHS_BETWEEN(end_dt, start_dt) months_active
FROM yourtable t
WHERE action = 'ADL'
AND end_dt >= TO_DATE('2015-10-01', 'yyyy-mm-dd') -- Q3 start
AND start_dt <= TO_DATE('2015-12-31', 'yyyy-mm-dd') -- Q3 end
comparing the end to the start and the start to the end is intentional, not a typo
ASKER
I don't want to do below I want to create date table and link this table to date and just say 2015-Q3
and it will give me all results and so on
end_dt >= TO_DATE('2015-10-01', 'yyyy-mm-dd') -- Q3 start
AND start_dt <= TO_DATE('2015-12-31', 'yyyy-mm-dd') -- Q3 end
and it will give me all results and so on
end_dt >= TO_DATE('2015-10-01', 'yyyy-mm-dd') -- Q3 start
AND start_dt <= TO_DATE('2015-12-31', 'yyyy-mm-dd') -- Q3 end
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
If it is different, please explain how?
2 - Why is end_dt using 2-digit years and the other dates 4-digit years?