Select query based on latest date old date

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 2008 Forums

Transact-SQL (2008)

Select query based on latest date old date

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 11:14:31

Is it possible via select query: I have the followng sample data, based on acct_unit, i want to put either Initial Row' or Updated Row' in Status_Flag field.

If Based on acct_Unit, if the date is latest then want to put 'Updated Row', if old then want to put 'Initial Row'
If there is only one row based on Acct_unit, then want to put 'Initial Row'

Declare @Sample table (acct_unit varchar(10), STATUS_DATE datetime, STATUS_FLAG varchar(20), SUR_KEY Int)
insert @Sample
select '168','20-OCT-2012', '', 19 union all
select '168','16-Oct-2012', '', 12 union all
select '173','24-Oct-2012', '', 26 union all
select '173','20-Oct-2012', '', 22 union all
select '182','17-Oct-2012', '', 14

Result should be
Acct_unit=168 - status_Flag = 'Updated Row' - surkey=19
Acct_unit=168 - status_Flag = 'Initial Row' - surkey=12
Acct_unit=173 - status_Flag = 'Updated Row' - surkey=26
Acct_unit=173 - status_Flag = 'Initial Row' - surkey=22
Acct_unit=182 - status_Flag = 'Initial Row' - surkey=14

Thanks a lot for the helpful information.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-30 : 11:20:27

Update it like shown below. Can there be more than one row for a STATUS_DATE+acct_unit combination? If there is, depending on what you want, you may need to change the "ROW_NUMBER" to "DENSE_RANK"

;WITH cte AS
(
	SELECT STATUS_FLAG,ROW_NUMBER() OVER(PARTITION BY acct_unit ORDER BY STATUS_DATE) AS RN
	FROM @Sample
)
UPDATE cte SET 
	STATUS_FLAG = CASE WHEN RN=1 THEN 'Initial Row' ELSE 'Updated Row' END;

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 11:23:34

Thank you Sunita, There could be more than one row with teh combination.

Can i use dense_Rank with oracle database?

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 11:28:20

quote:
Originally posted by cplusplus

Thank you Sunita, There could be more than one row with teh combination.

Can i use dense_Rank with oracle database?

Thanks again.

You may be better off asking this in a Oracle forum like www.orafaq.com then
this is MS SQL Server forum and there are very few Oracle experts here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-30 : 11:29:08

I am totally illiterate when it comes to Oracle - so whatever I am going to say will more likely than not be wrong. But it seems like it has DENSE_RANK based on this: http://www.oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions.php

If you need help with Oracle questions, dbforums.com may get you more accurate and faster responses.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 11:31:44

Hello Sunitha,

Is it not possible to write within select, without using update query..
I am using ETL tool, it accepts queries to be placed, but just one select query only.

select acct_unit,status_date,status_flag,sur_key from @sample

Thank you very much.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-10-30 : 11:37:50

Visakh, Sorry. I have this in both SQL server and oracle...

two diff sites.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 11:43:09

try this and see if it works (it works in t-sql anyways!)


select s.acct_unit,s.status_date,case when s1.acct_unit is not null then 'Updated Row' else 'Initial Row' end as status_flag,s.sur_key 
from @sample s
left join (select acct_unit, MIN(status_date) AS MinDate
           from @sample
           group by acct_unit
          )s1
on s1. acct_unit = s.acct_unit
and s1.MinDate = s.status_date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources