Need an case statement??? - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Need an case statement???

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2014-01-27 : 23:00:46

Guys I am having a great deal of trouble with a query and trying to get the data that I need.

I am trying to get each login, location, supervisor_name and in this it must have the details showing the latest column no duplicates of the logins.

I have the problem at the moment that its showing some login's that are duplicates because they may have different supervisor_names.

Here is what I am using:
select agent_login_id,location,supervisor_name,title,MAX(EFFECTIVE_start_DAY
from agentsnew
where location in ('AU','US','EU')
and (Title like '%Support%%Engineer%' or Title like '%Enterprise%')
and (EFFECTIVE_END_DAY >= '2013/06/01' or EFFECTIVE_END_DAY is null)
group by agent_login_id,location,supervisor_name,title

Some results:

agent_login_id location supervisor_name Start_date
Login1 US Manager1 2012-07-30
Login2 AU Manager2 NULL
Login3 US Manager3 2013-08-05
Login3 US Manager4 2012-10-01
Login3 US Manager3 NULL
Login4 EU Manager5 NULL

Note login3, this is repeated three times, I need to some how show only this one. While it seems easy enough to just target the NULL, not all of the logins will be in the same situation, in that they may not have NULL, and it may have a date.

Let me know if you need more information.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-28 : 00:50:04

If you have posted some DDL of the tables involved and sample DML, it would have been so easier to answer this query.. Now its more of a guessing game. First thing Login3 is repeating 3 times because it has different manager names and dates and since you are looking for a distinct record, the given record is also a distinct one.
So I hope if you are looking for the better answer, I guess you have to post the DML related to the agentsnew table and you will get response like a quick charm...

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-28 : 03:27:51

[code]
SELECT agent_login_id,location,supervisor_name,title,EFFECTIVE_start_DAY
FROM
(
select agent_login_id,location,supervisor_name,title,EFFECTIVE_start_DAY ,
ROW_NUMBER() OVER (PARTITION BY agent_login_id ORDER BY COALESCE(EFFECTIVE_start_DAY,0) DESC) AS Seq
from agentsnew
where location in ('AU','US','EU')
and (Title like '%Support%%Engineer%' or Title like '%Enterprise%')
and (EFFECTIVE_END_DAY >= '2013/06/01' or EFFECTIVE_END_DAY is null)
group by agent_login_id,location,supervisor_name,title
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

2revup
Posting Yak Master

112 Posts

Posted - 2014-01-28 : 16:29:46

perfect... as usual. visakh16 do you have any good guides that you could point me to around 'ROW_NUMBER() OVER (PARTITION BY' I would like to try and understand more about how it works.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-28 : 16:38:39

Here you go.. I have written an article related to ROW_NUMBER() and other RANKING functions in SQL SERVER. Please go through..

http://sqlsaga.com/sql-server/what-is-the-difference-between-rank-dense_rank-row_number-and-ntile-in-sql-server/

Good Luck :)

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-29 : 04:31:48

quote:
Originally posted by 2revup

perfect... as usual. visakh16 do you have any good guides that you could point me to around 'ROW_NUMBER() OVER (PARTITION BY' I would like to try and understand more about how it works.

see various uses of row_number here

http://beyondrelational.com/modules/2/blogs/70/posts/10802/multipurpose-rownumber-function.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources