Advice on combining records...gotta be better way...

[tmcfar]

Hi,

This code works for me...but it seems long and convoluted. Does anyone have a better / cleaner way to do this?

I have ...

EmplID Action_Reason Effective_Date
123456 'Return From Leave' 2004-06-08
123456 'Leave of Absence' 2004-05-19
123456 'Return From Leave' 2004-06-09

I need to calculate the length of the LOA (where I have data) by subtracting the LOA effective date (5/19/07) from the Return from Leave Effective Date (6/9/07). Unfortunately, my data has lots of Leaves without a Return and vice versa.

I split the original table into 2 queries...one with all of the Return From Leave's and one with everything else.

Then I wrote this...
(Query 2 - Return from Leave's, Query 1 - everything else)

SELECT Query1.LocCode, Query1.EmplID, Query1.Name, Query1.EffDate AS LeaveDate, Min(Query2.EffDate) AS ReturnDate
FROM Query1 INNER JOIN Query2 ON Query1.EmplID = Query2.EmplID
WHERE (((Query2.EffDate)>[query1].[effdate]))
GROUP BY Query1.LocCode, Query1.EmplID, Query1.Name, Query1.EffDate
ORDER BY Query1.LocCode, Query1.Name;

Which from this table data...
EmplID Action_Reason EffDate
123456 Non FMLA - No PTO 2004-05-19
123456 Return From Leave 2004-06-09
123456 FMLA - No PTO 2006-10-24
123456 Return From Leave 2006-11-03

I get...
EmplID LeaveDate ReturnDate
87854 2004-05-19 2004-06-09
87854 2006-10-24 2004-11-03

Then I use this to do the calc and sums...

SELECT Query3.LocCode, Query3.Name, Query3.EmplID, Sum(CDate([ReturnDate])-CDate([LeaveDate])) AS LOA_Len
FROM Query3
GROUP BY Query3.LocCode, Query3.Name, Query3.EmplID
HAVING (((Sum(CDate([ReturnDate])-CDate([LeaveDate])))>=30))
ORDER BY Query3.LocCode, Query3.Name;

Seems way to complicated for what it is.

Thanks in advance for helping me learn!!

= )
Tom

 

[rac2]

Generally, that is what you need to do to for this kind of problem.

You must write a query to place the start and end dates in the same row so that you can calculate the time period length. That work might be done using only the employee id, the dates, and the Action_Reason.
Then that result can be joined with the employee table to show employee data such as the name and location along with the time period length, the start and end dates.


This is a consequence of designing the database with an event table that has only the event and the date in a row. An alternative would be to design the table with columns for the start and end date of a process (going on vacation is a process with a beginning and an ending date) in the same row. When the beginning event occurs, the row is created; when the ending event occurs the row is updated. This simplifies the reporting queries.

 

[tmcfar]

Hi rac2,

That's pretty much what I have tried to do. My data comes from a second system so I have no control over the format of what I receive.

Thanks for input.
Tom