SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Struggling comparing rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kaos_king
Starting Member

9 Posts

Posted - 02/07/2014 :  07:20:55  Show Profile  Reply with Quote
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

Germany
56 Posts

Posted - 02/13/2014 :  17:34:48  Show Profile  Reply with Quote
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.
Go to Top of Page

kaos_king
Starting Member

9 Posts

Posted - 02/17/2014 :  05:06:22  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/17/2014 :  06:44:17  Show Profile  Reply with Quote

;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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

kaos_king
Starting Member

9 Posts

Posted - 03/03/2014 :  04:06:21  Show Profile  Reply with Quote
Thank you visakh16. A little modifying and it works perfectly.

Best of all is that I understand it! :D
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/03/2014 :  04:27:34  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000