Best way to return 450 rows with query that currently only returns one

Question

I am developing a time and attendance application for the company I work for. Each employee will have a "work schedule" that is assigned to them. This schedule may remain the same for years and years, or it may change twice in one week. So I have created a table such as the following. The table allows the application to know what schedule is in effect on any given date for each employee. The columns of the table are: 1. primary key (with auto-increment trigger) 2. the ID of the schedule 3. the ID of the employee 4. the schedule effective date

C_ID  SCHEDULE_CID  USER_CID        EFFECTIVE_DATE
98    27            1188685432      15-OCT-15 00:00:00
100   25            1188685432      16-OCT-15 00:00:00
101   26            1188685432      18-OCT-15 00:00:00
102   27            1904229547      14-OCT-15 00:00:00
103   25            1904229547      19-OCT-15 00:00:00

So to determine which schedule is in effect for an employee on any given date, I can run the following query, which seems to return what I want:

select Schedule_Cid, Effective_Date from Lei_Tas_People_Schedules
where Effective_Date = (select max(Effective_Date) from Lei_Tas_People_Schedules
where Lei_Tas_People_Schedules.User_Cid = '1188685432'
and Effective_Date <= '19-oct-2015');

Which in this example, will return: 26 18-OCT-15 00:00:00

So based on the above example, on Oct 19th, 2015 for employee 1188685432, the schedule with ID 26 was in effect.

The problem is, I sometimes need to get the schedule that is in effect for all 450 employees on a given date. My question is: what is the best solution that will return the schedule for all employees on a given date (without running the above query 450 times - which seems very inefficient)? Or is there a better way to design my data structure to accomplish what I want?

I have considered a stored procedure, but it seems like I would need a cursor, which I have heard should be avoided. I am not a SQL guru by any means, but am willing to learn.

Thanks a lot.

EDIT: Please forgive me, I can see my above select query is flawed. My question is still the same "How can I return the effective schedules for all 450 employees for a given date?"

EDIT: As far as this being a possible duplicate of another question, I will say the following: The answers I have received to this question seem different than the answer to the "possible duplicate" question. Maybe my question is about the same, but wouldn't alternative answers be worth keeping the question open? Especially since they appear to be correct answers. I do not know SO protocol, so I default to those who know.

Possible duplicate of [Select rows containing the group-wise maximum of a column](http://stackoverflow.com/questions/24787060/select-rows-containing-the-group-wise-maximum-of-a-column) — Jakub Kania, Oct 21 '15 at 13:10
@Nick.McDermaid Ha ha! Could it be so easy? Let me run some tests. — Joe Gayetty, Oct 21 '15 at 13:13
@Nick.McDermaid You are probably on the right track...but when I removed the employee filter from my query example it still returns just one row...I think must be due to the max(Effective_Date) function in my query? — Joe Gayetty, Oct 21 '15 at 13:15
No, an aggregate (here `MAX`) gives you back one record only. If you want a groupwise aggregate, you have to specify a `GROUP BY` clause. Please see my updated answer on that. — Thorsten Kettner, Oct 21 '15 at 13:17

score 1 · Answer 1 · answered Oct 21 '15 at 13:03

select user_cid, Schedule_Cid, Effective_Date
from (
select user_cid, Schedule_Cid, Effective_Date, 
row_number() over(partition by user_cid order by effective_date desc) as rn
from Lei_Tas_People_Schedules) t
where rn = 1

You can make use of the row_number function and get the corresponding row with latest date for each user.

Thorsten Kettner · Accepted Answer · 2015-10-21T13:11:10.717

What you want to do is rank your records, so as to find the best (i.e. newest) record per person. You do this with ROW_NUMBER, numbering each persons records such as to give the latest #1. Then only keep those.

select 
  user_cid,
  schedule_cid,
  effective_date
from
(
  select 
    user_cid,
    schedule_cid,
    effective_date, 
    row_number() over (partition by user_cid 
                       order by effective_date desc) as rn
  from lei_tas_people_schedules
  where effective_date <= date'2015-10-19'
)
where rn = 1;

You can do the same with your own approach by the way. Only, using an analytic function is supposed to be better, because you read your table only once.

select * 
from lei_tas_people_schedules
where (user_cid, effective_date) in 
(
  select user_cid, max(effective_date) 
  from lei_tas_people_schedules
  where effective_date <= date'2015-10-19'
  group by user_cid
);

Well Sir, I am amazed that your query runs perfectly as provided. I am certainly humbled before you SQL experts that is for sure. Please let me take a more thorough look and I will be back... — Joe Gayetty, Oct 21 '15 at 13:19
I will use your first suggestion since you suggest it may be more efficient. — Joe Gayetty, Oct 21 '15 at 13:38

Best way to return 450 rows with query that currently only returns one

2 Answers2