Getting the latest transaction for 2 items 1

[zevw]

I have a database where the vacation and sick for each employee is entered. I would like to get the most recent date for each employee when they received a vacation pay and the most recent date when they received their sick pay.

With the query below I get all the dates that they received vacation and sick pay. I grouped by employee id and by pay type. and tried using last or max but that gives me the latest date for both sick and pay. How can I get the most recent date for vacation and the most recent pay for vacation.

This is my SQL Statement:

SELECT DISTINCTROW tblPRItemHist.EmployeeID, tblPRItemPayHist.PayTypeID, tblPRPayType.PayDescription, tblPRItemHist.CheckDate
FROM tblPRItemHist INNER JOIN (tblPRPayType INNER JOIN tblPRItemPayHist ON tblPRPayType.PayTypeID = tblPRItemPayHist.PayTypeID) ON tblPRItemHist.ItemID = tblPRItemPayHist.ItemID
WHERE (((tblPRItemHist.Voided)=No) AND ((tblPRPayType.ShortName)="Vacation" Or (tblPRPayType.ShortName)="Sick Pay"))
GROUP BY tblPRItemHist.EmployeeID, tblPRItemPayHist.PayTypeID, tblPRPayType.PayDescription, tblPRItemHist.CheckDate
ORDER BY tblPRItemHist.EmployeeID, tblPRItemHist.CheckDate DESC;

Thanks in advance for all your help;

 

[rac2]

There are different ways to get there. Easy to understand way might be to create a couple of intermediate queries, one with the results for vacation, the other for sick pay. Then JOIN them on EmployeeID, or simply UNION them, depending on whether you need the dates side-by-side in columns or one-after-the-other in rows.

For most recent vacation check

SELECT  
       tblPRItemHist.EmployeeID, 
       tblPRItemPayHist.PayTypeID, 
       tblPRPayType.PayDescription, 
       MAX( tblPRItemHist.CheckDate )

FROM tblPRItemHist 
INNER JOIN tblPRItemPayHist 
    ON tblPRItemPayHist.ItemID = tblPRItemHist.ItemID
INNER JOIN tblPRPayType 
    ON tblPRPayType.PayTypeID = tblPRItemPayHist.PayTypeID

WHERE tblPRItemHist.Voided = No AND 
       tblPRPayType.ShortName = "Vacation" 

GROUP BY tblPRItemHist.EmployeeID, 
         tblPRItemPayHist.PayTypeID, 
         tblPRPayType.PayDescription

ORDER BY tblPRItemHist.EmployeeID;

Save this and call it LastVacationPay.

And the other one called LastSickPay.

SELECT  
       tblPRItemHist.EmployeeID, 
       tblPRItemPayHist.PayTypeID, 
       tblPRPayType.PayDescription, 
       MAX( tblPRItemHist.CheckDate ) AS LastCheckDate

FROM tblPRItemHist 
INNER JOIN tblPRItemPayHist 
    ON tblPRItemPayHist.ItemID = tblPRItemHist.ItemID
INNER JOIN tblPRPayType 
    ON tblPRPayType.PayTypeID = tblPRItemPayHist.PayTypeID

WHERE tblPRItemHist.Voided = No AND 
        tblPRPayType.ShortName = "Sick Pay"

GROUP BY tblPRItemHist.EmployeeID, 
         tblPRItemPayHist.PayTypeID, 
         tblPRPayType.PayDescription

ORDER BY tblPRItemHist.EmployeeID;

Combine them with JOIN

SELECT E.EmployeeID, 
       A.PayTypeID, 
       A.PayDescription, 
       A.LastCheckDate AS PayVacation,
       B.LastCheckDate AS PaySick
FROM Employees E
LEFT JOIN LastVacationPay A ON A.EmployeeID = E.EmployeeID
LEFT JOIN LastSickPay B ON B.EmployeeID = E.EmployeeID

Use the base table of Employees to insure that you get a row for every employee. That is also the reason for the LEFT JOIN, without these the result would include only those with both sick and vacation pay.

Or combine them as rows.

SELECT E.EmployeeID, 
       A.PayTypeID, 
       A.PayDescription, 
       A.LastCheckDate
FROM Employees E
LEFT JOIN LastVacationPay A ON A.EmployeeID = E.EmployeeID

UNION

SELECT E.EmployeeID, 
       B.PayTypeID, 
       B.PayDescription, 
       B.LastCheckDate
FROM Employees E
LEFT JOIN LastSickPay B ON B.EmployeeID = E.EmployeeID

Do not be concerned about my re-arrangement of the JOIN conditions. That is just my way of writing them. I find it easier to follow than the Access style.

 

[zevw]

Thank You so much rac2

I decided to go a little bit of a different route.

I first create a table with all the vacation and sick pay.

SELECT DISTINCTROW tblPRItemHist.EmployeeID,tblPRItemPayHist.ItemPayID, tblPRItemPayHist.PayTypeID, tblPRPayType.PayDescription, tblPRItemHist.CheckDate INTO TmpVacSick FROM tblPRItemHist INNER JOIN (tblPRPayType INNER JOIN tblPRItemPayHist ON tblPRPayType.PayTypeID = tblPRItemPayHist.PayTypeID) ON tblPRItemHist.ItemID = tblPRItemPayHist.ItemID WHERE (((tblPRItemHist.Voided) = No) And (tblPRPayType.ShortName) = 'Vacation' Or (tblPRPayType.ShortName) = 'Sick Pay')) GROUP BY tblPRItemHist.EmployeeID, tblPRItemPayHist.ItemPayID, tblPRItemPayHist.PayTypeID, tblPRPayType.PayDescription, tblPRItemHist.CheckDate ORDER BY tblPRItemHist.EmployeeID, tblPRItemPayHist.PayTypeID, tblPRItemHist.CheckDate DESC;

I then create a second table with the count for each transaction based on each employee and pay type, based from earliest date to most recent date.

SELECT Count(*) AS [Counter], A.EmployeeID, A.ItemPayId, A.PayTypeId, A.PayDescription, A.CheckDate INTO TmpVacSickCnt FROM TmpVacSick AS A INNER JOIN TmpVacSick AS B ON (A.CheckDate <= B.CheckDate) AND (A.PayTypeId = B.PayTypeId) AND (A.ItemPayId<=B.ItemPayId) AND (A.EmployeeID=B.EmployeeID) GROUP BY A.EmployeeID, A.PayTypeId, A.PayDescription, A.CheckDate, A.ItemPayId ORDER BY A.EmployeeID, A.PayTypeId, A.CheckDate;

Once this is done in my subreport I have this string as the recordsource.

SELECT TmpVacSickCnt.EmployeeID, tblPRPayType.ShortName, TmpVacSickCnt.CheckDate FROM TmpVacSickCnt INNER JOIN tblPRPayType ON TmpVacSickCnt.PayTypeId = tblPRPayType.PayTypeID WHERE (((TmpVacSickCnt.Counter)=1)) ORDER BY TmpVacSickCnt.EmployeeID, tblPRPayType.ShortName DESC;

Thanks for you help;

William