Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to select max based on condition. Expand / Collapse
Author
Message
Posted Sunday, May 19, 2013 7:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:12 AM
Points: 9, Visits: 57
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
Post #1454384
Posted Sunday, May 19, 2013 7:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:17 PM
Points: 1,095, Visits: 3,182
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

Post #1454388
Posted Sunday, May 19, 2013 8:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:12 AM
Points: 9, Visits: 57
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'
Post #1454392
Posted Monday, May 20, 2013 3:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:17 PM
Points: 1,095, Visits: 3,182
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
Post #1454447
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse