Struggling comparing rows - 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)

Struggling comparing rows

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources