What's new

Closed Select max date for each type for each employee

Status
Not open for further replies.

Heizengberg

Eternal Poster
Joined
Jul 12, 2017
Posts
597
Reaction
120
Points
312
mga sir ganto kasi table struct ko.

|emp_id| type | date | amount
----------------------------------------------------------------
| 1 | transpo | 2018-06-01 | 50
| 1 | others | 2018-06-01 | 50
| 1 | grade | 2018-06-01 | 50
| 2 | transpo | 2018-06-01 | 50
| 2 | others | 2018-06-01 | 50
| 2 | grade | 2018-06-01 | 50
| 1 | transpo | 2018-06-02 | 100
| 2 | transpo | 2018-06-02 | 90

ang gusto ko sana is makuha yung pinaka latest na allowances nila per type (transpo,others,grade) for each employee and each type

sample result for employee 1:
grade - 50
transpo - 100
others - 50
 
Use while boss

Tapos sa sql select mo

Select * from table order by desc group by ammount ung pnaka mataas lalabas jan

Try mu yan
 
SELECT empid,type,MAX(date) as date, amount FROM TABLE ORDER BY empid,date DESC GROUP BY type,empid

result.png
 

Attachments

SELECT empid,type,MAX(date) as date, amount FROM TABLE ORDER BY empid,date DESC GROUP BY type,empid

View attachment 439807


sir minodify ko yung code niyo ng unti ito ginawa ko

SELECT emp_id,type,MAX(effective_date) as effective_date,amount FROM hr_eac_allowance where emp_id='1011' GROUP BY type,emp_id

nakukuha niya yung max date kaso mali yung amount na nilabas sir .

eto yung nakuha niya:
upload_2018-8-16_11-51-30.png

pero dapat ang nakuha niya dun sa transpo is yung 150
upload_2018-8-16_11-53-3.png
 

Attachments

yung 150 dapat makuha sa transpo sir kasi ayun yung latest na allowance niya sa type na yun


e2 try mo...

result.png


Code:
SELECT emp_id,type,effective_date,amount
FROM hr_eac_allowance where (emp_id,effective_date,type) in
    (select emp_id, max(effective_date) as effective_date,type
    from hr_eac_allowance
    where emp_id = '1011'
    group by emp_id,type
    )
 

Attachments

Last edited:
Table Structure and Data
upload_2018-8-16_14-6-13.png


SQL Query
Code:
SELECT * FROM allowances INNER JOIN (SELECT type, MAX(Date) as TopDate FROM allowances  GROUP BY type) AS EachType ON EachType.TopDate = allowances.Date AND EachType.type = allowances.type  WHERE emp_id='1011'

Result
upload_2018-8-16_14-6-33.png
 

Attachments

Last edited:
ito po para makuha mo pinakalatest
Code:
select top 1 *
from hr_eac_allowance
where emp_id='1011'
order by effective_date desc
 
Status
Not open for further replies.
Back
Top