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.
| 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]
|
|
|
|
|
|
|
|
|
Update Date based on Employee