How to select max based on condition -- (SOLVED)

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

How to select max based on condition -- (SOLVED)

Author

Topic

sur200
Starting Member

7 Posts

Posted - 2013-05-19 : 21:12:38

Hi Friends,

I want to select maximum date data based on some condition i.e.
if alloc_dt column has value for c_id, then I want to select maximum date row for that particular c_id
and if all rows have NULL for c_id, then I want to select maximum DAYS row for that particular c_id.

Below are sample tables and sample date, please help
--create table c (c_id integer, p_id varchar(10))
--create table f (c_id integer,f_id integer,e_dt date,amt integer)
--create table bf(bf_id integer,actual_dt date,c_id integer,p_id varchar(10))
--create table bfm(bfm_id integer, bf_id integer,f_id integer,amt integer,c_id integer,alloc_dt date)

/*

insert into c values(1,'A')
insert into c values(2,'B')
insert into c values(3,'A')
insert into c values(4,'C')
*/

/*

insert into f values(1,1,'1/1/2013',100)
insert into f values(1,2,'2/1/2013',100)
insert into f values(1,3,'3/1/2013',200)
insert into f values(1,4,'4/1/2013',50)

insert into f values(2,5,'1/10/2013',400)
insert into f values(2,6,'2/10/2013',500)
insert into f values(2,7,'3/10/2013',100)

insert into f values(3,8,'5/1/2013',100)
insert into f values(3,9,'6/1/2013',300)
*/

/*
insert into bf values (1,'1/10/2013',1,'A')
insert into bf values (2,'1/25/2013',1,'A')
insert into bf values (3,'3/01/2013',1,'A')

--insert into bf values (4,'3/01/2013',3,'A')

insert into bf values (5,'01/20/2013',2,'B')

*/

/*
insert into bfm values(1,1,1,25,1,'1/10/2013')
insert into bfm values(2,2,1,25,1,'1/27/2013')
insert into bfm values(3,3,1,50,1,'3/01/2013')

--insert into bfm values(4,4,8,50,3,'3/01/2013')

insert into bfm values(5,5,5,400,2,'01/20/2013')

*/

--select * from f
--select * from bfm
--select * from bf

declare @runDate date
set @runDate = '6/11/2013';

select c.c_id,c.p_id,
f.e_dt,@runDate as RunDate,f.f_id,f.amt,
bf.actual_dt,bf.bf_id,bf.c_id as bfC_id,
bfm.bfm_id,bfm.f_id as bfmF_id,bfm.amt,bfm.alloc_dt,
case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) else
datediff(day,f.e_dt,@runDate) end as Days

from c
inner join f f on c.c_id = f.c_id
left join bfm on bfm.f_id = f.F_id
left join bf on bf.bf_id = bfm.bf_id

where f.e_Dt <= @runDate
order by c.c_id, days desc

so for c_id =1 row with alloc_dt =2013-03-01 should be selected becoz that is maximum date for that c_id
for c_id = 2, row with alloc_dt = 2013-01-20 should be selected becoz that is maximum
for c_id = 3, row with maximum days i..e 41 should be selected because all alloc_dt rows are NULL, so I want maximum days row in this case.

any comments/suggestions are highly appreciable.
TIA,
Surinder Singh

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-19 : 23:05:38

The data you provide has a non-null value in alloc_dt column for c_id = 3,
so I was unable to test the alloc_dt = null condition;
Here is your code slightly modified (in red) to give you what you want:
[CODE]
declare @runDate date
set @runDate = '6/11/2013';
SELECT * from (select ROW_NUMBER() OVER(PARTITION BY c.c_id Order by alloc_dt DESC,
(case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) else
datediff(day,f.e_dt,@runDate) end) DESC) as RN, c.c_id,c.p_id,
f.e_dt,@runDate as RunDate,f.f_id,f.amt as famt,
bf.actual_dt,bf.bf_id,bf.c_id as bfC_id,
bfm.bfm_id,bfm.f_id as bfmF_id,bfm.amt as bfmamt,bfm.alloc_dt,
case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) else
datediff(day,f.e_dt,@runDate) end as Days

from @c c
inner join @f f on c.c_id = f.c_id
left join @bfm bfm on bfm.f_id = f.F_id
left join @bf bf on bf.bf_id = bfm.bf_id

where f.e_Dt <= @runDate) A
WHERE RN = 1 order by A.c_id, days desc

[/CODE]

sur200
Starting Member

7 Posts

Posted - 2013-05-19 : 23:44:50

Looks Good!

Thanks a lot!!

Is there anything here where I can mark reply as ANSWER or HELPFUL or some points system :)

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-20 : 00:01:38

You can edit the Subject(Title) of post with tag --(SOLVED)

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources