SQL Help in getting the 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Help in getting the Max() Date....

Author

Topic

konquistador
Starting Member

4 Posts

Posted - 2009-01-09 : 17:22:27

Hi gurus, Here is my situation:

Here is the sample data:

Policy_Id Policy-Exp_Dt COl_1
123_____10/30/2008 __ 333
123_____ 09/25/2008___445
123_____ 08/30/2008___443

Here i have to get the Policy-Exp_Dt from the second row; In other words for all rows which have a similar Policy_Id i should first find out the max(Policy-Exp_Dt) and get the next smallest available date to that.

I have tried using

select Policy_Id, COl_1, Policy_Exp_Dt from
table_1
where Policy-Exp_Dt = (select max(Policy-Exp_Dt)-1 from
table_1 a
where a.policy_id = table_1.policy_id)

but here i am getting the value = 10/30/2008 - 1 which is 10/29/2008 but i need the value 9/25/2008 which is the next available date less than the max() date.

Please Advise

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-09 : 17:44:42

Select Policy_Id,[Policy-Exp_Dt],Col_1
from
(Select ROW_NUMBER() over (Partition by Policy_Id order by [Policy-Exp_Dt] desc)as Seq,
* from table)t
Where t.Seq = 2

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-09 : 22:58:13

declare @temp table (Policy_Id int, Policy_Exp_Dt datetime, COl_1 int)
insert into @temp
select 123,'10/30/2008',333 union all
select 123,'09/25/2008',445 union all
select 123,'08/30/2008',443

select Policy_Id,Policy_Exp_Dt,COl_1 from
(
select *, rank() over (partition by policy_id order by policy_exp_dt desc) as rn from @temp
)as p

If u want 9/25/2008, then put where condition in a query.
where rn = 2

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-10 : 01:32:41

declare @table table(Policy_Id int ,PolicyExp_Dt datetime,COl_1 int)
insert into @table
select 123,'10/30/2008' , 333 union all
select 123, '09/25/2008',445 union all
select 123, '08/30/2008',443 union all
select 133,'11/30/2008' , 333 union all
select 133, '11/25/2008',445 union all
select 133, '08/30/2008',443

select c.policy_id,c.maxdate,t.col_1 from @table t
inner join
(select a.policy_id,b.maxdate from
(select policy_id,max(PolicyExp_Dt) as date from @table group by policy_id) a
cross apply
(select max(PolicyExp_Dt)as maxdate from @table where PolicyExp_Dt < date and Policy_Id = a.Policy_Id group by Policy_Id) b )c
on t.Policy_Id = c.Policy_Id and t.PolicyExp_Dt = c.maxdate

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:04:19

quote:
Originally posted by Nageswar9

declare @temp table (Policy_Id int, Policy_Exp_Dt datetime, COl_1 int)
insert into @temp
select 123,'10/30/2008',333 union all
select 123,'09/25/2008',445 union all
select 123,'08/30/2008',443

select Policy_Id,Policy_Exp_Dt,COl_1 from
(
select *, rank() over (partition by policy_id order by policy_exp_dt desc) as rn from @temp
)as p

If u want 9/25/2008, then put where condition in a query.
where rn = 2

wont work if you've two records with same maxdate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:04:59

quote:
Originally posted by sodeep

Select Policy_Id,[Policy-Exp_Dt],Col_1
from
(Select ROW_NUMBER() over (Partition by Policy_Id order by [Policy-Exp_Dt] desc)as Seq,
* from table)t
Where t.Seq = 2

if you've more than two records with same max date this wont work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:05:33


select *, dense_rank() over (partition by policy_id order by policy_exp_dt desc) as rn from @temp 
)as p


If u want 9/25/2008, then put where condition in a query.
where rn = 2

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-10 : 11:44:55

quote:
Originally posted by visakh16

declare @temp table (Policy_Id int, Policy_Exp_Dt datetime, COl_1 int)
insert into @temp
select 123,'10/30/2008',333 union all
select 123,'09/25/2008',445 union all
select 123,'08/30/2008',443

select Policy_Id,Policy_Exp_Dt,COl_1 from
(

select *, dense_rank() over (partition by policy_id order by policy_exp_dt desc) as rn from @temp
)as p

If u want 9/25/2008, then put where condition in a query.
where rn = 2

Thanks for new knowledge buddy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 14:09:09

quote:
Originally posted by sodeep

quote:
Originally posted by visakh16

declare @temp table (Policy_Id int, Policy_Exp_Dt datetime, COl_1 int)
insert into @temp
select 123,'10/30/2008',333 union all
select 123,'09/25/2008',445 union all
select 123,'08/30/2008',443

select Policy_Id,Policy_Exp_Dt,COl_1 from
(

select *, dense_rank() over (partition by policy_id order by policy_exp_dt desc) as rn from @temp
)as p

If u want 9/25/2008, then put where condition in a query.
where rn = 2

Thanks for new knowledge buddy.

Hey no problem .. you're welcome buddy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources