how to show the last date in the month
-
hi
how to show the last date in the month that the employee work.
this is my table
---------------------
ID date_e val_unit
4807756 2/12/2007 1
4807756 3/12/2007 1
4807756 15/12/2007 4
2222222 12/12/2007 6
2222222 17/12/2007 9
9999999 29/12/2007 4
9999999 30/12/2007 5
--------------------------------------------
how to show only the last date that the employee work in the month
like this
---------
4807756 15/12/2007 4
2222222 17/12/2007 9
9999999 30/12/2007 5
4807756 15/12/2007 4
TNX
-
[font="Verdana"]Based on your situation use one of the below -
select id, max(workdate), max(val_unit) from empwork1
group by id
select e.id, e.date_e, e.val_unit from empwork1 e
INNER JOIN
(select id, max(date_e) as wd from empwork1
group by id ) A
ON (a.id = e.id and a.wd = e.date_e)[/font]
-
Not entirely clear what you want, ID 4807756 seems to turn up twice in your expected results?
Maybe this
with cte as (select ID,date_e,val_unit,
row_number() over(partition by ID order by date_e desc) as rn
from mytable
)
select ID,date_e,val_unit
from CTE
where rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
That will only work for one month of data. if the table has more than one month - you need to add to the group by.
This won't scale well, because of the function in the group by. You can push that logic into a computed persisted column (which can then be added to the index) to get some speed back if needed.
select
id,
max(date_e)
from
mytable
group by
id,
left(cast(char,date_e,112),6)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
This should do the trick.
select *
from @tb e
where e.date_e = (select max(date_e) from @tb a where e.id = a.id)
order by e.id
-
Here's another version that will give monthly results for each employee
with cte as (select ID,date_e,val_unit,
row_number() over(partition by ID, convert(char(6),date_e,112) order by date_e desc) as rn
from mytable)
select ID,date_e,val_unit
from cte
where rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply