January 6, 2014 at 8:09 pm
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!
January 6, 2014 at 8:58 pm
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......
January 6, 2014 at 9:05 pm
thanks for your reply.
i think you r right
January 6, 2014 at 9:07 pm
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 🙂
January 6, 2014 at 9:07 pm
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
January 6, 2014 at 9:11 pm
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.
January 6, 2014 at 9:14 pm
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
January 6, 2014 at 9:18 pm
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 🙁
January 6, 2014 at 9:21 pm
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
January 6, 2014 at 9:21 pm
thanks Jeff you are so helpful as always !!!!!!
January 6, 2014 at 9:23 pm
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
January 6, 2014 at 9:24 pm
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
January 6, 2014 at 9:31 pm
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 🙂
January 7, 2014 at 5:26 am
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
January 7, 2014 at 2:24 pm
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.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply