May 20, 2013 at 12:05 am
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_gross integer,amt_matched 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,100)
insert into f values(1,2,'2/1/2013',100,0)
insert into f values(1,3,'3/1/2013',200,0)
insert into f values(1,4,'4/1/2013',50,0)
insert into f values(2,5,'1/10/2013',400,400)
insert into f values(2,6,'2/10/2013',500,0)
insert into f values(2,7,'3/10/2013',100,0)
insert into f values(3,8,'5/1/2013',100,0)
insert into f values(3,9,'6/1/2013',300,0)
*/
/*
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 (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(5,5,5,400,2,'01/20/2013')
*/
Below is query that I am trying but want to select different data:
declare @runDate date
set @runDate = '6/11/2013'
SELECT c.c_id,
f.e_dt,@runDate as RunDate,bfm.alloc_dt,bfm.amt as match,f.amt_gross as gross,
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
order by c_id, days desc
so for c_id = 1 none of rows having MATCH and GROSS equal, so minimum e_dt record should come.
for c_id = 2, 400 amount row is matching so, I want to select next minimum e_dt for c_id = 2 and ignore row with 400 amount.
for c_id = 3, there are no rows having MATCH and GROSS same, so minimum e_dt record should come
current result is like this:
12013-01-012013-06-112013-01-1025100152 --- this one should come
12013-01-012013-06-112013-01-2725100137
12013-02-012013-06-11NULLNULL100130
12013-03-012013-06-11NULLNULL200102
12013-01-012013-06-112013-03-0150100102
12013-04-012013-06-11NULLNULL5071
22013-01-102013-06-112013-01-20400400142
22013-02-102013-06-11NULLNULL500121--- this one should come
22013-03-102013-06-11NULLNULL10093
32013-05-012013-06-11NULLNULL10041--- this one should come
32013-06-012013-06-11NULLNULL30010
I want only highlighted records as explained above.
Any help or suggestions are highly appreciable.
TIA,
Surinder
May 20, 2013 at 1:34 am
surindersinghthakur (5/20/2013)
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_gross integer,amt_matched 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,100)
insert into f values(1,2,'2/1/2013',100,0)
insert into f values(1,3,'3/1/2013',200,0)
insert into f values(1,4,'4/1/2013',50,0)
insert into f values(2,5,'1/10/2013',400,400)
insert into f values(2,6,'2/10/2013',500,0)
insert into f values(2,7,'3/10/2013',100,0)
insert into f values(3,8,'5/1/2013',100,0)
insert into f values(3,9,'6/1/2013',300,0)
*/
/*
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 (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(5,5,5,400,2,'01/20/2013')
*/
Below is query that I am trying but want to select different data:
declare @runDate date
set @runDate = '6/11/2013'
SELECT c.c_id,
f.e_dt,@runDate as RunDate,bfm.alloc_dt,bfm.amt as match,f.amt_gross as gross,
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
order by c_id, days desc
so for c_id = 1 none of rows having MATCH and GROSS equal, so minimum e_dt record should come.
for c_id = 2, 400 amount row is matching so, I want to select next minimum e_dt for c_id = 2 and ignore row with 400 amount.
for c_id = 3, there are no rows having MATCH and GROSS same, so minimum e_dt record should come
current result is like this:
12013-01-012013-06-112013-01-1025100152 --- this one should come
12013-01-012013-06-112013-01-2725100137
12013-02-012013-06-11NULLNULL100130
12013-03-012013-06-11NULLNULL200102
12013-01-012013-06-112013-03-0150100102
12013-04-012013-06-11NULLNULL5071
22013-01-102013-06-112013-01-20400400142
22013-02-102013-06-11NULLNULL500121--- this one should come
22013-03-102013-06-11NULLNULL10093
32013-05-012013-06-11NULLNULL10041--- this one should come
32013-06-012013-06-11NULLNULL30010
I want only highlighted records as explained above.
Any help or suggestions are highly appreciable.
TIA,
Surinder
declare @runDate date
set @runDate = '6/11/2013'
SELECT * FROM
(
SELECT c.c_id,f.e_dt,@runDate as RunDate,bfm.alloc_dt,bfm.amt as match,f.amt_gross as gross,
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 CASE when ISNULL(bfm.amt,0) - ISNULL(f.amt_gross,0) =0 THEN 9999 ELSE 1 END,f.e_dt ) AS RID
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
)T WHERE T.RID = 1
order by 1, days desc
--Divya
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply