Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to select max based on condition.


How to select max based on condition.

Author
Message
surindersinghthakur
surindersinghthakur
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 59
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
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
Hi

You could try the following. The results of the MAX aggregates need to be cast to varchar so that they can be provided in the same column.

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

with originalQuery as (
select c.c_id,c.p_id,
f.e_dt,@runDate as RunDate,f.f_id,f.amt 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_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 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
)
select c_id,
coalesce(cast(max(alloc_dt) as varchar(20)), cast(max(days) as varchar(20))) as maxValue,
case when max(alloc_dt) is null then 'Days' else 'Date' end as maxValueOf
from originalQuery
group by c_id


surindersinghthakur
surindersinghthakur
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 59
Thanks for quick reply!
But I need both columns i.e. one for alloc_date and other for Days.
so output should be like:
c_id alloc_dt Days
1 2013-03-01 132 (days for maximum date)
2 2013-01-20 172 (days corresponding to maximum date for c_id 2)
3 NULL 71 (max days because all rows are null)


Please let me know if I am not clear.

Thanks,
Surinder

EDIT:
those results are expected for
set @runDate = '7/11/2013'
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
Sorry I misunderstood the requirement. This should do it
declare @runDate date
set @runDate = '6/11/2013';

with originalQuery as (
select c.c_id,c.p_id,
f.e_dt,@runDate as RunDate,f.f_id,f.amt 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_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,
ROW_NUMBER() OVER (PARTITION BY c.c_id ORDER BY alloc_dt DESC) N --Added a rownumber
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
)
select c_id
,alloc_dt
,Days
from originalQuery
where N = 1 and alloc_dt is not null
union
select c_id
,max(alloc_dt)
,max(days)
from originalQuery
group by c_id having max(alloc_dt) is null


Unfortunately I can't test this at the moment.
Edit: Fixed up an ambiguous column reference

Also I can't see how you get the results you have specified in your previous post. With the data supplied I get:
c_id        alloc_dt   Days
----------- ---------- -----------
1 2013-03-01 102
2 2013-01-20 142
3 NULL 41

Looking at the results of the original query, this appears to be as specified
c_id        p_id    ... alloc_dt   Days
----------- ---------- ---------- -----------
1 A 2013-01-10 152
1 A 2013-01-27 137
1 A 2013-03-01 102
1 A NULL 130
1 A NULL 102
1 A NULL 71
2 B 2013-01-20 142
2 B NULL 121
2 B NULL 93
3 A NULL 41
3 A NULL 10

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search