How to derive years of service for employees that have termed and returned several times

I'm working with Oracle and have sample data to show you regarding my question (sample data is below).

I'm trying to figure out how to derive years of service for employees that have terminated and returned several times (up to 6 times). The business rules are that if someone leaves and returns before 365 days, then we bridge their service -- otherwise we don't. Bridging is determined by the "Yes" and "No" values in the BRIDGE fields.

My sample data below: The HIRE, TERM and BRIDGE fields go up to 3 levels but it can at times go all the way up to 6 levels deep for some employees.

╔════════╦════════════╦════════════╦══════════╦════════════╦═══════════╦══════════╦═══════════╗
║ EMPLID ║   HIRE_1   ║   TERM_1   ║ BRIDGE_1 ║   HIRE_2   ║  TERM_2   ║ BRIDGE_2 ║  HIRE_3   ║
╠════════╬════════════╬════════════╬══════════╬════════════╬═══════════╬══════════╬═══════════╣
║   1001 ║ 9/22/2011  ║ 12/9/2011  ║ Yes      ║ 7/1/2012   ║ 7/2/2013  ║ Yes      ║ 5/3/2014  ║
║   1002 ║ 3/29/1999  ║ 6/30/1999  ║ Yes      ║ 1/24/2000  ║ 3/29/2004 ║ No       ║ 11/5/2007 ║
║   1003 ║ 8/16/2009  ║ 12/31/2009 ║ Yes      ║ 1/1/2010   ║ 5/15/2012 ║ Yes      ║ 9/5/2012  ║
║   1004 ║ 7/23/2007  ║ 8/21/2009  ║ Yes      ║ 3/1/2010   ║ 2/9/2011  ║ Yes      ║ 4/25/2011 ║
║   1005 ║ 5/11/2010  ║ 8/31/2010  ║ Yes      ║ 7/1/2011   ║ 5/5/2012  ║ Yes      ║ 9/6/2012  ║
║   1006 ║ 1/17/2009  ║ 5/8/2009   ║ Yes      ║ 12/8/2009  ║ 5/4/2010  ║ Yes      ║ 1/18/2011 ║
║   1007 ║ 9/20/2004  ║ 5/4/2007   ║ No       ║ 11/15/2010 ║ 1/21/2011 ║ Yes      ║ 2/16/2011 ║
║   1008 ║ 6/22/2009  ║ 8/29/2009  ║ No       ║ 5/9/2011   ║ 4/13/2012 ║ Yes      ║ 5/21/2012 ║
║   1009 ║ 10/13/2008 ║ 5/9/2009   ║ Yes      ║ 8/24/2009  ║ 5/25/2010 ║ Yes      ║ 8/10/2010 ║
║   1010 ║ 8/13/2008  ║ 5/7/2010   ║ Yes      ║ 1/4/2011   ║ 5/31/2011 ║ Yes      ║ 8/15/2011 ║
║   5011 ║ 7/3/1985   ║ 10/24/2000 ║ No       ║ 7/19/2010  ║           ║ No       ║           ║
╚════════╩════════════╩════════════╩══════════╩════════════╩═══════════╩══════════╩═══════════╝

For the last entry, 5011, the employee was rehired on 7/19/2010 and hasn't left. So the calculation should be a little less than 5 years (4.xx). Because the prior termination date between the next hire date was more than 365 days, bridge_1 is "No". So this resets years recognized for service. Otherwise if the employee was rehired less than 365 days from the prior term, then the bridge_1 would be yes.

I'm looking for the most recent period of bridged employment, so if there are two periods separated by a gap of more than a year (as for employee 5011) then I only want the most recent, not the total of all the separate contiguous (bridged) periods.

Best How To :

As @PonderStibbons pointed out, this can be done quite simply by adding together the span of each hired period, and also adding the span of the between-hiring periods if the bridge value is 'Yes'. Open-ended hirings and different numbers of hirings can be handled by treating all null dates as the same, presumably as sysdate if you want the current hiring period to be counted up to today.

You can either calculate as days and divide by a nominal value for the number of days in the year (again as Ponder showed!), or use month subtraction and divide by 12:

select emplid,
  round((months_between(nvl(term_1, sysdate), hire_1)
    + case when bridge_1 = 'Yes' then months_between(hire_2, term_1) else 0 end
    + months_between(nvl(term_2, sysdate), nvl(hire_2, sysdate))
    + case when bridge_2 = 'Yes' then months_between(hire_3, term_2) else 0 end
    + months_between(sysdate, nvl(hire_3, sysdate))) / 12, 2) as total_years
from t42
order by emplid;

    EMPLID TOTAL_YEARS
