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 |
bhussey
Starting Member
5 Posts |
Posted - 2013-01-17 : 18:03:38
|
Hello, I am trying to create a query that will query any action changes made within the last 30 days. Let's say someone changes their name, I'd like to pull their current name/record and the previous name/record. This is the query used to pull the current recordselect employee, fld_nbr, date_stampfrom hrhistoryWhere fld_nbr = '2'and date_stamp > (sysdate-30) |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-01-17 : 18:36:11
|
You didn't way which version of SQL Sever you are using, but one way is with the ROW_NUMBER() function:select *from( select employee, fld_nbr, date_stamp, ROW_NUMBER() OVER (PARTITION BY employee ORDER BY date_stamp DESC) AS RowNum from hrhistory Where fld_nbr = '2' and date_stamp > (sysdate-30)) as twhere RowNum IN (1, 2) |
|
|
bhussey
Starting Member
5 Posts |
Posted - 2013-01-17 : 18:42:35
|
I'm using SQL Developer |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-17 : 20:04:21
|
quote: Originally posted by bhussey Hello, I am trying to create a query that will query any action changes made within the last 30 days. Let's say someone changes their name, I'd like to pull their current name/record and the previous name/record. This is the query used to pull the current recordselect employee, fld_nbr, date_stampfrom hrhistoryWhere fld_nbr = '2'and date_stamp > (sysdate-30)
Do you want them in 2 separate column? How does output look like |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-17 : 22:38:33
|
quote: Originally posted by bhussey Hello, I am trying to create a query that will query any action changes made within the last 30 days. Let's say someone changes their name, I'd like to pull their current name/record and the previous name/record. This is the query used to pull the current recordselect employee, fld_nbr, date_stampfrom hrhistoryWhere fld_nbr = '2'and date_stamp > (sysdate-30)
do you've audit tables to track the changes? or do you add it as a new item in your main table (type 2)?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|