Need help with max value in date column

Question

Post reply

Need help with max value in date column

mxy

SSCarpal Tunnel

Points: 4036
More actions
January 6, 2014 at 8:09 pm

#286933

create table employee
(employee_id int
,date_joined datetime
,date_ datetime
,sal float
,due_amount float
)
truncate table employee
insert into employee
values ('1234', '10/20/2011', '12/11/2013 20:30', 1.2, 3.445)
insert into employee
values('1234', '10/20/2011','12/10/2013 18:57',2.2, 3.433)
insert into employee
values('1234', '10/20/2011','12/10/2013 3:35',2.222, 3.42545)
insert into employee
values ('1234', '10/21/2011', '12/6/2013 20:59', 4.2, 3.4)
insert into employee
values('1234', '10/21/2011','12/11/2013 23:45',5.2, 3.4)
insert into employee
values('1234', '10/21/2011','12/10/2013 19:23',6.2, 3.4)
select * from employee
select distinct
employee_id,date_joined ,date_ , ROUND((sal+due_amount),2)
from employee
I am trying to achieve unquie combination value based on employee_id,date_joined and at the same time max value from date_ column.
Desired out put as below
employee_iddate_joined date_ (No column name)
12342011-10-21 00:00:00.0002013-12-11 23:45:00.0008.6
12342011-10-20 00:00:00.0002013-12-11 20:30:00.0004.64
thanks for your help!

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Christian Graus SSC Eights! Points: 963 More actions · Answer 1

select employee_id,date_joined ,max(date_) as maxDate, sum(ROUND((sal+due_amount),2))

from employee

group by employee_id, date_joined

I cannot work out where 8.6 and 4.64 are supposed to come from. If I knew that, I would know if it was possible with an aggregate in the main query, or if it requires a CTE to tease out and merge in later.

select employee_id,date_joined ,max(date_) as maxDate, avg(sal + due_amount)

from employee

group by employee_id, date_joined

order by employee_id, date_joined desc

gives 8.6 for the first value, but 5.30 for the second......

mxy SSCarpal Tunnel Points: 4036 More actions · Answer 2

mxy

SSCarpal Tunnel

Points: 4036

January 6, 2014 at 9:05 pm

#1677926

thanks for your reply.

i think you r right

Christian Graus SSC Eights! Points: 963 More actions · Answer 3

Great. If I'm not, you need to explain where you want those numbers to come from, what they are calculating, and we can try again 🙂

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 4

We can use ROW_NUMBER() to assign sequences for each unique combination in descending order by date_ and then select the rows that have a"1" in them. Like this... (and thank you VERY much for posting readily consumable data to test with! 🙂 )

WITH

ctePreSort AS

(

SELECT RowNum = ROW_NUMBER()OVER(PARTITION BY employee_id, date_joined ORDER BY date_ DESC)

,employee_id

,date_joined

,date_

,SomeCalculatedColumn = ROUND((sal+due_amount),2)

FROM dbo.employee

)

SELECT employee_id

,date_joined

,date_

,SomeCalculatedColumn

FROM ctePreSort

WHERE RowNum = 1

;

[EDIT]

Apologies... forgot to add the results.

employee_id date_joined date_ SomeCalculatedColumn

----------- ----------------------- ----------------------- ----------------------

1234 2011-10-20 00:00:00.000 2013-12-11 20:30:00.000 4.64

1234 2011-10-21 00:00:00.000 2013-12-11 23:45:00.000 8.6

(2 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christian Graus SSC Eights! Points: 963 More actions · Answer 5

True - the real question is, does his calculated column come from a single row, or is it an aggregate ? I couldn't find any combination that gave the values he gave, but I found an aggregate that gave one of the two 🙂

I'm not convinced my answer was correct, I think we need to dig more in to what that calculated column is supposed to show, given that there's no way I can see to get the result he gave.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 6

Christian Graus (1/6/2014)
True - the real question is, does his calculated column come from a single row, or is it an aggregate ? I couldn't find any combination that gave the values he gave, but I found an aggregate that gave one of the two 🙂
I'm not convinced my answer was correct, I think we need to dig more in to what that calculated column is supposed to show, given that there's no way I can see to get the result he gave.

According to the data, it comes from a single row. The code I posted produces the exact result set requested.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christian Graus SSC Eights! Points: 963 More actions · Answer 7

Damn - you're right. I did that, without the row number, and didn't think the right values came out, that's why I started looking at group by.

To the OP - my solution is wrong 🙁

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 8

Christian Graus (1/6/2014)
Damn - you're right. I did that, without the row number, and didn't think the right values came out, that's why I started looking at group by.
To the OP - my solution is wrong 🙁

Part of the problem was that the OP listed the rows out of order according to the date-joined. Almost threw me, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mxy SSCarpal Tunnel Points: 4036 More actions · Answer 9

mxy

SSCarpal Tunnel

Points: 4036

January 6, 2014 at 9:21 pm

#1677934

thanks Jeff you are so helpful as always !!!!!!

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

mxy (1/6/2014)
thanks Jeff you are so helpful as always !!!!!!

Thanks but Christian likely had it, as well. It just didn't look right and so he took another path. I've been down that road myself a thousand times.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 11

Christian Graus (1/6/2014)
Damn - you're right. I did that, without the row number, and didn't think the right values came out, that's why I started looking at group by.
To the OP - my solution is wrong 🙁

Multiple solutions for the same problem are a good thing. Do you still have that code to post?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christian Graus SSC Eights! Points: 963 More actions · Answer 12

No, I've answered two questions since then, I deleted it. I do think I looked through the correct numbers being there, and thus pursued the wrong solution, after reading the question 5 times for a clue as to the business rules for where that value came from 🙂

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 13

jeff,

I tried the below solution, will it effect the peformance ?

select

employee.employee_id ,date_joined ,aa.date_ , ROUND((sal+due_amount),2)

from employee

inner join (select employee_id,MAX(date_) 'date_' from employee group by date_joined,employee_id)aa

on aa.date_=employee.date_ and aa.employee_id =employee.employee_id

Correct me if this approach is not right

Christian Graus SSC Eights! Points: 963 More actions · Answer 14

It's a subquery and so it will affect performance. It's good practice to avoid subqueries where-ever possible. The approach I took, assumed you were looking for an aggregate value. The approach Jeff took, is the approach I would have taken if i'd paid more attention to the question. It's the best possible way to do this. I would never say 'don't EVER use subqueries', SQL is too nuanced for that, the answer is 'it depends' far too often. But, I would avoid subqueries where-ever I could, and I'd definitely use Jeff's code over what you posted.

If your table doesn't have much data in it, create a similar table and fill it with millions of records, then time both bits of SQL, the best way to find out which is faster, is to test it. Of course, if your table won't ever have a lot of records, then it really doesn't matter as much as making sure the people working on the code, can understand it and maintain it.