---------- -----------
      1001        3.74
      1002       12.62
      1003        5.84
      1004        7.90
      1005        5.10
      1006        6.42
      1007        7.21
      1008        4.29
      1009        6.68
      1010        6.85
      1011        3.31
      1012        2.21
      5011       20.22

SQL Fiddle

For all six potential hiring periods, repeat the pattern; only the first and last clauses in the addition will be different, as hire_1 is always present, and term_6 won't exist.

If you only want the most recent contiguous/bridged period, not the total of all contiguous/bridged periods, you need to be more selective about which periods are included - starting from the most recent and working backwards, taking all later bridges into account:

select emplid,
  round((
    -- 3rd period
    months_between(sysdate, nvl(hire_3, sysdate))
      -- gap between 3rd and 2nd
      + case when (hire_3 is not null and bridge_2 = 'Yes') then
          months_between(hire_3, term_2) else 0 end
      -- 2nd period
      + case when (hire_3 is null or bridge_2 = 'Yes') then
          months_between(nvl(term_2, sysdate), nvl(hire_2, sysdate)) else 0 end
      -- gap between 2nd and 1st
      + case when (hire_3 is not null and bridge_2 = 'Yes')
            and (hire_1 is not null and bridge_1 = 'Yes') then
          months_between(hire_2, term_1) else 0 end
      -- 1st period
      + case when (hire_3 is null or bridge_2 = 'Yes')
            and (hire_2 is null or bridge_1 = 'Yes') then
          months_between(nvl(term_1, sysdate), nvl(hire_1, sysdate)) else 0 end
    ) / 12, 2) as total_years
from t42
order by emplid;

    EMPLID TOTAL_YEARS
---------- -----------
      1001        3.74
      1002        7.62
      1003        5.84
      1004        7.90
      1005        5.10
      1006        6.42
      1007        4.59
      1008        4.11
      1009        6.68
      1010        6.85
      1011        3.31
      1012        1.88
      5011        4.91

SQL Fiddle, including a breakdown for each period/gap if it is should be included.

Improving work with SQL DataTime

sql,sql-server,database,tsql

You can do it like this: SELECT IIF(DAY(@A) >= 25, DATEADD(d, 25 - DAY(@A), @A), DATEADD(d, 25, EOMONTH(@A, -2))) Here's a sample fiddle as well: sqlfiddle Note: EOMONTH requires SQL Sever 2012 or above - it returns the End-Of-Month date given a start date and a month offset....

How to select next row after select in SQL Server?

sql,sql-server

The query can be written as: ; WITH Base AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Shift_Date) RN FROM #Table1 ) , WithC AS ( SELECT * FROM Base WHERE Shift2 = 'C' ) SELECT * FROM WithC UNION SELECT WithCNext.* FROM WithC C LEFT JOIN Base WithCNext ON...

Add 1 to datediff result if time pass 14:30 or 2:30 PM

sql,ms-access,ms-access-2007

