Finding the right record

22 Dec 2005 12:19 AM

John Baima

My brain is not working right now and I need some help.

I have two tables
Employee
    EmpID varchar(10) (primary key, unique values)
    EmpSSN varchar(9)

And
TimeSheet
    EmpID varchar(10) (foreign key to employee EmpID)
    TimeIn datetime

The problem is that we have multiple Employees with the same EmpSSN.
What I would like to do is find the EmpID's of these duplicates, and
change the SSNs of all those dups to '000000000' except the one EmpID
with the most recent TimeIn.

Not every EmpID has a TimeSheet record.

So, if I have
EmpID = 1234 with SSN 123456789
And
EmpID = 1235 with SSN 123456789

And time sheet records of
EmpID = 1234 with TimeIn of '11/1/2005'
And
EmpID = 1235 with TimeIn of '10/1/2005'

I'd like to change the EmpSSN of the Employee record, EmpID = 1235 to
'000000000'

Thanks for any help.

-John

22 Dec 2005 12:53 AM

Stijn Verrept

John Baima wrote:

> The problem is that we have multiple Employees with the same EmpSSN.
> What I would like to do is find the EmpID's of these duplicates, and
> change the SSNs of all those dups to '000000000' except the one EmpID
> with the most recent TimeIn.
>
> Not every EmpID has a TimeSheet record.

I think this will do:

update Employee set EmpSSN = '000000000' from Employee EM where EM.EmpID
<> IsNull((select max(TimeIn) from Timesheet where Timesheet.EMID =
EM.EmpID), EM.EmpID)

The IsNull is to make sure the ones without timesheet records aren't
deleted.

--
HTH,

Stijn Verrept.

22 Dec 2005 1:19 AM

John Baima

Show quote

"Stijn Verrept" <TURN_moc.tfosyrtne@njits_AROUND> wrote:

>John Baima wrote:
>
>> The problem is that we have multiple Employees with the same EmpSSN.
>> What I would like to do is find the EmpID's of these duplicates, and
>> change the SSNs of all those dups to '000000000' except the one EmpID
>> with the most recent TimeIn.
>>
>> Not every EmpID has a TimeSheet record.
>
>I think this will do:
>
>update Employee set EmpSSN = '000000000' from Employee EM where EM.EmpID
><> IsNull((select max(TimeIn) from Timesheet where Timesheet.EMID =
>EM.EmpID), EM.EmpID)
>
>The IsNull is to make sure the ones without timesheet records aren't
>deleted.

Thanks! For some reason (besides the insignificant typo), when I run
this I get:

The conversion of char data type to smalldatetime data type resulted
in an out-of-range smalldatetime value.

-John

22 Dec 2005 1:24 AM

Stijn Verrept

John Baima wrote:

> Thanks! For some reason (besides the insignificant typo), when I run
> this I get:
>
> The conversion of char data type to smalldatetime data type resulted
> in an out-of-range smalldatetime value.

Damn made a mistake, this won't work in a million year :) I'll rewrite
it.

--
HTH,

Stijn Verrept.

22 Dec 2005 1:28 AM

Stijn Verrept

This will be better I think. Please supply DDL next time so we can
immediatly test if it works.

update Employee set EmpSSN = '000000000' from Employee EM
where EM.EmpID <> IsNull((select Top 1 Timesheet.EmpID from Timesheet
where Timesheet.EMID = EM.EmpID order by TimeIn desc), EM.EmpID)

--
HTH,

Stijn Verrept.

22 Dec 2005 12:52 PM

John Baima

"Stijn Verrept" <TURN_moc.tfosyrtne@njits_AROUND> wrote:

>This will be better I think. Please supply DDL next time so we can
>immediatly test if it works.
>
>update Employee set EmpSSN = '000000000' from Employee EM
>where EM.EmpID <> IsNull((select Top 1 Timesheet.EmpID from Timesheet
>where Timesheet.EMID = EM.EmpID order by TimeIn desc), EM.EmpID)

Thanks, but that does not work. When I run it with a select instead of
the update, I do not get any rows. Here is the cut down DDL:

