🔒 Closed Select max date for each type for each employee

[Heizengberg]

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

 

[Heizengberg]

up

 

[Phcmjal]

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

 

[Funyarinpa]

SELECT type, amount FROM tbl WHERE emp_id = [value]

 

[Heizengberg]

SELECT type, amount FROM tbl WHERE emp_id = [value]

di to sir. pano pag may magkaparehas na type . kailangan makuha yung pinaka latest

 

[Heizengberg]

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

anong while sir?

 

[Phcmjal]

anong while sir?

Search no po kaya haha anu ba language n gmit mo?

 

[jeff1228]

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

 

[Heizengberg]

Search no po kaya haha anu ba language n gmit mo?

php sir . kaso need ko kasi sir sql lang talaga

 

[Heizengberg]

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:


pero dapat ang nakuha niya dun sa transpo is yung 150

 

[Heizengberg]

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

View attachment 439807

eto din na try ko sir same result SELECT emp_id,type,MAX(effective_date) as effective_date, amount FROM hr_eac_allowance where emp_id='1011' GROUP BY type,emp_id ORDER BY emp_id,effective_date DESC

 

[Phcmjal]

Hindi nmn 150 nklagay sa data mo 100 lng db

 

[Phcmjal]

Go

Hindi nmn 150 nklagay sa data mo 100 lng db

Group by empid at type gawin mo or ung ammount

 

[jeff1228]

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:
View attachment 439819

pero dapat ang nakuha niya dun sa transpo is yung 150
View attachment 439821

anu ba dapat ung amount? issum ba nia ung amount?

 

[jeff1228]

-double post-

 

[Heizengberg]

-double post-

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

 

[Phcmjal]

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

Max(amount)

 

[jeff1228]

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

e2 try mo...

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
    )

 

[syml1nk]

Table Structure and Data



SQL Query

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

 

[Skywalker22]

ito po para makuha mo pinakalatest

select top 1 *
from hr_eac_allowance
where emp_id='1011'
order by effective_date desc