Group Mulitple Data Ranges by ID and Plan Code (1 Viewer)

karoe

New member
Local time
Today, 04:49
Joined
Sep 17, 2019
Messages
3
Hello,

I have a file of multiple data ranges per EEID ranging from 1/1/2003 to current. The various stop/start dates is how they appear in our HRIS. I am looking for suggestions of code to group by EEID and plan code.

I have the spreadsheet attached void of any confidential data. It was produced in Access.

Thanks in advance,

karoe
 

Attachments

  • tbl10103History.xlsx
    123.4 KB · Views: 74

theDBguy

I’m here to help
Staff member
Local time
Today, 01:49
Joined
Oct 29, 2018
Messages
21,357
Hi. Can you mock up for us the final result you're trying to achieve from this sample data? Thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:49
Joined
May 7, 2009
Messages
19,169
what do you mean by group?

this is grouped:

select plan_code, start_date, stop_date from tblHistory order by plan_code, start_date, stop_date;

this is also group:

select distict plan_code, (selec min(t.start_date) from tblhistory as t where t.plan_code=tblhistory.plan_code) as start_date, (selec max(t.stop_date) from tblhistory as t where t.plan_code=tblhistory.plan_code) as stop_date from tblhistory;
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:49
Joined
Feb 28, 2001
Messages
26,996
The question is not clear regarding the start/stop dates. You define date ranges based on two dates. But you are not clear on the selectivity of those date ranges with respect to the other groups you mentioned.

Do you have a single list of dates that define the crossover periods of the date ranges for a given group?

Or do you have a list of dates that define the crossover periods of the data ranges for ALL groups, one set of dates fits all?

Or do you have a list of dates that define date ranges in some other way?

This answer will govern the most likely approach to establish the data relationships you need for this grouping.
 

karoe

New member
Local time
Today, 04:49
Joined
Sep 17, 2019
Messages
3
I would like to see each employee ID and the latest date range (latest stop date) for each plan code. An employee can have more than one plan code. I've attached what I would like the end result to look like.
 

Attachments

  • Mock_Up_End_Result.xlsx
    14.7 KB · Views: 83

June7

AWF VIP
Local time
Today, 00:49
Joined
Mar 9, 2014
Messages
5,423
Rather hard to produce that exact output when data doesn't exist. For instance, there is no employee 1009 in history.

Perhaps you should use SELECT TOP N nested query. Requires a unique record identifier, autonumber field should serve. http://allenbrowne.com/subquery-01.html#TopN

SELECT tbl10103History.*
FROM tbl10103History WHERE ID IN (SELECT TOP 1 ID FROM tbl10103History AS Dupe WHERE Dupe.Employee=tbl10103History.Employee AND Dupe.Plan_Code=tbl10103History.Plan_Code ORDER BY Employee, Plan_Code, Stop_Date DESC);

Unfortunately, the query is slow, which is not uncommon for this type of query.
 
Last edited:

plog

Banishment Pending
Local time
Today, 03:49
Joined
May 11, 2011
Messages
11,611
We need 2 sets of related data:

A. Starting data. Include table and field names and enough data to cover all cases.

B. Expected results of data in A. Show what data you hope to end up with when you feed in the data from A.

You have provided A data, but you have not provided B data. Your expected results are not based on the A data you provided. Please provide the actual data you expect when you feed in the data from your first post(tbl10103History.xlsx)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:49
Joined
May 7, 2009
Messages
19,169
it's almost the same as query #2 in post #3:
Code:
select distict company, 
   employee, 
   plan_code, 
   (select min(t.start_date) from tblhistory as t 
       where t.company=tblhistory.company and t.employee=tblhistory.employee 
        and t.plan_code=tblhistory.plan_code) as start_date, 
   (select max(t.stop_date) from tblhistory as t 
        where t.company=tblhistory.company and t.employee=tblhistory.employee 
        and t.plan_code=tblhistory.plan_code) as stop_date from tblhistory;

** replace tblhistory with the name of your table.
 

Users who are viewing this thread

Top Bottom