Help on SQL Query

Hi,

I have a table called employee. I have fields like Emp.Nbr and login.date. I want to retrieve unique data of employee numbers. But I want unique with max(login.date) of an employee.

Ex: 111 7/19/2007

222 6/13/2007

111 7/25/2007

333 5/21/2007

222 7/12/2007

I want out put like

111 7/25/2007

222 7/12/2007

333 5/21/2007

How to write query for the above out put.

Kindly help.

Thanks

SELECT EMP.NBR,MAX(login.date)

FROM EMP INNER JOIN Login

on EMP.ID = Login.EmpId

Group by EMP.NBR

|||

select Nbr,max(LoginDate)

from emp

group by nbr

|||

Here it is,

Code Snippet

Create Table #emp (

[EmpId] Varchar(100) ,

[Date] DateTime

);

Insert Into #emp Values('111','7/19/2007');

Insert Into #emp Values('222','6/13/2007');

Insert Into #emp Values('111','7/25/2007');

Insert Into #emp Values('333','5/21/2007');

Insert Into #emp Values('222','7/12/2007');

Select EmpId, Max(Date) As date From #emp Group By EmpID

|||

hope this will suites your requirement: Here I have used Table Variable instead of Temporary Tables.

DECLARE @.Table TABLE ( Nbr int , LoginDate smalldatetime )

INSERT INTO @.Table (Nbr, LoginDate) VALUES(111, '7/19/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(222, '6/13/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(111, '7/25/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(333, '5/21/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(222, '7/12/2007')

SELECT

Nbr, max(LoginDate)

FROM @.Table

GROUP BY Nbr

Regards,

Prashanthi.

Help needed in SQL Query Search!

Hi everyone,

I'm trying to implement SQL Server database search. The details are:-
1. I have table called EMPLOYEE has FNAME,LNAME etc cols.
2. User might look for any employee using either FNAME or LNAME
3. I have search box in asp.net where user could enter search string
The sample data:
FNAME LNAME
abc george
def george
rkis lita
rose lita

The query i wrote:
SELECT * FROM EMPLOYEE WHERE lname like '%' + searchArg + '%'
My problem is:-
1. let's say user is looking for employee "george"; In search string instead of typing actual word "george", user could type "jeorge"; because the name pronounce or sounds like similar.
Same thing with user could type "leta" instead of "lita". Again these are all similar sounds.

When you look for "jeorge" in GOOGLE; it says "did you mean george"; i would like implement something like that. somewhere i saw SOUNDEX would do what i am looking for; but i no luck for me.

Is this possible anyway in T-SQL or Fulltext search.

Your help is greatly appreciated.

Thanks
Bob

Bob,
You were on the right track with Soundex, here's how you can use it. Also look into difference.
Sample data: (pubs)

au_id au_lname au_fname

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

409-56-7008 Bennet Abraham

648-92-1872 Blotchet-Halls Reginald

238-95-7766 Carson Cheryl

722-51-5454 DeFrance Michel

712-45-1867 del Castillo Innes

427-17-2319 Dull Ann

213-46-8915 Green Marjorie

527-72-3246 Greene Morningstar

472-27-2349 Gringlesby Burt

My Queries:

select*from authorswheresoundex(au_lname)=soundex('Benet')

Returns:
au_id au_lname au_fname

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

409-56-7008 Bennet Abraham

select au_id,au_lname,au_fnamefrom authorswheredifference(au_lname,'whit')> 3

Returns:
au_id au_lname au_fname

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

172-32-1176 White Johnson

Hope this helps, as you can see here it is definitely possible to get the "similar" functionality. Check books online for more info on difference, iirc it returns one of a number of different values that state how close or different the words are.
Scott

Help me to write this query

Hi Everybody,
I have a table call Employee like this
EmpId DOB Sex
1 01/02/1982 Male
2 01/01/1983 Female

And also i have got Contacts Table looks like this
ConId EmpId ConDate Description
1 1 02/02/2007 Not Specified
2 1 02/03/2007 Not Specified
3 2 01/01/2007 Personal

Now i need to display like this

Description Male Female Age 0-10 Age 10+
Not Specified 1 0 0 1
Personal 0 1 0 1

When calculation the age If one employee has got more than one contacts then we need to get the maximum ConDate from the Contacts table and then get the DOB from the Employee table and and get the DateDiff by year

How can can i do this query ?
Any Idea ?

regards
suis

Here You go..

Code Snippet

Create Table #employee (

[EmpId] int ,

[DOB] datetime ,

[Sex] Varchar(100)

);

Insert Into #employee Values('1','01/02/1982','Male');

Insert Into #employee Values('2','01/01/1983','Female');

--Completed :: employee

--Sample Table :: employeecontact

Create Table #employeecontact (

[ConId] int ,

[EmpId] int ,

[ConDate] datetime ,

[Description] Varchar(100)

);

Insert Into #employeecontact Values('1','1','02/02/2007','NotSpecified');

Insert Into #employeecontact Values('2','1','02/03/2007','NotSpecified');

Insert Into #employeecontact Values('3','2','01/01/2007','Personal');

--Using SQL Server 2000

Select

Description,

Case When Sex='Male' Then 1 Else 0 End Male,

Case When Sex='Female' Then 1 Else 0 End Female,

Case When Datediff(YY,DOB,getdate()) <=10 Then 1 Else 0 End [Age 0-10],

Case When Datediff(YY,DOB,getdate()) >10 Then 1 Else 0 End [Age 10+]

from

#employee E

Join (Select

EC.EmpID,EC.Description

From

#employeecontact EC

Join (

Select

[EmpId]

,Max([ConDate]) ConDate

From

#employeecontact

Group By [EmpId]

) as Data On Data.ConDate =EC.ConDate and Data.EmpID = EC.EMPID) C On E.EmpID=C.EmpID

--Using SQL Server 2005

;With CTE as

(

Select EmpId,Description, ROW_NUMBER() Over (Partition By EmpId Order By [ConDate] Desc) RowId From #employeecontact

)

Select

Description,

Case When Sex='Male' Then 1 Else 0 End Male,

Case When Sex='Female' Then 1 Else 0 End Female,

Case When Datediff(YY,DOB,getdate()) <=10 Then 1 Else 0 End [Age 0-10],

Case When Datediff(YY,DOB,getdate()) >10 Then 1 Else 0 End [Age 10+]

from

#employee E

Join CTE C On E.EmpId = C.EmpId

Where

C.RowID=1

|||

Code Snippet

create table #emp

( EmpId int, DOB datetime, Sex varchar(10))

insert into #emp values (1, '01/02/1982', 'Male')

insert into #emp values (2, '01/01/1983', 'Female')

insert into #emp values (3, '10/01/1999', 'Female')

create table #contacts

( ConId int, EmpId int, ConDate datetime, Description varchar(100))

insert into #contacts values (1, 1, '02/02/2007', 'Not Specified')

insert into #contacts values (2, 1, '02/03/2007', 'Not Specified')

insert into #contacts values (3, 2, '01/01/2007', 'Personal')

insert into #contacts values (3, 3, '5/01/2007', 'Personal')

;with contact1 as

(

select c.EmpId, c.Description, max(c.ConDate) as ConDate

from #contacts c

group by c.EmpId, c.Description

),

contact2 as

(

select c.EmpId, c.Description, c.ConDate, e.Sex,

datediff(yy, e.dob, c.ConDate)

+ case when (month(e.dob)*100)+day(e.dob)>(month(c.ConDate)*100)+day(c.ConDate)

then -1 else 0

end as Age

from contact1 c

inner join #emp e

on c.EmpId = e.EmpId

)

select Description,

sum(case Sex when 'Male' then 1 else 0 end) as Male,

sum(case Sex when 'Female' then 1 else 0 end) as Female,

sum(case when Age <=10 then 1 else 0 end) as 'Age 0-10',

sum(case when Age > 10 then 1 else 0 end) as 'Age 10+'

from contact2 c

group by Description

|||Hi Manivannan.D.Sekaran
Many Many thanks for your quick response.this is great help for me,
i will have a look this query and let u know ASAP the result,cos this query is bit tau ff for me,

again thank you so much for your valuable response.

this is great forum.....i love this forum........

regards
suis
|||hi Dale and Sekaran
Its very useful u r comments to sort out my problem
now my query is working perfectly,i had to do some modification for that,becase my database structure is not like as i post ,becase its just sample database format,

anyway thanks for the quick response from you two .

many thanks to .Net forum......nice work

regards
suis