CREATE TABLE [Employee] (
    [EmpID] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
    [EmpSSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
    (
        [EmpID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [TimeSheet] (
    [EmpID] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
    [TimeIn] [smalldatetime] NOT NULL CONSTRAINT
[DF_TimeSheet_TimeIn] DEFAULT (''),
) ON [PRIMARY]
GO

-John

22 Dec 2005 8:30 PM

Stijn Verrept

John Baima wrote:

> Thanks, but that does not work. When I run it with a select instead of
> the update, I do not get any rows.

update Employee set EmpSSN = '000000000' from Employee EM
where EM.EmpID <> IsNull((select Top 1 Timesheet.EmpID from Timesheet
inner join Employee EM2 on EM2.EmpID = Timesheet.EmpID
where EM2.EmpSSN = EM.EmpSSN order by TimeIn desc), EM.EmpID)

Ok this should be better. If you post please also post some testing
data: www.aspfaq.com/5006

I hope this one works cause I'm off for a 4 week holiday to Vietnam
tomorrow ;)

--
HTH,

Stijn Verrept.

22 Dec 2005 5:50 AM

Uri Dimant

John
CREATE TABLE #Empl
(
idNo INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
SSN VARCHAR(1000),
EmplId INT,
Hire_Date DATETIME
)

INSERT INTO #Empl (SSN,EmplId,Hire_Date) VALUES ('5555555',20,'20050101')
INSERT INTO #Empl (SSN,EmplId,Hire_Date)VALUES ('5555555',10,'20050102')
INSERT INTO #Empl (SSN,EmplId,Hire_Date)VALUES ('YYYMM',10,'20050106')
INSERT INTO #Empl (SSN,EmplId,Hire_Date) VALUES ('5555555',20,'20050104')

UPDATE #Empl SET SSN='0000000'
WHERE idNo IN ( SELECT B.idNo
FROM #Empl A JOIN #Empl B
ON A.idNo <> B.idNo
AND A.SSN = B.SSN
AND A.EmplId = B.EmplId)

SELECT * FROM #Empl

Show quote

"John Baima" <john@nospam.com> wrote in message
news:42sjq1t7ft508bfpidssl4uvcpte43v716@4ax.com...
> My brain is not working right now and I need some help.
>
> I have two tables
> Employee
> EmpID varchar(10) (primary key, unique values)
> EmpSSN varchar(9)
>
> And
> TimeSheet
> EmpID varchar(10) (foreign key to employee EmpID)
> TimeIn datetime
>
> The problem is that we have multiple Employees with the same EmpSSN.
> What I would like to do is find the EmpID's of these duplicates, and
> change the SSNs of all those dups to '000000000' except the one EmpID
> with the most recent TimeIn.
>
> Not every EmpID has a TimeSheet record.
>
> So, if I have
> EmpID = 1234 with SSN 123456789
> And
> EmpID = 1235 with SSN 123456789
>
> And time sheet records of
> EmpID = 1234 with TimeIn of '11/1/2005'
> And
> EmpID = 1235 with TimeIn of '10/1/2005'
>
> I'd like to change the EmpSSN of the Employee record, EmpID = 1235 to
> '000000000'
>
> Thanks for any help.
>
> -John
>

22 Dec 2005 5:55 AM

Uri Dimant

Correction
UPDATE #Empl SET SSN='0000000'
WHERE idNo IN ( SELECT B.idNo
FROM #Empl A JOIN #Empl B
ON A.idNo < B.idNo
AND A.SSN = B.SSN
AND A.EmplId = B.EmplId)

Show quote

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:OjqqZurBGHA.3936@TK2MSFTNGP12.phx.gbl...
> John
> CREATE TABLE #Empl
> (
> idNo INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
> SSN VARCHAR(1000),
> EmplId INT,
> Hire_Date DATETIME
> )
>
> INSERT INTO #Empl (SSN,EmplId,Hire_Date) VALUES ('5555555',20,'20050101')
> INSERT INTO #Empl (SSN,EmplId,Hire_Date)VALUES ('5555555',10,'20050102')
> INSERT INTO #Empl (SSN,EmplId,Hire_Date)VALUES ('YYYMM',10,'20050106')
> INSERT INTO #Empl (SSN,EmplId,Hire_Date) VALUES ('5555555',20,'20050104')
>
> UPDATE #Empl SET SSN='0000000'
> WHERE idNo IN ( SELECT B.idNo
> FROM #Empl A JOIN #Empl B
> ON A.idNo <> B.idNo
> AND A.SSN = B.SSN
> AND A.EmplId = B.EmplId)
>
>
> SELECT * FROM #Empl
>
>
> "John Baima" <john@nospam.com> wrote in message
> news:42sjq1t7ft508bfpidssl4uvcpte43v716@4ax.com...
>> My brain is not working right now and I need some help.
>>
>> I have two tables
>> Employee
>> EmpID varchar(10) (primary key, unique values)
>> EmpSSN varchar(9)
>>
>> And
>> TimeSheet
>> EmpID varchar(10) (foreign key to employee EmpID)
>> TimeIn datetime
>>
>> The problem is that we have multiple Employees with the same EmpSSN.
>> What I would like to do is find the EmpID's of these duplicates, and
>> change the SSNs of all those dups to '000000000' except the one EmpID
>> with the most recent TimeIn.
>>
>> Not every EmpID has a TimeSheet record.
>>
>> So, if I have
>> EmpID = 1234 with SSN 123456789
>> And
>> EmpID = 1235 with SSN 123456789
>>
>> And time sheet records of
>> EmpID = 1234 with TimeIn of '11/1/2005'
>> And
>> EmpID = 1235 with TimeIn of '10/1/2005'
>>
>> I'd like to change the EmpSSN of the Employee record, EmpID = 1235 to
>> '000000000'
>>
>> Thanks for any help.
>>
>> -John
>>
>
>

22 Dec 2005 9:50 AM

Stijn Verrept

Uri Dimant wrote:

> Correction
> UPDATE #Empl SET SSN='0000000'
> WHERE idNo IN ( SELECT B.idNo
> FROM #Empl A JOIN #Empl B
> ON A.idNo < B.idNo
> AND A.SSN = B.SSN
> AND A.EmplId = B.EmplId)

Uri,

He has 2 tables, Employee and Timesheet and needs to select it on the
most recent TimeIn column where the SSN columns are identical. The
EmpID don't need to be identical.

--
Kind regards,

Stijn Verrept.

22 Dec 2005 9:57 AM

Uri Dimant

Hi
I did not follow his tables structure. I did mean to give him an idea how it
could be done , that's all

Show quote

"Stijn Verrept" <TURN_moc.tfosyrtne@njits_AROUND> wrote in message
news:idCdnden8vR06zfeRVny1g@scarlet.biz...
> Uri Dimant wrote:
>
>> Correction
>> UPDATE #Empl SET SSN='0000000'
>> WHERE idNo IN ( SELECT B.idNo
>> FROM #Empl A JOIN #Empl B
>> ON A.idNo < B.idNo
>> AND A.SSN = B.SSN
>> AND A.EmplId = B.EmplId)
>
> Uri,
>
> He has 2 tables, Employee and Timesheet and needs to select it on the
> most recent TimeIn column where the SSN columns are identical. The
> EmpID don't need to be identical.
>
> --
> Kind regards,
>
> Stijn Verrept.