Could be: SELECT reservations.customerid, DateDiff("d",reservations.checkin_date, Date()) + Abs(DateDiff("s", #14:30#, Time()) > 0)AS Due_nights FROM reservations ...

Looping distinct values from one table through another without a join

sql,sql-server,tsql,while-loop

So you want all distinct records from table1 paired with all records in table2? That is a cross join: select * from (select distinct * from table1) t1 cross join table2; Or do you want them related by date? Then inner-join: select * from (select distinct * from table1) t1...

Purging Database - Count purged/not-purged tables

mysql,sql,sql-server,database,stored-procedures

The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...

SQL Customized search with special characters

sql,sql-server,sql-server-2008

Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT...

Retrieve Values As Column

mysql,sql

If types are fixed (just IMPRESSION and CLICK), you could use a query like this: SELECT headline, SUM(tracking_type='IMPRESSION') AS impressions, SUM(tracking_type='CLICK') AS clicks FROM tracking GROUP BY headline ...

Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008

I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';...

T-SQL Ordering a Recursive Query - Parent/Child Structure

sql,tsql,recursion,order,hierarchy

The easiest way would be to pad the keys to a fixed length. e.g. 038,007 will be ordered before 038,012 But the padding length would have to be safe for the largest taskid. Although you could keep your path trimmed for readability and create an extra padded field for sorting....

Notice: Array to string conversion in “path of php file” on line 64

php,mysql,arrays,oracle

Curly brackets are your friend when inserting variables into double quoted strings: $main_query=oci_parse($connection,"INSERT INTO ROTTAN(NAME,ROLLNO) VALUES('{$array[$rs][0]}','{$array[$rs][1]}')"); ...

Default the year based on month value

sql,sql-server

SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return "2016" Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,...

Pull information from SQL database and getting login errors

php,sql,database

change $username = "'rylshiel_order"; to $username = "rylshiel_order"; and you should be through. You are passing on an extra single quote here. ...

Convert AWK command to sqlite query

sql,awk,sqlite3

SQLite is an embedded database, i.e., it is designed to be used together with a 'real' programming language. It might be possible to import that log file into a database file, but the whole point of having a database is to store the data, which is neither a direct goal...

Get unique row by single column where duplicates exist

sql,sql-server

SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ...

SQL varchar variable inserts question mark

sql,sql-server-2012

there is un recognizable character in your string that is giving that ?. Delete the value and retype. see my above screen shot...

Take thousand value in SQL

sql,sql-server

SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ...

Select count Columns group by No

sql,sql-server

this will work. you have to provide separate case statement to each condition SQLFIDDLE for the same SQLFIDDLE SELECT EMP_NO, sum(CASE WHEN Emp_Shift = 'AL' THEN 1 ELSE 0 END) AS COUNT_AL, sum(CASE WHEN Emp_Shift = 'S' THEN 1 ELSE 0 END) AS COUNT_S, sum(CASE WHEN Emp_Shift = 'H' THEN...

Query how often an event occurred at a given time

mysql,sql

This could be done using user defined variable which is faster as already mentioned in the previous answer. This needs creating incremental variable for each group depending on some ordering. And from the given data set its user and date. Here how you can achieve it select user, date, purchase_count...

How to delete only the table relationed

sql,laravel,migration

When creating a foreign key constraint, you can also decide what should happen with the constraints. For instance, if you want to delete all articles when a category is deleted (as I guess is your question): Schema::table('articulos', function($table) { $table->foreign('categoria_id')->references('id')->on('categorias')->onDelete('cascade'); $table->foreign('creador_id')->references('id')->on('users'); }); Also see documentation. The cascade identifies to the...

Using Sum in If in Mysql

mysql,sql,select,sum

Using least would be much easier: SELECT LEAST(SUM(my_field), 86400) FROM my_table ...

for-loop add columns using SQL in MS Access

sql,ms-access,table,for-loop,iteration

I have tested your code, I do not see any issues except for the fact, your For statement is a bit off and that you needed to set the db object. Try this code. Sub toto() Dim db As Database, i As Integer Set db = CurrentDb For i =...

mysql_real_escape_string creates \ in server only not in local

php,sql

Your server has magic quotes enabled and your local server not. Remove it with the following sentence set_magic_quotes_runtime(0) As this function is deprecated and it will be deleted in PHP 7.0, I recommend you to change your php.ini with the following sentencies: magic_quotes_gpc = Off magic_quotes_runtime = Off If you...

MySQL: Select several rows based on several keys on a given column

mysql,sql,database

If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012

Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...

SQL: overcoming no ORDER BY in nested query

sql,sqlite

Use a join instead: SELECT a, b FROM t JOIN (SELECT DISTINCT date FROM t ORDER BY date DESC LIMIT 2) tt on t.date = tt.date; ...

Merging two tables into new table by ID and date

sql,sql-server,phpmyadmin

You can use a SELECT statement when inserting into a table. What I would do here is write a select statement that pulls all of the columns you need first. You will have to do a full outer join (simulated by a union of left and right joins) because some...

Select Statement on Two different views

sql

Yes, You can use two different view in SELECT query. You have to JOIN them, if them have matched column in each other. Just treat two different views as like two different tables when using in SELECT Clause. SELECT vw1.a, vw2.b FROM View1 vw1 INNER JOIN View2 vw2 ON vw1.id...

Is there a better way to write this query involving a self select?

sql,postgresql,join,aggregate-functions

Query The query is not as simple as it looks at first. The shortest query string does not necessarily yield best performance. This should be as fast as it gets, being as short as possible for that: SELECT p.username, COALESCE(w.ct, 0) AS won, COALESCE(l.ct, 0) AS lost FROM ( SELECT...

SQL Repeated Condition in Two Tables

sql,variables,coding-style,condition

you can achieve it like this DECLARE @AccountId int; @AccountID=20; DELETE FROM Table_A WHERE FunctionId IN (Select FunctionId FROM Table_B WHERE [email protected]); DELETE FROM Table_B WHERE [email protected]; ...

How to join result of a Group by Query to another table

sql,tsql

You have mistake here from tblUsers u,a.Login_Name try to move this piece of code a.Login_Name to select Select u.User_Id, a.Login_Name from tblUsers u inner join (SELECT s.Login_Name Login_Name, COUNT(s.s1CIDNumber)as abc FROM [dbSuppHousing].[dbo].[tblSurvey] s group by s.Login_Name) a on u.Login_Name=a.Login_Name ...

timestamp SQL to Excel

php,mysql,sql,excel

The ###### is shown in MS Excel when the data in a cell is too long for the column width.... the data inside the cell is still correct, as you can see if you select one of those cells and look at the value displayed in the cell content bar...

Select all none null values from all columns in a table

mysql,sql

select @variable will just return you a value of variable. You need to use some dynamic SQL I believe. Maybe smth like exec('select ' + @colname + ' from ' etc) will work for you (at least it will work in MS SQL server).

Inner Join 3 tables pl/sql

mysql,sql

The issue is that you are using the alias C where you should not, with the count function. This: C.Count(C.column6) should be: Count(C.column6) and the same change applies in the order by clause (which might be counting the wrong column - should it not be column6?): order by C.count(column5) desc...

Entity Framework code-first: querying a view with no primary key

sql,oracle,entity-framework,view,ef-code-first

It's not possible in Entity Framework to have Entities without primary key. Try to get a possible unique key from the views, combining columns, ... to create a unique primary key. If is not possible there is a workaround, if is only a queryable view, with out need to do...

Foreign key in C#

c#,sql,sql-server,database

You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...

SQL only one cell from column can have X value

mysql,sql

Have a separate table with one row that contains the mainOfferName of the row that you want. Then query as: select yt.*, (nt.mainOfferName is not null) as valueFlag from yourtable yt left join newtable nt on yt.mainOfferName = nt.mainOfferName; When you want to change the value, change the value in...

Matplotlib: Plot the result of an SQL query

python,sql,matplotlib,plot

Take this for a starter code : import numpy as np import matplotlib.pyplot as plt from sqlalchemy import create_engine import _mssql fig = plt.figure() ax = fig.add_subplot(111) engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') ## the data data =...

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like

WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile...

Recursive Lag Column Calculation in SQL

sql,sql-server,recursion

Edit In hindsight, this problem is a running partitioned maximum over Column1 * 2. It can be done as simply as SELECT Id, Column1, Model, Product, MAX(Column1 * 2) OVER (Partition BY Model, Product Order BY ID ASC) AS Column2 FROM Table1; Fiddle Original Answer Here's a way to do...

oracle sql error Case When Then Else

sql,oracle,oracle11g

Perhaps this is what you want? If there are rows in SecondTable, then do the second EXISTS: SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND (NOT EXISTS (SELECT 1 from SecondTable) OR EXISTS (SELECT 1 FROM SecondTable WHERE FirstTable.Key = SecondTable.Key and SecondTable.RowProcessed = 'Y')) AND OtherConditions ...

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql

I can explain... a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if...

left join table, find both null and match value

sql,sql-server,join

Try FULL OUTER JOIN. This is the sqlfiddle. It will produce the op you are expecting SQLFiddle select t1.years, t1.numOfppl, t2.years, t2.numOfppl from t1 full outer join t2 on t1.years=t2.years ...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

sql,sql-server,sql-server-2008

You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (...

SQL Group By multiple categories

php,mysql,sql,mysqli

Just include a case statement for the group by expression: SELECT (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3' ELSE Categories.name END) as name, sum(locations.name = 'loc 1' ) as Location1, sum(locations.name = 'loc 2') as Location2, sum(locations.name = 'loc 3') as Location3, count(*) as total FROM ... GROUP BY (CASE...

How do I find records in one table that do exist in another but based on a date?

mysql,sql

select d.`name` from z_dealer d where (select count(*) from z_order o WHERE o.promo_code = d.promo_code AND o.date_ordered > '2015-01-01') = 0 ...

How can I create missing date records for each employee based off a limited calendar?

mysql,sql,tsql

Create a table that contains all the possible dates in the range, @alldates Then insert your missing records with something like this: Query INSERT INTO dbo.timeclock SELECT id ,d.punchtime ,'no time entered' FROM ( SELECT DATE ,id FROM @alldates d CROSS JOIN ( SELECT DISTINCT id FROM dbo.timeclock )...

Retrieve data from one table and insert into another table

sql,asp.net,sql-server

INSERT INTO tbl2 ( Name ,parentId ) SELECT DISTINCT manager ,0 FROM tbl1 WHERE manager NOT IN ( SELECT employee FROM tbl1 ) INSERT INTO tbl2 SELECT DISTINCT employee ,0 FROM tbl1 UPDATE tbl2 SET parentid = parent.id FROM tbl2 INNER JOIN tbl1 ON tbl2.Name = tbl1.employee INNER JOIN tbl2...

Is it possible to index views in Apache Solr

sql,view,solr

You can use the data import handler and set the "query" to point to your view. See example below: <dataConfig> <dataSource driver="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:/temp/example/ex" user="sa" /> <document name="products"> <entity name="feature" query="SELECT * FROM MyView"> <field column="attr_1" name="Attr1" /> <field column="attr_2" name="Attr2" /> <field column="attr_3" name="Attr3" /> </entity> ...