Previously entered value with max 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

General SQL Server Forums

New to SQL Server Programming

Previously entered value with max date

Author

Topic

plsqldev
Starting Member

11 Posts

Posted - 2011-07-15 : 16:50:27

I have below datset..

123 C1 1/1/2010
123 A1 1/1/2010
123 A3 1/1/2011
123 NULL 10/1/2010
123 NULL 11/1/2010
124 NULL 10/1/2010
124 NULL 1/1/2010
125 NULL 1/1/2011
125 A1 1/1/2010
125 NULL 11/1/2010

I need to have previous ID's Type, If maximum Date is NULL. Else NULL

I need

for 123 - 123 A3 11/1/2011
for 124 --- 124 NULL 10/1/2010
for 125 -- 125 A1 11/1/2010 as output

Can any one suggest me the solution.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-15 : 20:08:26

This is not too hard to do, except for one thing: By definition, data in a table is not an ordered collection. So you need to be able to define some way to order the rows in the table in order for us to be able to say what the "previous" row is.

This ordering can be based on a column, for example, you might have an ID column that is an ordering ID. Or you may order by a date. In your example, it didn't seem like the ordering is by date. Can you describe how the data should be ordered?

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-16 : 00:43:16

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

>>I need to have previous ID's something_type, If maximum something_date is NULL. Else NULL <<

Since sets (tables) have no ordering, what does "previous" mean? Try7 again and be polite.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

plsqldev
Starting Member

11 Posts

Posted - 2011-07-16 : 10:44:32

Thank you for your response... the data should order by date.... can you please give me the sql.... I am not good at sql... I tried with a sub query looking for not null... it worked out for the 2 scenarios (has code) and in case of all null codes the id is not piking..

thank you in advance...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-16 : 11:29:38

This is one way to do it. Done only minimal testing, so if it does not seem like it is not giving the right answers, please post sample data that shows the problem (in a way in which someone can copy it and run, like I have in my sample data).

-- SAMPLE DATA
if object_id('tempdb..#tmp') is not null drop table #tmp;
create table #tmp (id int, typeid varchar(2), datestamp datetime);
insert into #tmp values ('123','C1','1/1/2010');
insert into #tmp values ('123','A1','1/1/2010');
insert into #tmp values ('123','A3','1/1/2011');
insert into #tmp values ('123',NULL,'10/1/2010');
insert into #tmp values ('123',NULL,'11/1/2010');
insert into #tmp values ('124',NULL,'10/1/2010');
insert into #tmp values ('124',NULL,'1/1/2010');
insert into #tmp values ('125',NULL,'1/1/2011');
insert into #tmp values ('125','A1','1/1/2010');
insert into #tmp values ('125',NULL,'11/1/2010');

-- QUERY
with A as
(
select id,max(datestamp) Dt,max(case when typeid is not null then datestamp end) tDt
from #tmp group by id
)
select
	a.id,
	t.typeid,
	a.Dt
from
	A inner join #tmp t on t.id = a.id
	and coalesce(tDt,dt) = t.datestamp;

-- CLEANUP
drop table #tmp;

plsqldev
Starting Member

11 Posts

Posted - 2011-07-16 : 12:02:52

Thank you for your help... I really appreciate.

Thanks alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources