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 |
|
kaos_king
Starting Member
13 Posts |
 Posted - 2014-02-07 : 07:20:55
|
Hi guys, the title doesn't really sum up what I have been trying to do but I'll try and explain it a little better.
We have a user audit table that contains userauditref (unique identifier for the table), userref, username, obsolete, editdate (and others of no interest to me :) ).
So, there are multiple entries per each user and for each entry the obsolete field is a boolean flag for what it was at the time of the user being updated.
I am trying to pull a report that will look at the records where the obsolete flag has gone from 1 to 0 (i.e. unobsoleted) however as this could have happened many times, I want the most recent.
I attempted to use derived tables but still seem to get duplicates. I also tried using nested selects in my joins but I'm not sure what the best way to do it.
My logic was to select all distinct users where the obsolete flag is 1 and the edit date is the most recent, do the same in another table for where the flag is 0 and then do a comparison on the two tables to see what date is greater to obtain my answer.
Am I missing a better way of doing this or can you help prompt me as to how to get this to work? TIA |
|
|
marcusn25
Yak Posting Veteran
56 Posts |
Posted - 2014-02-13 : 17:34:48
|
No sample data but i hope this is what you are looking for.
CREATE TABLE [dbo].[Audit]
(
[userauditref] [int],
[userref] [varchar](20),
[username] [varchar](20),
[obsolete] [varchar](20),
[editdate] [datetime]
)
Insert into Audit
(
userauditref,
userref,
username,
obsolete,
editdate
)
values
(101,'A101', 'J.Smith', 1 , '2014-01-12 00:10:00.000'),
(102,'A101', 'J.Smith', 0 , '2014-01-11 00:00:00.000'),
(103,'A101', 'J.Smith', 1 , '2014-01-14 00:09:00.000'),
(104,'A101', 'J.Smith', 1 , '2014-01-14 12:10:00.000'),
(105,'A101', 'J.Smith', 1 , '2014-01-14 12:11:00.000')
Select
Audit.*
from
(
select
A.*,
Rank ()over (partition by a.userref order by a.editdate desc) as LatestEditDate
from Audit A
where
A.obsolete = 1
) as Audit
where
Audit.LatestEditDate =1
Marcus
I learn something new everyday. |
 |
|
|
kaos_king
Starting Member
13 Posts |
Posted - 2014-02-17 : 05:06:22
|
Wow, thank you Marcus. I didn't even know about the RANK function!
Please could you offer a brief explanation of it? If I can make it out correctly, it seems like it only pulls back the ones where obsolete is is 1 but I'm not sure how it would distinguish which entries have been 1 and gone to 0? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-17 : 06:44:17
|
[code]
;With CTE
AS
(
select
A.*,
ROW_NUMBER() over (partition by a.userref order by editdate) as Seq
from Audit A
)
SELECT *
FROM
(
SELECT c1.userref,c1.editdate AS MovedOutDate,c2.Obsolete AS MovedInDate,..any other columns,
ROW_NUMBER() OVER (PARTITION BY c1.userref ORDER BY c1.edidate DESC) AS Rn
FROM CTE c1
INNER JOIN CTE c2
ON c1.userref = c2.userref
AND c1.Seq = c2.Seq-1
AND c1.Obsolete = 1
AND c2.Obsolete = 0
)r
WHERE Rn = 1
[/code]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
kaos_king
Starting Member
13 Posts |
Posted - 2014-03-03 : 04:06:21
|
Thank you visakh16. A little modifying and it works perfectly.
Best of all is that I understand it! :D |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-03-03 : 04:27:34
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
|
|
|
|
|
Struggling comparing rows