Update Date based on Employee - 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 2005 Forums

Transact-SQL (2005)

Update Date based on Employee

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2014-10-23 : 01:38:25

Hi All,
I have one table with three columns. I have to update the last transation date based on employee.

Actually Last transaction date will be the previous entry date of the particular employee

Please suggest

Employee_No Trans_Date Last_Trans_Date
E1 10/23/2014 1:32
E1 10/23/2014 1:35 10/23/2014 1:32
E1 10/23/2014 1:37 10/23/2014 1:35
E1 10/23/2014 1:48 10/23/2014 1:37
E1 10/23/2014 1:52 10/23/2014 1:48
E1 10/23/2014 1:55 10/23/2014 1:52
E1 10/23/2014 2:30 10/23/2014 1:55
E2 10/23/2014 2:32
E2 10/23/2014 2:37 10/23/2014 2:32
E2 10/23/2014 2:38 10/23/2014 2:38
E2 10/23/2014 2:40 10/23/2014 2:40

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-23 : 16:37:10

Given your input data, what would the output data look like after the update? This will help determine the query to use.

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-10-23 : 17:20:03

create Table Employee
(
Employee_No varchar (10),
Trans_Date datetime,
Last_Trans_Date datetime
)
Insert into Employee
values
('E1', '10/23/2014 1:32', ''),
('E1', '10/23/2014 1:35', '10/23/2014 1:32'),
('E1', '10/23/2014 1:37', '10/23/2014 1:35'),
('E1', '10/23/2014 1:48', '10/23/2014 1:37'),
('E1', '10/23/2014 1:52', '10/23/2014 1:48'),
('E1', '10/23/2014 1:55', '10/23/2014 1:52'),
('E1', '10/23/2014 2:30', '10/23/2014 1:55'),
('E2', '10/23/2014 2:32', ''),
('E2', '10/23/2014 2:37', '10/23/2014 2:32'),
('E2', '10/23/2014 2:38', '10/23/2014 2:38'),
('E2', '10/23/2014 2:40', '10/23/2014 2:40')

With EmployeeData(Employee_No,Trans_Date, Last_Trans_Date )
as
(
select
Employee_No ,
Rank () Over (Partition by Employee_No order by Last_Trans_Date desc) as Trans_Date,
Last_Trans_Date
from
Employee
)
select
Employee_No,
dateadd (day, -1, Trans_Date) as PreviousTransDate,
Last_Trans_Date,
Trans_Date
from EmployeeData
where
Trans_Date = 1

Does this help, didn't understand your problem ?

Marcus

I learn something new everyday.

sanjay5219
Posting Yak Master

240 Posts

Posted - 2014-10-24 : 05:13:43

@gbritton

We have to update the third column based on employee last transaction date

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-10-24 : 05:24:21

[code]UPDATE t
SET Last_Trans_Date = l.Trans_Date
FROM yourtable t
CROSS APPLY
(
SELECT MAX(Trans_Date) as Trans_Date
FROM yourtable x
WHERE x.Employee_No = t.Employee_No
AND x.Trans_Date < t.Trans_Date
) l[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources