Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 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

549 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
2241 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  
 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.02 seconds. Powered By: Snitz Forums 2000