filter by latest date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

filter by latest date

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2010-03-03 : 11:59:07

Hi All.
I would like create query to return latest date for according user. The Table1 has EmpId, CallInDate fields. My code is wrong becuase it looking max(date) for whole table and I need only for according user. How to fix it?
select empid, callindate
from table1
where empid=141
and callindate in
(SELECT MAX(callindate)
FROM table1)
Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-03 : 12:00:44

[code]select empid, max(callindate)
from table1
where empid=141
group by empid[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:04:17

[code]select empid, callindate
from table1 t1
where empid=141
and callindate in
(SELECT MAX(callindate)
FROM table1
where empid=t1.empid)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-03-03 : 12:20:25

ms65g way


select 
   empid, 
   callindate
from table1 t1
where empid=141
and Exists
(SELECT MAX(callindate)
        FROM table1
        where empid=t1.empid)

PBUH

eugz
Posting Yak Master

210 Posts

Posted - 2010-03-03 : 12:25:59

Thanks for help. It's works.
How to modify that query to return max(date) and only latest date before?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:32:57

do you mean recent 2 dates ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eugz
Posting Yak Master

210 Posts

Posted - 2010-03-03 : 12:38:29

Yes, it is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:41:14

are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eugz
Posting Yak Master

210 Posts

Posted - 2010-03-03 : 12:41:48

yes

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-03 : 12:44:41

quote:

Originally posted by Idera

ms65g way


select 
   empid, 
   callindate
from table1 t1
where empid=141
and Exists
(SELECT MAX(callindate)
        FROM table1
        where empid=t1.empid)

PBUH

Are you really all right??

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-03 : 12:45:51

quote:
Originally posted by visakh16

select empid, callindate
from table1 t1
where empid=141
and callindate in =
(SELECT MAX(callindate)
FROM table1
where empid=t1.empid)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

when subquery returns one value you can use = operator always!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:48:23

quote:
Originally posted by eugz

yes

ok then use either of below


SELECT empid, callindate
FROM
(
select empid, callindate,row_number() over (partition by empid order by callindate desc) as seq
from table1
where empid=141
)t
WHERE seq < = 2 

SELECT t1.empid,t2.callindate
FROM (SELECT DISTINCT empid FROM table1) t1
CROSS APPLY (SELECT TOP 2 callindate
             FROM table1
             WHERE empid=t.empid
             ORDER BY callindate DESC)t2
WHERE t1.empid=141

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 12:49:31

quote:
Originally posted by ms65g

quote:
Originally posted by visakh16

select empid, callindate
from table1 t1
where empid=141
and callindate in =
(SELECT MAX(callindate)
FROM table1
where empid=t1.empid)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

when subquery returns one value you can use = operator always!

I know that

Didnt notice the IN
just copy pasted from OP's post

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-03-03 : 12:58:26

quote:
Originally posted by ms65g

quote:
Originally posted by Idera

ms65g way

select
empid,
callindate
from table1 t1
where empid=141
and Exists
(SELECT MAX(callindate)
FROM table1
where empid=t1.empid)

PBUH

Are you really all right??

Why do you ask?

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-03-03 : 13:09:44

I just said it because you always have used EXISTS clauses in most of your solutions.Thats it.
Visakh also used Apply clauses most of the time thats why his custom title is "Very Important crosS Applying yaK Herder".
And I think there is nothing to get so furious about.

PBUH

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-03 : 13:11:39

quote:
Originally posted by visakh16

quote:
Originally posted by eugz

yes

ok then use either of below

SELECT empid, callindate
FROM
(
select empid, callindate,row_number() over (partition by empid order by callindate desc) as seq
from table1
where empid=141
)t
WHERE seq < = 2

SELECT t1.empid,t2.callindate
FROM (SELECT DISTINCT empid FROM table1) t1
CROSS APPLY (SELECT TOP 2 callindate
FROM table1
WHERE empid=t.empid
ORDER BY callindate DESC)t2
WHERE t1.empid=141

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Is not this easier?

SELECT TOP 2 empid, callindate
FROM table1
WHERE empid = 141
ORDER BY collindate DESC

Sachin.Nand

2937 Posts

Posted - 2010-03-03 : 13:13:16

What if he does not have any filter?

PBUH

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-03 : 13:14:08

quote:
Originally posted by Idera

I just said it because you always have used EXISTS clauses in most of your solutions.Thats it.
Visakh also used Apply clauses most of the time thats why his custom title is "Very Important crosS Applying yaK Herder".
And I think there is nothing to get so furious about.

PBUH

I am really sorry for this. I think wrong!
No problem

My favorite in SQL is just EXISTS predicate

Sachin.Nand

2937 Posts

Posted - 2010-03-03 : 13:17:51

No problem.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 09:33:04

quote:
Originally posted by ms65g

quote:
Originally posted by visakh16

quote:
Originally posted by eugz

yes

ok then use either of below

SELECT empid, callindate
FROM
(
select empid, callindate,row_number() over (partition by empid order by callindate desc) as seq
from table1
where empid=141
)t
WHERE seq < = 2

SELECT t1.empid,t2.callindate
FROM (SELECT DISTINCT empid FROM table1) t1
CROSS APPLY (SELECT TOP 2 callindate
FROM table1
WHERE empid=t.empid
ORDER BY callindate DESC)t2
WHERE t1.empid=141

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Is not this easier?

SELECT TOP 2 empid, callindate
FROM table1
WHERE empid = 141
ORDER BY collindate DESC

easier for this scenario. but i dont think OP reqmnt is to run this for 141 employee (or single employee) always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-07 : 15:13:41

quote:

Originally posted by Idera

ms65g way


select 
   empid, 
   callindate
from table1 t1
where empid=141
and Exists
(SELECT MAX(callindate)
        FROM table1
        where empid=t1.empid)

PBUH

Hi,
In fact I do not suggest this way in this case but I offer this way just for fun.

your query should change to this (you missed a having clause):

SELECT empid,
       callindate
  FROM @table1 t1
 WHERE empid=141
   AND EXISTS (SELECT 1 
                 FROM @table1 t2
                WHERE t1.empid = t2.empid
               HAVING MAX(t2.callindate) = t1.callindate);

And my solution for this case is following method (NOT EXISTS() Predicate)
But Madhivanan said this type solutions is not efficient

Execuse me for very bad English.

SELECT empid,
       callindate
  FROM @table1 t1
 WHERE t1.empid = 141
   AND NOT EXISTS 
       (SELECT * 
          FROM @table1 t2 
         WHERE t2.empid=t1.empid 
           AND t2.callindate > t1.callindate);

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources