how to select these rows. - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

how to select these rows.

Author

Topic

sur200
Starting Member

7 Posts

Posted - 2013-05-20 : 02:07:48

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:
1 2013-01-01 2013-06-11 2013-01-10 25 100 152 --- this one should come
1 2013-01-01 2013-06-11 2013-01-27 25 100 137
1 2013-02-01 2013-06-11 NULL NULL 100 130
1 2013-03-01 2013-06-11 NULL NULL 200 102
1 2013-01-01 2013-06-11 2013-03-01 50 100 102
1 2013-04-01 2013-06-11 NULL NULL 50 71
2 2013-01-10 2013-06-11 2013-01-20 400 400 142
2 2013-02-10 2013-06-11 NULL NULL 500 121--- this one should come
2 2013-03-10 2013-06-11 NULL NULL 100 93
3 2013-05-01 2013-06-11 NULL NULL 100 41--- this one should come
3 2013-06-01 2013-06-11 NULL NULL 300 10

I want only highlighted records as explained above.

Any help or suggestions are highly appreciable.

TIA,
Surinder

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-20 : 02:42:55

Hmm..Your explanation is not same as sample data

For ex you say for c_id = 1 none of rows having MATCH and GROSS equal but I cant see the row below in f table
insert into f values(1,1,'1/1/2013',100,100)

it has gross amd match values same

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-20 : 13:49:00

Here you go:
[CODE]

SELECT * FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY c.c_id Order by COALESCE(alloc_dt, '1/1/3000'),
(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,
f.e_dt,@runDate as RunDate,bfm.alloc_dt,COALESCE(bfm.amt, 0) as match,COALESCE(f.amt_gross, 0) 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 and (COALESCE(bfm.amt, 0) <> COALESCE(f.amt_gross, 0))) A
WHERE RN = 1 order by c_id, days desc

[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources