May 19, 2013 at 7:10 pm
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
May 19, 2013 at 7:49 pm
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 #cc
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
May 19, 2013 at 8:42 pm
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'
May 20, 2013 at 3:05 am
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 #cc
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
[highlight]1 A 2013-03-01 102[/highlight]
1 A NULL 130
1 A NULL 102
1 A NULL 71
[highlight]2 B 2013-01-20 142[/highlight]
2 B NULL 121
2 B NULL 93
[highlight]3 A NULL 41[/highlight]
3 A NULL 10
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply