Let's say there is a table that has ID#, effective date, status, and some other data. Status is an employee's exempt/non-exempt status. If/when an employee changes from exempt to non-exempt, or vise versa, a new row is created on the table with the effective date of the change. A new row could also be created if some of the "other data" changed and the status did not.

I'm looking for a way to run a query that for some given month, for some list of people, would return:

• the ID#
• either "exempt" (if they were only exempt each day of the month), "non-exempt" (if they were only non-exempt each day of the month), or "both" (if they were only exempt each day of the month)
• the effective date of the exempt/non-exempt switch if they were "both"

I haven't been able to get anything to work yet... anyone have any ideas? Is something like this feasible? I believe the type of SQL I'm using is Oracle/PLSQL if that matters. TIA

I have referred to this article from time to time:

https://www.simple-talk.com/sql/database-administration/database-design-a-point-in-time-architecture/

there isn't just one solution, there are many, but this should at least get you thinking about things...I will poke around a bit more and possibly give some more input it it helps.

Additional pieces of info:

http://www.4guysfromrolla.com/webtech/041807-1.shtml

Just a quick guess, but sounds like you're looking for group by command. Something like (quick example):

Select "col_name1", "col_name2", etc. (or *)
from "table_name"
where "date" between 'x' and 'y' and "status" = x
group by "id"

SQL server? Oracle?

My assumptions are you don't have an employee table (without status/effective date) and that it needs to check the Status prior to the beginning period to see if it changed.

This query will get the ID, Status, Effective Date, but it will also return the effective date even if it is not Both.


select distinct
ID,
case when
exists
(
select
count(distinct Status)
from
tblSTATUS a
where
ID =e.ID
and
(
EffectiveDate >= (select MAX(EffectiveDate) from tblSTATUS b where b.ID=a.ID and EffectiveDate<='9/1/2016')
or
(
EffectiveDate>'9/1/2016'
and
EffectiveDate<'10/1/2016'
)
)
and
EffectiveDate<'10/1/2016'
GROUP by ID
having COUNT(distinct Status)>1
)
then 'Both'
else
(
select
distinct Status
from
tblSTATUS a
where
ID =e.ID
and
(
EffectiveDate >= (select MAX(EffectiveDate) from tblSTATUS b where b.ID=a.ID and EffectiveDate<='9/1/2016')
or
(
EffectiveDate>'9/1/2016'
and
EffectiveDate<'10/1/2016'
)
)
and
EffectiveDate<'10/1/2016'
)
end as status,
(
select
MAX(EffectiveDate)
from
tblSTATUS a
where
ID =e.ID
and
(
EffectiveDate >= (select MAX(EffectiveDate) from tblSTATUS b where b.ID=a.ID and EffectiveDate<='9/1/2016')
or
(
EffectiveDate>'9/1/2016'
and
EffectiveDate<'10/1/2016'
)
)
and
EffectiveDate<'10/1/2016'
GROUP by ID
)
as effectivedate


from
(
select
ID,
Status,
EffectiveDate
from
tblSTATUS e
where
ID in ('employeeid1','employeeid2', 'employeeid3')
and
(
EffectiveDate >= (select MAX(EffectiveDate) from tblSTATUS s where s.ID=e.ID and EffectiveDate<='9/1/2016')
or
(
EffectiveDate>'9/1/2016'
and
EffectiveDate<'10/1/2016'
)
)
and
EffectiveDate<'10/1/2016'
) as e

Is there a need to do this all through a SQL statement in terms of efficiency or performance, or because of the programming language it's built on?

Although it would be slightly less efficient, you could really just do this programmatically using if/else statements and breaking it up into multiple SQL statements. It should end up being quite a bit simpler that way, and I don't see the difference in efficiency being all that noticeable.

You would just essentially be breaking KK's response up into individual statements and testing them each from the programming language side.

I just realized I forgot to report back that KK's solution works exactly as I hoped. Thanks to KK and everyone! I had been trying to get something like this working for a while and I appreciate the help.