| Author |
 Topic  |
|
|
sur200
Starting Member
India
7 Posts |
 Posted - 05/19/2013 : 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 |
Edited by - sur200 on 05/20/2013 00:34:33
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 05/19/2013 : 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:
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
|
Edited by - MuMu88 on 05/19/2013 23:06:40 |
 |
|
|
sur200
Starting Member
India
7 Posts |
Posted - 05/19/2013 : 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 |
Edited by - sur200 on 05/19/2013 23:46:10 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 05/20/2013 : 00:01:38
|
You can edit the Subject(Title) of post with tag --(SOLVED)
--
Chandu |
|
|
| |
Topic |
|
|
|
How to select max based on condition -- (SOLVED)