How to do this Query - 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 2000 Forums

SQL Server Development (2000)

How to do this Query

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2012-01-30 : 08:18:18

Hi Guys,
i have table something similar below

Manager Employee Dept Dt_End DT_start
M1 E1 Dept1 12/12/2008 1/1/2005
M1 E1 Dept1 30/10/2010 31/12/9999
M2 E2 Dept2 12/12/2008 1/1/2005
M3 E3 Dept3 12/12/2008 31/12/9999
M4 E4 Dept1 12/12/2008 1/1/2006
M4 E4 Dept1 30/10/2010 31/12/9999

i want to select only the records which is having max date. but for M1 and E1 record should not be picked up, b'coz it end dated once. how can i do. For the above data it should select only M3 E3 record?

Advise me pls

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-30 : 08:54:33

"i want to select only the records which is having max date"
the max date is based on which column ?

what do you mean by "end dated once" ?

Can you show us the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Sarakumar
Posting Yak Master

108 Posts

Posted - 2012-01-30 : 09:08:19

Hi See the table below..in my previous mail. i give wrong table.
E1 works for M1 two times. First time he worked from 2001(dt_start) to 2005 and again starting from 2010 to max date(dt_end). But my requirement to select the employee and manager who are currently active and their relationship should exists only once. For the ex blow, it shoul select only M3, E3 record
Manager Employee Dept DT_Start Dt_End
M1 E1 Dept1 12/12/2001 1/1/2005
M1 E1 Dept1 30/10/2010 31/12/9999
M2 E2 Dept2 12/12/2003 1/1/2005
M3 E3 Dept3 12/12/2008 31/12/9999
M4 E4 Dept1 12/12/2004 1/1/2006
M4 E4 Dept1 30/10/2010 31/12/9999

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-30 : 09:11:29

[code]
select Manager, Employee
from yourtable
group by Manager, Employee
having count(*) = 1
and max(Dt_End) = '9999-12-31'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Sarakumar
Posting Yak Master

108 Posts

Posted - 2012-02-01 : 22:59:45

Sorry, requirement is to get the records which are not currently active. dt_end is used to determaine whether it is active or not.dt_end 31/12/999 meaning it is active.i cannot use having count(*) =1 , b'coz there may be many records in this criteria. i want to select M2 ,E2 combinations two records as out put for my query.
Manager Employee Dept DT_Start Dt_End
M1 E1 Dept1 12/12/2001 1/1/2005
M1 E1 Dept1 30/10/2010 31/12/9999
M2 E2 Dept2 12/12/2003 1/1/2005
M2 E2 Dept2 12/12/2006 1/1/2010
M3 E3 Dept3 12/12/2008 31/12/9999
M4 E4 Dept1 12/12/2004 1/1/2006
M4 E4 Dept1 30/10/2010 31/12/9999

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-02 : 04:29:31

[code]
select Manager, Employee
from yourtable
group by Manager, Employee
having count(*) = 1
and max(Dt_End) <> '9999-12-31'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Sarakumar
Posting Yak Master

108 Posts

Posted - 2012-02-26 : 10:27:15

Manager Employee Dept DT_Start Dt_End
M1 E1 Dept1 12/12/2001 1/1/2005
M1 E1 Dept1 30/10/2010 31/12/9999
M2 E2 Dept2 12/12/2003 1/1/2005
M2 E2 Dept2 12/12/2006 1/1/2010
M3 E3 Dept3 12/12/2008 31/12/9999
M4 E4 Dept1 12/12/2004 1/1/2006
M4 E4 Dept1 30/10/2010 31/12/9999

Hi
i tried your query. but what happens, is it still returning me the following two rows.
M1 E1 Dept1 12/12/2001 1/1/2005
M4 E4 Dept1 12/12/2004 1/1/2006

but my expected result is only following rows

M2 E2 Dept2 12/12/2003 1/1/2005
M2 E2 Dept2 12/12/2006 1/1/2010

B'coz above two rows are already less then current date.
M4 and E4 has got two rows..One of the row dt_end is less than current date but the other one is Max date(DB2). So i dont want to see that in my result.
How can i acheive this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-26 : 15:05:01

[code]
SELECT *
FROM Table t
WHERE NOT EXISTS(SELECT 1 FROM Table
WHERE Manager = t.Manager
AND Employee = t.Employee
AND Dept = t.Dept
AND Dt_End ='9999-12-31'
)
AND t.Dt_End < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
[/code]

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

Sarakumar
Posting Yak Master

108 Posts

Posted - 2012-02-27 : 07:22:02

No. Still it is giving me the same result as explained in the previous thread. In my scenario, there are possibility, that employ had relationship with company and manager many times. In case, if the employee got terminated 4 times, but now he is active in the companay, i dont want to pick up that record.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-27 : 20:40:09

will employee be related to same manager and department in all those cases?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources