March 24, 2016 at 9:14 am
I need to pick earliest Security Date for each Security Id ..see attached sample text..
for security id 10000 securutiy desc cahnged from abc to xyz and it should pick date 1/1/12
for security id 2000 security code changed from a to c on 1/1/2015 ..we should pick that date
for security id 3000 security desc is same..abc 10000...(even though screen shot says ABC 10001,ABC 1002 AND ABC 1003..its not the case) and Security code changed from a to b and b to c ..and recent change is 1/1/2015..this is the date we need to pick
for securty 5000 same thing it changed security code from c to d on 1/1/15..so we need to pick this date
bottom line is we need to pick earliestSecurity Date when Security desc changed or Security code changed..
any suggestions how to do this one?...I tried to use row num ..and tried to compare current and row and next row ..but still seeing some descrepancies..and it some case i am getting duplicate rows while i am using analytical function..
March 24, 2016 at 11:29 am
ssisguy (3/24/2016)
I need to pick earliest Security Date for each Security Id ..see attached sample text..for security id 10000 securutiy desc cahnged from abc to xyz and it should pick date 1/1/12
for security id 2000 security code changed from a to c on 1/1/2015 ..we should pick that date
for security id 3000 security desc is same..abc 10000...(even though screen shot says ABC 10001,ABC 1002 AND ABC 1003..its not the case) and Security code changed from a to b and b to c ..and recent change is 1/1/2015..this is the date we need to pick
for securty 5000 same thing it changed security code from c to d on 1/1/15..so we need to pick this date
bottom line is we need to pick earliestSecurity Date when Security desc changed or Security code changed..
any suggestions how to do this one?...I tried to use row num ..and tried to compare current and row and next row ..but still seeing some descrepancies..and it some case i am getting duplicate rows while i am using analytical function..
I really dont follow this comment.....can you please explain
for security id 3000 security desc is same..abc 10000...(even though screen shot says ABC 10001,ABC 1002 AND ABC 1003..its not the case) and Security code changed from a to b and b to c ..and recent change is 1/1/2015..this is the date we need to pick
......I would have thought that the first change for id 30000 was 12/31/06??
suggest you read the following article on how to post questions with relevant data that is easy for responders to provide accurate answers
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 24, 2016 at 1:03 pm
I have to agree with J Livingston - the logic doesn't match with the description of the goal.
To get you started, you said that you want to select the earliest date for each SecurityID. Here's how to do that.
SELECT SecurityID, MIN(SecurityDate)
FROM dbo.YourTable
GROUP BY SecurityID
ORDER BY SecurityID;
Now you have a list of the earliest date for each SecurityID, but it doesn't match your examples. Where do we go from here?
March 30, 2016 at 9:05 am
thanks for all the replies...I got this one sorted out..by using analytical functions ROW_NUM and DENSE_RANK
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply