SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to select max based on condition -- (SOLVED)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sur200
Starting Member

India
7 Posts

Posted - 05/19/2013 :  21:12:38  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/19/2013 :  23:05:38  Show Profile  Reply with Quote
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
Go to Top of Page

sur200
Starting Member

India
7 Posts

Posted - 05/19/2013 :  23:44:50  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 05/20/2013 :  00:01:38  Show Profile  Reply with Quote
You can edit the Subject(Title) of post with tag --(SOLVED)

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000